The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

VB6: ADO question

Please bare with me, I'm still new at this...
I’m having trouble with ADO (simply put – I just don’t know how it works):
Code that I inherited from another system looks like this(ADOConn is passed as a parameter to the function, it looks like “ADOConn As ADODB.Connection”) :

Dim ADOrs As ADODB.Recordset
Dim sSql As String
Dim vRsVariant as Variant

sSql = "Select * from Mytable where bla = ‘whocares’ "
   
Set ADOrs = New ADODB.Recordset
ADOrs.Open sSql, ADOConn, adOpenForwardOnly, adLockReadOnly

    If ADOrs.EOF Then
        MsgBox "It's empty"
    Else
        vRsVariant = ADOrs.GetRows(100) ‘because I only want the first 100 returned – for testing
    End If

The error I get is: “operation not allowed when object is closed.”  And this happens before the If ADOrs.EOF statement.
Please help, any suggestions?
Celeste L Send private email
Tuesday, November 15, 2005
 
 
I must also mention that for my testing scenario, the select does not return any value (it works when the script DOES return something).
So, am I write in assuming that when nothing is returned the recordset is closed?
Celeste L Send private email
Tuesday, November 15, 2005
 
 
Maybe you haven't opened the connection.

ADOCONN.Open strConnectionDetails

Which line is giving you getting the error?

Aside: further down you're restricting the rows to 100. It would be better to do this in the sql using SELECT TOP 100, as you reduce the data being returned.
Justin Send private email
Tuesday, November 15, 2005
 
 
Try opening the connection i.e.
ADOConn.Open ' (May have to refer to connection string here)
ADOrs.Open sSql, ADOConn, adOpenForwardOnly, adLockReadOnly

Make sure to close the connection after you retrieve the recordset...

Good luck!
+= Send private email
Tuesday, November 15, 2005
 
 
Thanks Justin, I've found the problem (yes conn was opened).  The stored proc I used was screwy.  It first did an insert, then an update, and the nocount was not set to on. 
I added the "Set nocount on" to the stored proc and it worked.
(luckily my Sql is a bit better than my VB...)
Celeste L Send private email
Tuesday, November 15, 2005
 
 
You'll also get errors if you have multiple select statements.

As you're using stored procs, I take it that the actual code looks nothing like what you originally posted - presumably you're using the command object, rather than just having sql = "exec(sprocname)".
Justin Send private email
Tuesday, November 15, 2005
 
 
The VB code looks the same, but the sql strings looks like : "Exec [storedprocname] onevalue, 'another'" (the stored proc inserts into a table and select from another table joining the table it just inserted to.)

Anyway.
Celeste L Send private email
Tuesday, November 15, 2005
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz