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:
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
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.
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