Using the <recordset>.EOF function problem

[tjessejeff]

I have the following code that is suppose to search a table for a match. If the end of the file is reached, then the record does not exist. The problem I am getting is "THE OPERATION REQUESTED BY THE APPLICATION IS NOT ALLOW..."

' Open ADO recordset from Problem_ID table
stringConnection = "provider=microsoft.jet.oledb.4.0; " _
& "data source=c:\rcas\VB6System\rcas_VB6DB.mdb"
Set rsProblem_ID = New ADODB.Recordset

' OpenDynamic so changes can be made to data
rsProblem_ID.CursorType = adOpenDynamic

' Build SQL statement to search Problem ID table
SQL_Statement = "SELECT * FROM [problem id] " _
& "WHERE [problem id] = '" & Problem_ID & "' "

' Open the recordset
rsProblem_ID.Open SQL_Statement, stringConnection

' Check to see if record is found
If rsProblem_ID.EOF = True Then
' Record is not found


The book I used states that the format is correct, but no match is followed. The table has three records in it.

 

[bjrollet]

You need to dim a connection object. You cannot open a recordset with a connsection string.

dim objCN as ADODB.Connection
set objCN=New ADODB.Connection
dim objRS as ADODB.Recordset
set objRS=New ADODB.Recordset

stringConnection = "provider=microsoft.jet.oledb.4.0; " _
                     & "data source=c:\rcas\VB6System\rcas_VB6DB.mdb"

objCN.open stringConnection

once you have your connection object....

objRS.open strSQL, objCN

 

[tjessejeff]

I am still having problems with the &quot;IF <recordset>.EOF = TRUE THEN&quot; statement. The error message is the same.

 

[bjrollet]

I just tried the following code in VB 6.0 using an Access20000 database and it worked fine. Substitute your information where appropriate.....

Dim objCN As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim strConnection As String
Dim strSQL As String


Private Sub Command1_Click()

Set objCN = New ADODB.Connection
Set objRS = New ADODB.Recordset

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1_test.mdb;Persist Security Info=True"

objCN.Open strConnection

strSQL = "select * from tblDescription"

objRS.Open strSQL, objCN, adOpenForwardOnly, adLockReadOnly

If objRS.EOF Then
    MsgBox "There are no records in the recordset!"
End If

End Sub

 

[bjrollet]

Dont forget to clean up at the end..

objRS.Close
objCN.Close
set objRS=Nothing
set objCN=Nothing

 

[tjessejeff]

I am still getting &quot;<recordset>.EOF =<THE OPERATION REQUESTED BY THE APPLICATION IS NOT ALLOWE...&quot; on the &quot;IF <recordset>.EOF = true Then.... Is there something I am suppose to do in setting up the application that will resolve this issue?

 

[tjessejeff]

I am able to read the database. I just can not update or add to the database. Nor can I do a search. But I can retrieve the first record. IF I do a search, I get that message I stated before. If I try to update, the slRecordAffected is always zero when it should be at least one. The SQL statements work because I tested them before coding them in the application. I have done as stated above. I have read and followed examples in some books I bought. I believe if I can get the search and update resolved, I can handle the delete and add functions. But nothing is working right now. I can read one record but I cannot write back to the database.

 

[87vette]

are you using the OLEDB control. if you are not then this could be why you can not update Nail the code

 

[tjessejeff]

Yes I am. I am using the following:
stringConnection = &quot;provider=microsoft.jet.oledb.4.0; &quot; ___
& &quot;data source=c:\rcas\VB6System\rcas_VB6DB.mdb&quot;

 

[NickISD]

tjessejeff,
If you can post back the code you are now working with it will give us more of a clue as to where the problem is.

For recordsets I've always used :-
If Not (rs.EOF And rs.BOF)
'data exists

Else
'No Data Found
EndIf

Regards, Nick

 

[tjessejeff]

Ok. I am trying to develop a tier three application. Where the application is divided into three parts, user enterface, business rules, and the database access. I am trying to do a search of the database with a record key. When I execute the debugger, I can see how the record key is flowing through the system. I get an error message &quot;THE OPERATION REQUESTED BY THE APPLICATION IS NOT ALLOWE...&quot; when I check for the end of file. Here is the code I am using for the search.

Public Sub Search()

On Error Resume Next

