SQL Statement Problems

[bartvanbez]

Hello,

I have some problems for running SQL Statements that i have write bellow.

1. Connection to the database is correct. (i see session on server)
2. Is the RecordSet correct with the SELECT Query?
3. Is the script Wscript.Echo correct to return integer value?

I hope somebody can helping.
==========================================================
Dim mConnection
Dim mRecordset
Dim Data
Dim sql

'Create object RecordSet ADO
Set mRecordset = CreateObject("ADODB.Recordset")

'Create object Connection ADO
Set mConnection = CreateObject("ADODB.Connection")

'Connection with SQL Server 2005 Express W2K8DB01\SQLEXPRESS
mConnection.ConnectionString = "Provider=SQLOLEDB.1;Data Source=W2K8DB01\SQLEXPRESS;Initial Catalog=*****;User Id=sa;Password=*****;"
mConnection.Open

'Check connection with database: 0=false 1=true
'Connection must be display in SQL Management Sudio - Activity Monitor
'In SQL Management Sudio - Activity Monitor under Application you see Microsoft Windows Script Host.
'Session is display connection is oke!
MsgBox "1 = verbonden - waarde = " & mConnection.State

'Defineer SQL Query
sql = "SELECT * FROM incident where gereed = 0"

' Create a Recordset by executing an SQL statement.
mRecordset.Open
mConnection.open sql,mConnection

'Close connections
mRecordset.Close
mConnection.Close

' Display sum of total rows (integer) form query
WScript.echo mRecordset.Open & ":OK"
==========================================================

 

[jges]

WScript.echo can display integer values, but what you have (mRecordset.Open command) would not evaluate to an integer.

 

[mrmovie]

wscript.echo mRecordset.Count

under some circumstances will show you the number of rows. however, i have red that this is unreliable in some circumstances. cant recall where i got that from but...
so, you might be stuck with an +intCounter and looping through. but the .Count should do it

 

[tsuji]

If you want to benefit from the support of recordcount, you should set the CursorLocation to adUseClient.

In any case, the block of the script is quite wrong in this part.
[tt]
'... etc etc
' Create a Recordset by executing an SQL statement.
'if you want the support of recordcount, you've to set the cursorLocation to client
[blue]mConnection.CursorLocation=3 'adUseClient=3[/blue]
m[red]Recordset[/red].open sql,mConnection

' Display sum of total rows (integer) form query
WScript.echo mRecordset.[red]RecordCount[/red] & ":OK" 'if it is not supported, it can return -1 signature

'Close connections
mRecordset.Close
mConnection.Close[/tt]

 

[mrmovie]

thanks tsuji

 

[bartvanbez]

Thanks, Everything now works.