ADODB.Recorset Recordcount returns -1

[jtrask]

I'm trying to create an ADODB recordset, but I don't think its working because my RecordCount is always -1.

I've set my CursorType to adOpenStatic, but I still get -1.

I've set my CursorLocation to adUseClient, but I still get -1.

Per another post in this forum, I created an ADODB.Error object.  When I try to access its Count property, I receive "Ole Object: Object does not exist, or period is used instead of a comma"

Normally, I use RS = DB.Execute(SQLString).  I was experiencing timeouts, so I switched to using ADODB.Command (see below).  Since I've never done it this way before, I'm assuming that is the root source of my problem.  I'm hoping another set of eyes might help.

It's always something stupid, so what stupid thing have I done this time? - for now let's assume that vstrServer, vstrDatabase, and vstrTable all have valid values:

Code: Winbatch

vstrConnection = "driver={SQL Server};server=":vstrSQLServer:";database=":vstrDatabase:";Trusted_Connection=TRUE;"

; Connect to the SQL database
vobjDatabase = ObjectCreate("adodb.connection")
vobjDatabase.Open = (vstrConnection)

vstrHostName = 'jdoe-laptop'

vstrSQLString = "SELECT * FROM ":vstrTable;:" WHERE [HostName] = '":vstrHostName:"' AND [DateRemoved] IS NOT NULL"

vobjADOCmd = ObjectOpen("ADODB.Command")
vobjADOCmd.ActiveConnection = vobjDatabase
vobjADOCmd.CommandText = vstrSQLString
vobjADOCmd.CommandTimeout = 120

vobjRS = ObjectCreate("ADODB.recordset")
vobjRS.CursorLocation = 3 ;adUseClient
vobjRS.CursorType = 3 ;adStaticOpen
               
vcolSQLErr = CreateObject('ADODB.Error')
vobjRS = vobjADOCmd.Execute()

If vobjSQLErr.Count > 0 Then
        Pause("Notice",vobjSQLErr.Number,@CRLF,vobjSQLError.Description)
End If
 

[jtrask]

After posting, I realized I'm using ObjectOpen("ADODB.Command").  Should I be using ObjectCreate instead?  Neither way works.

[td]

I'm trying to create an ADODB recordset, but I don't think its working because my RecordCount is always -1.

I've set my CursorType to adOpenStatic, but I still get -1.

I've set my CursorLocation to adUseClient, but I still get -1.

You create a Recordset object and set the properties but the created Recordset is never used so setting those properties on the created Recordset object does nothing but waste a few CPU cycles.

Per another post in this forum, I created an ADODB.Error object.  When I try to access its Count property, I receive "Ole Object: Object does not exist, or period is used instead of a comma"

The 'Adodb.Error' object is not a collection and does not have a 'Count' Property.  The error collection object is returned by several ADODB object members like the 'Errors' property of the 'Adodb.connection' object.   

Normally, I use RS = DB.Execute(SQLString).  I was experiencing timeouts, so I switched to using ADODB.Command (see below).  Since I've never done it this way before, I'm assuming that is the root source of my problem.  I'm hoping another set of eyes might help.

There are numerous examples in the Tech Database that illustrate how to use Recordsets.  Here is one place to start:

http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/nftechsupt.web+WinBatch/OLE~COM~ADO~CDO~ADSI~LDAP/ADO~DAO+ADO~SQLOLEDB~Example.txt

[jtrask]

When you say I never used the recordset, what do you mean?  I thought I was using it with vobjADOCmd.Execute().

It turns out my recordset has records in it.  I just can't get the Count property to work the way I think it should.  I thought that would be the easiest way to determine whether or not the recordset contained any records.

I'm looking at the code here:http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/nftechsrch.web+WinBatch/OLE~COM~ADO~CDO~ADSI~LDAP/ADO~DAO+TechHome/WinBatch/OLE~COM~ADO~CDO~ADSI~LDAP/ADO~DAO

It's setting the properties for the ADODB Command object differently from the way I was doing it.

[jtrask]

