|
I'm trying to find out how many records are in a table using this code but I keep getting -1 for n.
Dim command As String, data As ADODB.Recordset, n As Integer<br />
<br />
rsGuest.Open("Guest", ADOConnection,ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)<br />
command = "SELECT [Guest ID] from Guest"<br />
<br />
data = ADOConnection.Execute(command)<br />
n = data.RecordCount
I read on another webisite that I need to add this line:
data.CursorLocation = ADODB.CursorLocationEnum.adUseClient
but when I do I get this error:
Operation is not allowed when the object is open.
So what should I do. Thank you for your help.
Mike
|
|
|
|
|
forgot to add I'm doing this with vb.net 2005 with microsoft access 2007.
|
|
|
|
|
If you are using vb.net , I would recomment using the System.Data.Oledb classes rather than com interop with ADODB. It would be both easier and more performant.
To get the number of records currently in a table Use "Select Count(*) as nRecs from Mytable" for your query.
For your ADODB approach to work, you have to navigate to the end of the recordset before the count is accurate (use Recordset.MoveLast). Obviously, this is slow...
To changed to a client side cursor, set the cursortype variable BEFORE opening the connection.
Hope thies suggestions help.
|
|
|
|
|
Hi,
you could try a SELECT COUNT(*) FROM Guest and use ExecuteScalar, then
cast to integer.
Luc Pattyn [Forum Guidelines] [My Articles]
This month's tips:
- before you ask a question here, search CodeProject, then Google;
- the quality and detail of your question reflects on the effectiveness of the help you are likely to get;
- use PRE tags to preserve formatting when showing multi-line code snippets.
|
|
|
|
|
Hi thank you both for your help. I don't have much experience with database programming and I'm not sure what ExecuteScalar is a method of or how I would use it.
To clarify the question of whether I was trying to get the number of rows or the highest id number, I was looking for the number of rows.
thanks again Luc and blueboy,
Mike
|
|
|
|
|
Check this link[^], you can learn more about ExcuteScalar method.
I Love T-SQL
|
|
|
|
|
If you want to get maximum value of Guest ID then use this query
select max([Guest ID]) from Guest
If you have to get number of rows on table then refer to Luc Pattyn's answer.
I Love T-SQL
|
|
|
|
|
Hi.
How can I link DBase III Files in SQL Server 2000.( Linked Server ).
|
|
|
|
|
Hi all,
I am trying to fetch a CsV file from an Ftp site on to a SQL table every three hours.The Csv has the same fields as the table in Sql.
How do i go about this problem? your ideas are most appreciated.
Please advice.
Thank you
|
|
|
|
|
If you're using 2005 search google for SSIS FTP Task. If you're using an earlier version of SQL Server search google for DTS FTP Task. If you run into any problems, then let us know what your specific problem is and what the specific symptoms are and we'll give you a hand.
|
|
|
|
|
Thank you mark,
I have manged to copy the file from the FTP task to the local Pc where SQl is running.However now would like to update my table with the Bulk Insert task in to the table.
How will I make sure that every 30 Minutes its getting a new file from the Ftp and then Insert it to My table in Sql2005.No Duplicated data will be enterd in to the table more than once.
As I am dumping the file on two one folder how will the bulkinsert task remembers this file is used and the other one is going to be used.
Thank you
|
|
|
|
|
Create a data flow task. Use the flat file transform as your source and either Sql Server destination or OleDb destination transform. I personnally don't like the Sql Server destination because it seems buggy to me.
If you want to prevent duplicate data you have a couple options:
1) create an empty table you can load using the data flow task. Then use a sql command to copy the data from the loading table into your target for all records which don't exist in the target. I recommend that you include a step to truncate your loading table before loading it will data. This option is probably the easiest if you're not familiar with SSIS and will probably perform best if you are working with a large dataset.
2) Add a lookup transform to your data flow task to match existing records from your target table with records in your flat file source. Then redirect any errors (ie. records which don't match, thus don't exist already in your target) to your destination transform.
3) Use 2 source transforms, one for your flat file source and one for your target table. Then use a merge transform and use it to filter any matches and return only records which don't have a match. Then pass the results to your destination transform.
Options 2 and 3 may not be viable if your target table is large, unless you have a way to filter the data for the lookup/merge. Like if you can filter the data by a date range or by some foreign key based on your knowledge of the data that is in your flat file source.
Hope this helps
|
|
|
|
|
Your Help is much appreciated,
What i want mark now is how to select files that are not picked up from the Ftp site and only load them as they are not copied.
That is to say for instance You Have a file name : -- 20080430_11.csv file generated from Ftp site at 11 O'clock today.
and --20080430_12.csv for 12 hours ftp.
How will the FTP task remembers that it has copied one file but not another one?
Thank you once again.
Reagrds
modified on Wednesday, April 30, 2008 11:53 AM
|
|
|
|
|
You could use another FTP task to delete the file after you're done. Or you could try and rename it. I don't know if that is directly supported, but if you have permissions you could possibly rename your local copy when you're done, then upload it back to the ftp server and delete the original copy from the server.
You could try and keep a local list of files you're already processed if you don't have permissions on the FTP server and use that to determine which files need to be downloaded and processed.
|
|
|
|
|
Is it possible to restore the (SQL Server 2000)database in SQL Server 7.0?
How to do this?
Thank ^^
|
|
|
|
|
I'm sure you could use the database copy wizard or DTS, but I'm not sure about attach/detach or backup/restore. I would try to set the database compatibility level to match 7.0, if there are no problems then you might be able to try either detach/attach or backup/restore method.
But first, before you do anything else, make a backup of your database in 2000 so you can restore it to the same state in case something goes wrong.
|
|
|
|
|
You cannot restore a backup, nor attach files from an SQL 2000 database to an older Server instance. This works in the older to newer direction (the database gets upgraded automatically), but never in the reverse. Your only choice is to export the data (with BCP, for example), and import into an empty SQL 7 database.
modified on Wednesday, April 30, 2008 10:26 AM
|
|
|
|
|
i have to create a generic stored procedure which accept XML as parameter in which root element contain the table name & inside tags are there field name with values like
<product>
<id>1
<desc>mobile
<price>4500
if the a xml is like above then data shoiuld be inserted in Product table in product.id,product.desc,product.price & so on
how can i read xml tags inside stored procedure
please help me in the above issue
thanxs
|
|
|
|
|
Lot of informations [^] just by simple google search.
I Love T-SQL
|
|
|
|
|
i have tried google but i dont find the appropriate result
by the way
thankx
|
|
|
|
|
Hi,
I am in the process of trying to find out what is the best practise for generating reports from an sql database useing different tables, that would be usefull to user on the Software access the DB, how do you do it? I come from a web design background and are now having to design a reporting engine for an ancient VB 6 application i have been lumped with....
I am used to having to build each report manually in html line for line and writting the formatting out in code, using sql querys for getting the data I need. but this is hard crap work and I would like to know if I am the only one doing it this way or do you have another way of doing it using some kind of report builder like crystal reports.
Its just there must be a better and faster way to get usefull reports from a db without the hard work building every line in the report from different querys and functions that jump all over the place in your application and db.
your insiteful comments will be greatly appreciated...
|
|
|
|
|
If you're programming under .NET, I would suggest to use Microsoft's Reporting Technology (I think Crystal Report is a little more complicated, but that's just my personal opinion). It's easy--all you have to do is to design a report (Add new item -> Report), create an instance with parameters and attach it to your program. You don't have to worry about print margins or layout. They can be set by changing some variables, unlike HTML, which you'd have to mess with CSS. This is especially useful when you have to print labels.
Plus, The Reporting Technology and Crystal Report comes with VS2005. If you're programming .NET you should have them installed already.
|
|
|
|
|
Hi, I have a table that lists the incomes and expenditures of various organizations.
<br />
Organization | Report Year | Income | Expenditure<br />
Org 1 | 1/4/2008 | £20 | £30<br />
Org 1 | 1/4/2007 | £20 | £30<br />
Org 2 | 1/4/2003 | £20 | £30<br />
Org 2 | 1/4/2002 | £20 | £30<br />
Org 2 | 1/4/2001 | £20 | £30<br />
Org 3 | 1/4/2006 | £20 | £30<br />
Org 4 | 1/4/2002 | £20 | £30<br />
I have been trying to write a query that will return the latest record for each organization. So the result would be
<br />
Org 1 | 1/4/2008 | £20 | £30<br />
Org 2 | 1/4/2003 | £20 | £30<br />
Org 3 | 1/4/2006 | £20 | £30<br />
Org 4 | 1/4/2002 | £20 | £30<br />
Tried searching but came up with no uselful answers. Can someone help? It needs to be compatible with Microsoft Access 
|
|
|
|
|
here it is :
select distinct organisation,<br />
(select top 1 t1.rerportyear from myTable as t1 where t1.organisation = myTable.organisation order by rerportyear desc) as reportYear,income,expenditure<br />
from myTable
I Love T-SQL
|
|
|
|
|
Hi
Try this:
SELECT<br />
Organization,<br />
[Report Year],<br />
[Income],<br />
[Expenditure]<br />
FROM <br />
Table1 a <br />
WHERE [Report Year] >=<br />
(<br />
SELECT TOP 1 <br />
[Report Year]<br />
FROM <br />
Table1 b <br />
WHERE <br />
a.Organization = b.Organization <br />
ORDER BY [Report Year] DESC<br />
)<br />
ORDER BY <br />
Organization
Hope you are getting results as expected.
Harini
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
