0

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?

Bigdaddyduergar
  • 29
  • 1
  • 5
  • 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 Aug 28 '14 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 Aug 28 '14 at 18:01
  • Edited to proper tag, it's MSSQL 2008 – Bigdaddyduergar Aug 28 '14 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 Aug 28 '14 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 Aug 28 '14 at 18:20
  • Not sure if the way you have it can return a recordset, even though it can invoke a stored procedure to do processing (insert, update, etc). The examples I've seen use a command object. [Example 1](http://msdn.microsoft.com/en-us/library/windows/desktop/ms676516(v=vs.85).aspx), [Example 2](http://stackoverflow.com/questions/8249706/run-stored-procedure-and-return-values-from-vba) – Tak Aug 28 '14 at 18:20
  • can you paste here the command *after* the concatenation with the contents of dStartDate & dEndDate? have you tried with #mm-dd-yyyy# ? – Lucio M. Tato Aug 28 '14 at 18:25
  • (EXECUTE dbo.StoredProc @@StartDate = '8/22/2014', @@EndDate ='8/28/2014'). When I run this directly in SSMS it returns the info without any problems. I had to put @@ instead of a single one because it was trying to tag users. – Bigdaddyduergar Aug 28 '14 at 18:39
  • try sending this via VB: "EXECUTE dbo.StoredProc @StartDate = '2014-08-22', @EndDate='2014-08-28'". See: http://technet.microsoft.com/en-us/library/ms180878(v=sql.100).aspx#StringLiteralDateandTimeFormats – Lucio M. Tato Aug 28 '14 at 19:40
  • Two things to try. Have you checked the connection error object after the Open call? Have you tried making a very simple stored proc such as SELECT Column FROM Table and see if you can execute that? – jac Aug 29 '14 at 02:31
  • In your code, try changing adCmdText to adCmdStoredProc, – George Mastros Aug 29 '14 at 12:08
  • Changed it to adCmdStoredProc and I get nothing, I created a basic stored procedure as suggested and it doesn't work either. I said forget it and just rewrote the entire project in .net instead of VB6. Thank you all for your help. – Bigdaddyduergar Sep 02 '14 at 19:42

1 Answers1

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

jakdep
  • 852
  • 1
  • 11
  • 28