VB6 Database Closed Error on Execution of Stored Procedure when Create Table or Select Into in procedure

Question

I have an application that I'm modifying. Originally, there was a giant brick of in line SQL that executed to return some data. Due to requests for changes, I've put that into a stored procedure that does more than just return data, and am now trying to execute. Here is the code that I cannot get to work:

 Dim oConnCRS as CRS_Connection
 Dim sSQL As String
 Dim oRS_ReturnRecs As ADODB.Recordset

 sSQL = "EXECUTE dbo.StoredProcedure @StartDate = '" & dStartDate & "', @EndDate ='" & dEndDate & "'"

 Set oConnCRS = New CRS_Connection
 Set oRS_ReturnRecs = New ADODB.Recordset

 With oRS_ReturnRecs
 .Open sSQL, oConnCRS.Connection, adOpenForwardOnly, adLockReadOnly, adCmdText

 If .EOF Then
    .Close
    Me.MousePointer = vbDefault
    MsgBox "No return file activity to report", vbExclamation + vbOKOnly, APP_NAME

    Exit Sub
 End If

If I change the sSQL to something like "Select Column from Table Where Condition" it will work just fine.

The stored procedure is loading a bunch of information into a temporary table, modifying it, and then selecting all of the records from the temporary table.

I have tried changing various settings on the with open, but I still get an error on

 If .EOF then

The error is: "Operation is not allowed when Object is closed"

CRS_Connection is a object that contains all of the connection information for the database, and on a new instantiation opens the connection automatically.

Any guidance or suggestions on what we're doing wrong is greatly appreciated.

New Discovery:

I started having this problem again on another application. I create a temp table, fill it with data, and then select from it. When I do that, I get the same closed error I was experiencing before.

When I remove the select into or create table, it executes without a problem.

What about creating temp tables in stored procedures causes VB6 to close the connection? Is there a workaround for this?

please paste sSQL var contents after concatenation of dates. I'm not familiar with MSSQL, but I guess the dates should be passed as #mm-dd-yyyy# instead of quotes. — Lucio M. Tato
– Lucio M. Tato, Commented Aug 28, 2014 at 17:44
Not sure how your code will work when you change to Select column from table where condition. You seem to be missing connection string and opening the connection in your code. Have you pasted the full code or just part of it? — Tak
– Tak, Commented Aug 28, 2014 at 18:01
CRS_Connection is a object that contains all of the connection information for the database, and on a new instantiation opens the connection automatically. — Bigdaddyduergar
– Bigdaddyduergar, Commented Aug 28, 2014 at 18:02
Yeah, I have no idea where this error is coming from, or why it works with a regular 'Select * from xxx' but not 'exec storedproc' — Bigdaddyduergar
– Bigdaddyduergar, Commented Aug 28, 2014 at 18:20

jakdep · Accepted Answer · 2015-06-12 05:25:09Z

1

I had a similar problem to what you are describing.

Adding SET NOCOUNT ON at the start of the stored procedure solved the problem for me.

Some more suggestions in case this does not solve the problem : http://www.codeproject.com/Questions/414056/VB-Recordset-not-opening-with-stored-procedure

edited Jun 12, 2015 at 5:25

answered Jun 12, 2015 at 5:18

jakdep

8721 gold badge11 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VB6 Database Closed Error on Execution of Stored Procedure when Create Table or Select Into in procedure

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related