VB6 - Call Stored Procedure with recordset output on SQLServer-CodePudding

I'm supposed to run a procedure that results in a recordset. I found some code online. I used this code:

Dim cnAdoDB As ADODB.Connection
Dim StrConnection As String
Dim ACmd As ADODB.Command
Dim TempRS As ADODB.Recordset
Dim RecordNumber As Long
 
Set cnAdoDB = New ADODB.Connection
StrConnection = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=xxx;Password=xxx;Initial Catalog=DB-Name;Data Source=xxx.xxx.xxx.xxx"
 
cnAdoDB.ConnectionString = StrConnection
cnAdoDB.Open
cnAdoDB.CursorLocation = adUseClient

 
Set ACmd = New ADODB.Command
 
ACmd.CommandType = adCmdStoredProc
ACmd.CommandText = "[dbo].[st_log_GetDatiTracciabilitaLottoArticolo]"
 
ACmd.Parameters.Append ACmd.CreateParameter("@ReturnValue", adInteger, adParamReturnValue)
ACmd.Parameters.Append ACmd.CreateParameter("@vLotto", adVarChar, adParamInput, 20, "2022085001")
ACmd.Parameters.Append ACmd.CreateParameter("@vCodArticolo", adVarChar, adParamInput, 32, "A014L628")
ACmd.Parameters.Append ACmd.CreateParameter("@vDataOraPompa", adDate, adParamInput, , Null)
 
 
Set ACmd.ActiveConnection = cnAdoDB
 
Set TempRS = ACmd.Execute(RecordNumber)`

ADODB.Recordset RecordCount property always returns -1. The TempRS recordset is empty but the "RecordNumber" variable has a value of 2 as the result records really are.

CodePudding user response：

Thanks everyone for the replies. I tried to execute the procedure directly from SQLManager, and the output recordset contains 2 records. If you look in the attached image, you find the "RecordNumber" variable at 2, so i thought the vb code and the parameters passed to the procedure were correct. I can try passing the stored procedure the right string length but I guess it doesn't change anything.

CodePudding user response：

Make it a habit to never rely on the .RecordCount property. It's too situational. From the help:

Use the RecordCount property to find out how many records are in a Recordset object. The property returns -1 when ADO cannot determine the number of records or if the provider or cursor type does not support RecordCount. Reading the RecordCount property on a closed Recordset causes an error.

Your code also has errors:

ACmd.Parameters.Append ACmd.CreateParameter("@vLotto", adVarChar, adParamInput, 20, "2022085001")
ACmd.Parameters.Append ACmd.CreateParameter("@vCodArticolo", adVarChar, adParamInput, 32, "A014L628")

Instead of "20" and "32" (presumably the defined size), the Parameter object expects that actual length of the passed data, i.e.

ACmd.Parameters.Append ACmd.CreateParameter("@vLotto", adVarChar, adParamInput, Len("2022085001"), "2022085001")
ACmd.Parameters.Append ACmd.CreateParameter("@vCodArticolo", adVarChar, adParamInput, Len("A014L628"), "A014L628")