' Declare variables
Dim SQL_Statement As String, _
rsProblem_ID As ADODB.Recordset, _
RootCause_Database As ADODB.Connection, _
stringConnection As String, _
varBookmark As Variant, _
strCommand As String, _
lngMove As Long
MsgBox &quot;search routine&quot;
' Open ADO recordset from Problem_ID table
stringConnection = &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; _
& &quot;Data Source=C:\RCAS\Final RCAS-2000_be.mdb;&quot; _
& &quot;Persist Security Info=False&quot;
Set rsProblem_ID = New ADODB.Recordset
Set RootCause_Database = New ADODB.Connection

' OpenDynamic so changes can be made to data
' rsProblem_ID.CursorType = adOpenDynamic

' Build SQL statement to search Problem ID table
SQL_Statement = &quot;SELECT * FROM tblProblemID &quot; _
& &quot;WHERE [problem id] = '&quot; & Problem_ID & &quot;' &quot;

' Open the recordset
RootCause_Database.Open stringConnection
rsProblem_ID.Open SQL_Statement, RootCause_Database, adOpenForwardOnly, adLockReadOnly

When executing,
adOpenForwardOnly is equal to zero(0)
adLockReadOnly is equal to one(1)

MsgBox (&quot;Problem ID from table = &quot; & tblProblemID.[problem id] & &quot; and the Problem ID from form = &quot; & Problem_ID)

Here is where the problem occurs. It is saying &quot;THE OPERATION REQUESTED BY THE APPLICATION IS NOT ALLOWE...&quot;. It takes the No Record Found path and moves spaces to the fields.

' Check to see if record is found
If rsProblem_ID.EOF = True Then
' Record is not found
Problem_ID = &quot;&quot;
Issue_Date = &quot;&quot;
Program_Area = &quot;&quot;
Functional_Area = &quot;&quot;
Problem_Description = &quot;&quot;
Impact = &quot;&quot;
Goals = &quot;&quot;
Measurement = &quot;&quot;
RaiseEvent Action(2)
Else
Issue_Date = &quot;&quot;
Program_Area = &quot;&quot;
Functional_Area = &quot;&quot;
Problem_Description = &quot;&quot;
Impact = &quot;&quot;
Goals = &quot;&quot;
Measurement = &quot;&quot;

' Fill fields with the results of the search
Problem_ID = rsProblem_ID![problem id]
Issue_Date = rsProblem_ID![issue date]
Program_Area = rsProblem_ID![program area]
Functional_Area = rsProblem_ID![functional area]
Problem_Description = rsProblem_ID![Problem Description]
Impact = rsProblem_ID![Impact]
Goals = rsProblem_ID![Goal]
Measurement = rsProblem_ID![Measurement]
RaiseEvent Action(200)
End If

End Sub

I thank you for helping me resolve this issue. If it is a problem with the way I am reading the recordset, it can prevent problems with the add, change and delete functions.

 

[NickISD]

Hi,
Placed your code in to VB and changed the MDB to an Access 2000 one I have and it worked Ok to a point. Your test for EOF will not work if more than one record is returned as it will not be true. Try changing the test to be :-

If Not (rsProblem_ID.EOF And rsProblem_ID.BOF) Then
' Data found so process it

Else
' No Data returned so blank fields out etc
EndIf

I used the reference Microsoft ActiveX Data Objects 2.1 Library to test.

Regards, Nick

 

[tjessejeff]

I am still getting the same message. Is there a problem in the way I am accessing the recordset? I have an ADODC on the form and the fields are bound. I am using data connection2 for creating the forms through a wizard. I am using an ACCESS 2000 database. Because the wizard would not create a form with ACCESS 2000, I converted the database to ACCESS 97, created the form, and change the connection back to ACCESS 2000. Am I mixing apples and oranges here?

 

[Brewman76]

One suggestion I have is to remove the &quot;On Error Resume Next&quot; statement. The error message you are getting comes up because for whatever reason your recordset is not open. If an error is occurring somewhere before you call recordset.eof (either in creating the connection object or opening the recordset) you aren't able to see it because of the on error resume next statement.

Remove the on error statement and step through to see if it errors prior to this line.

Adam

 

[tjessejeff]

Thank you for you all's help. This issue is resolved. Being new at this, I could not have done this in a timely manner with out all the help. Thank you'll again.