Using VB-ADO with SQL search, but getting this error message.

[Yasir4444]

"Run-time error '3021':
Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record."

This is the extact code I am using.

------------------------------------------------------------
Public Sub GetCounter(TableOfCounter As String, RecordCounter As Double)

Dim DbCnn As ADODB.Connection0
Dim Cmd As ADODB.Command
Dim Rec As ADODB.Recordset
Dim SQLStatment As String

SQLStatment = "SELECT CntCounter FROM TblCounter WHERE CntTblName = ' " & TableOfCounter & " ' "

TableOfCounter = UCase(TableOfCounter)
RecordCounter = 0

Set DbCnn = New ADODB.Connection
Set Cmd = New ADODB.Command
Set Rec = New ADODB.Recordset

DbCnn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=\MySample\MyMSAccess.MDB"

With Cmd
.ActiveConnection = DbCnn
.CommandType = adCmdText
.CommandText = SQLStatment
.CommandTimeout = 60
Set Rec = .Execute
End With

RecordCounter = Rec!CntCounter

End Sub
-----------------------------------------------------------

' On the second last line [RecordCounter = Rec!CntCounter] I get error message,
which is
"Run-time error '3021':
Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record."

The record does exist if I issue the same SQLStatment into MS-Access using
MS-Access queries.

I have tried to insert this statment [Rec.MoveFirst], but I get the same error
message as stated above.

Could someone, please help me since I have spent over a week now and cannot find
any solution even after checking serveral different books.

Thanks,

Yasir Ahmad

 

[JRJohnston]

I would get the record count from your select statement, to make sure that the issue is that you are getting an empty record set (or not). If you are, I would print out the
SQLStatement, and cut & paste it into a query, and other such, exploring for some subtle difference between what you put in manually, and what VB generates. Are you sure about your use of single versus double quotes?

Just some suggestions off the top.
hope it is helpful. If not, sorry.
Jim

 

[vb5prgrmr]

Ok, lets start off with noticing that you upper case (ucase) the variable containing the table counter name after you set your query string. This should not make a difference but just for giggles (testing purposes) make sure that the table counter name you are querying against is the exact same case. Also what version of ADO are you using? I ask this because the "!" is going to be obsolete here soon. You may want to change your syntax to Rec.Fields("CntCounter&quot. Also

[tt]
If Rec.RecordCount <> 0 And Rec.Bof = False And Rec.Eof = False Then
'You Have at least 1 record
End If

[/tt]

 

[CajunCenturion]

I suggest that unless the spaces are required, that you remove the leading and trailing space from the TableOfCounter variable.

SQLStatment = "SELECT CntCounter FROM TblCounter WHERE CntTblName =

' "

 & TableOfCounter &

" '

 "

SQLStatment = "SELECT CntCounter FROM TblCounter WHERE CntTblName =

'"

 & TableOfCounter &

"'

 "

Because you had a leading and trailing space inside of the single quote, the search will require that those spaces exist in the data in order to match.

That being said, I would also highly recomment that you follow vb5prgrmr's suggesstion on checking the EOF and BOF properties of the recordset before trying to access it. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein