Stored Procedure Question

[abdullauthan]

Please help me with the Stored Procedure Question. I could not open the recordset.

*** Stored Procedure ***

CREATE PROCEDURE sp_CategoryByCode
@CategoryCode Varchar(10)
AS
Select * From ID_Category
Where Category_Code=@CategoryCode


*** VB Code ***

Dim cnInv as New ADODB.Connection
Dim cmCategory As New ADODB.Command
Dim rstCategory As New ADODB.Recordset

cnInv.Open "Driver={SQL Server};Server=MyServer;Database=MyDB;Uid=sa;Pwd=pwd;"

cmCategory.Activeconnection=cnInv
cmCategory.CommandType = adCmdStoredProc
cmCategory.CommandText = "sp_CategoryByCode '" & txtCategoryCode.Text & "'"
rstCategory.Open cmCategory

*************

When I check for rstCategory.RecordCount, it's returning -1.

 

[sunaj]

Hi,

The default ADO recordset (forward only) does no support .recordcount . Change the Cursor type of the recordset to 'static' or 'keyset'.
Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[mwardle]

If you are passing a parameter to a stored procedure, you use the following syntax :

cmCategory.Activeconnection=cnInv
cmCategory.CommandType = adCmdStoredProc
cmCategory.CommandText = "sp_CategoryByCode"
cmCategory.Parameters(1).Value = txtCategoryCode.Text
Set rstCategory = cmCategory.Execute