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

 

[dbtech]

Are u able to get the result for your SP when u run inside SQL query analyzer?


Here is the modified version of your sp, i prefer to use this method.
With cmd
.ActiveConnection = DBS
.CommandType = adCmdStoredProc
.CommandText = "CompareBom"
.Parameters.Append .CreateParameter("Group1",adVarChar, adParamInput, 20, BOM1)
.Parameters.Append .CreateParameter("Group1",adVarChar, adParamInput, 20, BOM1)
End with

Set RST = CMD.Execute()

I dont see any problem with the code,

Since the SP is not returning any records you have to check using

If not RST.EOF Then
' display results
End If

dbtech

 

[sameerbob]

hi david,
here am using 2 parameter objects & also am giving the stored procedure's name "CompareBom" instead of "group1" assuming it to be the stored procedure as u hv assigned that to the commands commandtext property. i dont understand y u hv given "group1" inside create parameter.

try the following code:

Dim RST As New ADODB.Recordset
Dim CMD As New ADODB.Command
Dim prm1,prm2 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 prm1 = CMD.CreateParameter("CompareBom", adVarChar, adParamInput, 20, BOM1)
Set prm2 = CMD.CreateParameter("CompareBom", adVarChar, adParamInput, 20, BOM2)
CMD.Parameters.Append prm1
CMD.Parameters.Append prm2

Set RST = CMD.Execute()

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

 

[dachaney]

I can't use 'if not RST.EOF' either as I get the same error message. I don't think the error is to do with the SP not returning any records, but to with the record set not being created?

I do get results when using the Query Analyzer eg.

Exec CompareBoms 'EP8007', 'EP8007D'

Gives results :-

PARTno BOMA BOMB
------------ ----------- ---------
R99-3788 1 0
R99-3788D 0 1
R99-3789 1 0
R99-3789D 0 1
R99-3793 1 0
R99-3793D 0 1

(6 row(s) affected)

This is my procedure :-
--------------------------------------
CREATE PROCEDURE CompareBom @Group1 varchar(20), @Group2 varchar(20) AS

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'BOM1')
DROP TABLE BOM1

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'BOM2')
DROP TABLE BOM2

SELECT Partno, Sum(Qty) AS SumOfQty INTO BOM1
FROM BOMS
GROUP BY Partno,BOMno
HAVING (((BOMno)=@Group1))
ORDER BY BOMno, Partno;

SELECT Partno, Sum(Qty) AS SumOfQty INTO BOM2
FROM BOMS
GROUP BY Partno,BOMno
HAVING (((BOMno)=@Group2))
ORDER BY BOMno, Partno;


SELECT PARTno, SUMOFQTY AS BOMA, 0 AS BOMB FROM BOM1
WHERE NOT EXISTS (Select * from BOM2 where BOM2.PARTno=BOM1.PARTno and BOM2.SUMOFQTY=BOM1.SUMOFQTY)

UNION SELECT PARTno, 0 AS BOMA, SUMOFQTY AS BOMB FROM BOM2
WHERE NOT EXISTS (Select * from BOM1 where BOM1.PARTno=BOM2.PARTno and BOM1.SUMOFQTY=BOM2.SUMOFQTY)

Order By PARTno;
--------------------------------------

Could it be a problem with the stored procedure itself ?


Thanks

David

 

[tlbroadbent]

Add SET NOCOUNT ON after the AS in the CREEATE PROCEDURE statement. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[dachaney]

Thanks, that worked.