MS SQL Server Express access via VBS

[gustaf111]

Hello,

I have serached the internet how to access how many row there are in a table in a MS SQL database.

Does anybody have an example how to get the number of rows in a table ?

The code and table look like this:
String Value1
Test1 1
Test1 2
Test1 3
Test2 10
Test2 20
Test2 30

I run the SQL command:
SELECT Value1, FROM TestTable WHERE String='Test1'

And after this these commands:
RecordNum = objRecordset.RecordCount
FieldsNum = objRecordset.Fields.count

Value = objRecordset.Fields(0).Value

But the RecordCount returns -1 ? I can se that I that Value is set to 1 which is the first Test1 value. But why does the RecordCount returns -1 and not 3 ?

Cheers Gustaf

 

[tsuji]

There seems to be understood that questions on mssql using vbscript should more often than not be asked in vbscript forum. (It is just by usage observed over time.) You can bring the question to vbs forum:

http://www.tek-tips.com/threadminder.cfm?pid=329

For the present question, I can help with this: When you establish the connection object (say, conn), specify the cursorLocation to client side.
[tt] conn.CursorLocation=3 'adUseClient=3[/tt]
Then you use .RecordCount to get the RecordNum.

 

[simian336]

You can also do

Select count(value1)
FROM TestTable
WHERE String='Test1'

Simi

 

[JoeAtWork]

But why does the RecordCount returns -1 and not 3 ?

You usually have to to populate the entire recordset by doing objRecordset.MoveLast before the RecordCount will be accurate.

However, I highly recommend using an SQL statement like the one Simian336 demonstrated, it is much more efficient if all you want is the count of records.