Recordsets from Stored Procedures

[mhall]

Hi

I am using the following code to run my stored procedure and the creating a recordset to hold the results so that I can move through and display the results. My problem is that the recordcount function and previous functions of the recordset do not work could anybody tell me why.

GetConnectionString

With cn
' Establish DSN-less connection
.ConnectionString = ConnectionString
.ConnectionTimeout = 10
.Open
End With

Dim objComm As ADODB.Command
Dim pr_Parameters(2) As Variant
Set objComm = New ADODB.Command

With objComm
.ActiveConnection = cn
.CommandText = "sp_get_comments"
.CommandType = adCmdStoredProc
End With

pr_Parameters(0) = frmParts.txtOrderNo.Text
pr_Parameters(1) = frmParts.flxParts.TextMatrix(frmParts.flxParts.RowSel, 0)
pr_Parameters(2) = frmParts.flxParts.TextMatrix(frmParts.flxParts.RowSel, 3)

Set rsdisplayComments = objComm.Execute("sp_get_comments", pr_Parameters, adCmdStoredProc)

any help would be greatly appriciated

thanks

Matt

 

[mohmehran]

It is normal. When you use Execute method of Command or Connection object, it returns a forward-only recordset and it does not allowed you to move back (that is using .MovePrevious method). RecordCount will return -1 in this case. So, you can use Recordset.Open instead of Command.Execute. Just remember to set CursorType propertu of Recordset object to something other than 'Forward Only' before opening it. Mohammad Mehran Nikoo, MCSD mohmehran@msn.com