Stored Procedures

[PaulHamill]

All


I am using the code below to run my first SQL Stored Procedure via VB. It is a simple procedure passing in a parameter. The problem is that when I try and do a recordcount I get -1. I think it is to do with the fact that when I run the line
Set objRecordset = objCommand.Execute
the CursorType changes to adOpenForwardOnly, and I think you are not allow to do a recordcount on this type. My question is how do I stop the Recordset from changes to adForwardOnly or is there a way to get the number of records in my recordset.

Any help would be much appreciated

Thanks

Dim objConn As New ADODB.Connection
Dim objCommand As New ADODB.Command
Dim objRecordset As New ADODB.Recordset
Dim strConnString As String

strConnString = "Provider=etc....."

objConn.ConnectionString = strConnString
objConn.Open

objCommand.ActiveConnection = objConn
objCommand.CommandType = adCmdStoredProc
objCommand.CommandText = "SP_TEST"
objCommand.Parameters(1).Value = "FAIL"

objRecordset.CursorType = adOpenDynamic
objRecordset.LockType = adLockOptimistic
Set objRecordset = objCommand.Execute

MsgBox objRecordset.RecordCount

 

[zemp]

A couple of things I see. First I donn't see where you create your parameter, you just give it a value.

Second the .recordcount property is not always reliable. I have found especially true with Access.

Here is an example of the code I use. The only real difference is creating of a parameter in the command object and I use the recordsets .Open method.

Dim Conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command

Conn.ConnectionString = "Provider=etc....."
Conn.Open

With cmd
.ActiveConnection = Conn
.CommandType = adCmdStoredProc
.CommandText = "MyStoredProcedure"
.Parameters.Append .CreateParameter("MyParam", adVarChar, adParamInput, 20)
.Parameters("MyParam&quot.value = Trim(text1)
End With
rs.Open cmd, , adOpenStatic, adLockOptimistic
Thanks and Good Luck!

zemp