How To Return Results from SQL Stored Procedure in VB6

[dachaney]

HI,

I have a stored procedure which works fine in SQL Query Analyser, but when I execute it from a VB application I don't get a record set.

Dim RST As New ADODB.Recordset
Dim CMD As New ADODB.Command
Dim prm As ADODB.Parameter

BOM1 = txt1.text
BOM2 = txt2.text

'DBS connection is set globally
Set CMD.ActiveConnection = DBS
CMD.CommandType = adCmdStoredProc
CMD.CommandText = "CompareBom"
Set prm = CMD.CreateParameter("Group1", adVarChar, adParamInput, 20, BOM1)
CMD.Parameters.Append prm
Set prm = CMD.CreateParameter("Group2", adVarChar, adParamInput, 20, BOM2)
CMD.Parameters.Append prm

Set RST = CMD.Execute()

If RST.RecordCount > 0 Then
' display results
End If

When run the recordset RST is not created and I get the error 'Run Time Error 3704 - The operation is not allowed when the object is closed' at the 'If' statement

For info :-
I am using SQL7.0 and VB6.0
The stored procedure compares two partslists and returns the difference between the two.

Has anyone any ideas??

Thanks

David Chaney

 

[youwhat]

You will need to set your connection string for the recordset then recordset.open before you can execute any commands.

 

[dachaney]

The recordset connection string etc for RST are setup & created by the 'Set RST = CMD.Execute()' command.
The problem is that the recordset is being closed straight away and I am unable to access the results

David