My RecordSet does not support adApproxPosition and adBookmark.  Now I've just got to figure out why.

[td]

When you say I never used the recordset, what do you mean?  I thought I was using it with vobjADOCmd.Execute().

You create a Recordset then it is destroyed and a new one is created by the line 'vobjADOCmd.Execute()'.  Setting properties on the one you create with the 'vobjRS = ObjectCreate("ADODB.recordset")' does nothing because the Recordset is destroyed when you assign a new Recordset to to the 'vobjRS' variable.

It turns out my recordset has records in it.  I just can't get the Count property to work the way I think it should.  I thought that would be the easiest way to determine whether or not the recordset contained any records.

Yes, the Recordset returned by the 'Execute' method may have records in it but that is not the same Recordset you created with 'vobjRS = ObjectCreate("ADODB.recordset")' and Recordset objects do not have a 'count' property.

I'm looking at the code here:http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/nftechsrch.web+WinBatch/OLE~COM~ADO~CDO~ADSI~LDAP/ADO~DAO+TechHome/WinBatch/OLE~COM~ADO~CDO~ADSI~LDAP/ADO~DAO

Your url is not valid.  It is missing the actual article.

[td]

My RecordSet does not support adApproxPosition and adBookmark.  Now I've just got to figure out why.

If a provider does not support a particular functionality, it doesn't support that functionality.  There isn't much figuring required. 

[stanl]

I think you need to look at RecordCount, not Count. Also, don't see why you need the Command Object, should be able to use the Recordset Object, or .execute() from the Connection Object which creates a recordset.
 
Additionally, in SQL Server there is an @@RowCount property that can be obtained.

[jtrask]

I had read that the functionality is related to the type/location of the cursor.  I've been playing around with those.  I've also switched from the old SQL Server provided to the Native Client 11.  Still no joy.

I've also switched to RecordCount, but that hasn't resolved anything either. 

I'm using the Command Object because I have having an issue with some of my queries timing out.  Setting the CommandTimout for the command object resolved that issue.

I'll look at @@RowCount.  I read elsewhere that someone used Count(*) From... to determine if the query would return any rows before running the actual query. I'm going to try that as well.

[jtrask]

Well, this was the best I could do:

"IF (SELECT COUNT(*) FROM ":vstrTable:") = 0 BEGIN SELECT 'ERROR_ERROR' AS Count END ELSE SELECT * FROM ":vstrTable

Now GetRows() always works,  I just have to evaluate the first element in the array to determine whether or not real records were returned.

[JTaylor]

I think I usually set cursor location equal to 3.   Assuming you are using a Connection and RecordSet object something like the following should work.  RecordCount will not (at least not for me) work unless you jump to the last record so it initializes the whole set and can get a count.  The EOF property will tell you whether you have records so you don't have to worry about GetRows working or not.   If the only purpose of getting the row count is to know whether you want try GetRows() you don't need that part as the EOF property will handle that for you.

Code: Winbatch

  adoConst = ObjectConstantsGet(myConn)

  SQLText = "Select isbn, title, author from resources where title like 'Hello%' "
  myREC.Open (SQLText, myConn, adoConst.adOpenStatic, adoConst.adLockReadOnly, adoConst.adCmdText)
  If myREC.EOF == @FALSE Then
    myREC.MoveLast
    myREC.MoveFirst
    row_cnt = myREC.RecordCount
    myREC.MoveFirst
    iarr = myREC.GetRows()
  EndIf
  myREC.Close

 

Jim

[stanl]

RecordCount will not (at least not for me)

Unless I am querying 100,000's of rows, I use keyset/optimistic rather than static, and recordcount works with client cursor. (Access, SQL Server, MySQL, Oracle, SQLite)

[jtrask]

Hmmm...I'll check out .EOF.  The only reason I wanted a record count was to script around the eventuality that no records were returned. EOF should do that for me.

[mathia]

I had this issue a long time ago.  My standard for connecting to any odbc source is:

rst.activeconnection=con
rst.cursortype=3
rst.locktype=3
rst.CursorLocation =3

Cursor type is the thing that actually fixed the problem.