Cursors with ADO

Hi

I am trying to execute an SP which contains cursors etc....but when I create
the RS using ADO, I seem to get an error?

Is there something wrong with executing SP's of this type through VB using
ADO?

Below is a sample of the code I use:

'**************************************************
Set mrsRpt = New ADODB.Recordset
Set cmd1 = New ADODB.Command
Set cnn1 = New ADODB.Connection



sconnect = "Provider=SQLOLEDB.1;Password = " & mstrPassword & _
";Persist Security Info=True;User ID= " & mstrUser & _
";Initial Catalog=" & mstrDatabase & ";Data Source=" &
mstrServer



'Creates Connection information (NOTE: Server-Side cursor much faster.
With cnn1
.CursorLocation = adUseServer
.CommandTimeout = 0
.Open sconnect

End With

'Creates Command information
With cmd1
.ActiveConnection = cnn1
.CommandText = "exec " & txtSPName & " " & txtParam
.CommandType = adCmdText
.CommandTimeout = 0
End With



mrsRpt.Open cmd1.CommandText, cnn1, adOpenDynamic, adLockBatchOptimistic
'**************************************************



Kind Regards

Rikesh
(VB6 SP5)

Take a look at the Parameters collection in the ADO Command object. That
will give you a good start.

Your command text is incorrect. Try:
With cmd1
.ActiveConnection = cnn1
.CommandText = txtSPName.Text
.CommandType = adCmdText
.CommandTimeout = 0
End With

Next, you will want to create a parameter using the .CreateParameter method
on the command object to create the parameter name, data type, input/output
direction, and length (if a character parameter).

Append this parameter object to the command object using
cmd1.Parameters.Append <parm object>.

Finally, set the value of the parameter using
cmd1.Parameters(<parmname>).Value = txtParm.Text

Once your parameter is created, appended, and set, you can execute your
query just like you have it in your message.

But again, I would start with the BOL and look up the ADO Parameter object.
That will go a long way in answering any specific questions that you might
have after reading this response.

I do hope this helps. Good luck.
Richard

"rikesh patel" wrote:

 > Hi
 >
 > I am trying to execute an SP which contains cursors etc....but when I create
 > the RS using ADO, I seem to get an error?
 >
 > Is there something wrong with executing SP's of this type through VB using
 > ADO?
 >
 > Below is a sample of the code I use:
 >
 > '**************************************************
 > Set mrsRpt = New ADODB.Recordset
 > Set cmd1 = New ADODB.Command
 > Set cnn1 = New ADODB.Connection
 >
 >
 >
 > sconnect = "Provider=SQLOLEDB.1;Password = " & mstrPassword & _
 > ";Persist Security Info=True;User ID= " & mstrUser & _
 > ";Initial Catalog=" & mstrDatabase & ";Data Source=" &
 > mstrServer
 >
 >
 >
 > 'Creates Connection information (NOTE: Server-Side cursor much faster.
 > With cnn1
 > .CursorLocation = adUseServer
 > .CommandTimeout = 0
 > .Open sconnect
 >
 > End With
 >
 > 'Creates Command information
 > With cmd1
 > .ActiveConnection = cnn1
 > .CommandText = "exec " & txtSPName & " " & txtParam
 > .CommandType = adCmdText
 > .CommandTimeout = 0
 > End With
 >
 >
 >
 > mrsRpt.Open cmd1.CommandText, cnn1, adOpenDynamic, adLockBatchOptimistic
 > '**************************************************
 >
 >
 >
 > Kind Regards
 >
 > Rikesh
 > (VB6 SP5)
 >
 >
 >
 ><!-- ~MESSAGE_AFTER~ -->