strange recordset problem - Xtreme Visual Basic Talk

06-07-2005, 04:34 AM

Nowhere Man Nowhere Man is offline

Regular

Join Date: Jul 2002

Location: Belgium

Posts: 99

strange recordset problem

Hi all,

I've encountered a very strange problem just a few hours ago and I was hoping to get a fresh look on things on this forum.

To loop through my recordset I always use the same lines of code, in every project and several times within one project. This project has the following code running in 6 different functions and subs now (not at the same time):

Code:

If Not rs.EOF Or Not rs.BOF Then 'Just to check if there are any records in a recordset
    rs.MoveFirst
        
    While Not rs.EOF 
        'Some code to put the data in a listview
        rs.MoveNext
    Wend
End If

For 5 functions this works correctly but now I doesn't want to work anymore

VB gives me the following error:
Either BOF or EOF is true, or the current record has been deleted. Requested operation requires a current record

I've already checked the SQL statement I use and it works perfect, nothing wrong there. Plus all the other functions that work the same way are working
eg

Code:

If Not rs.EOF Or Not rs.BOF Then
    rs.MoveFirst
        
    While Not rs.EOF
        MDIForm1.cmbDatabase.AddItem (rs!description)
        MDIForm1.lstDatabases.AddItem (rs!catalog)
        rs.MoveNext
    Wend
End If

any idea's??

06-07-2005, 05:06 AM

Shurik12

Steppe Walker

Retired Moderator
* Expert *

Join Date: Jul 2002

Location: Ukraine/Russia/Belgium

Posts: 7,227

Please show how you open

-connection
-the recordset in question

__________________
"A diaper is not like a computer that makes satisfying burbling noises from time to time, hinting at great inner complexity." Malcolm Gladwell

"I'm sitting here completely surrounded by no beer." Onslow, 'Keeping up appearances'

06-07-2005, 05:13 AM

Nowhere Man Nowhere Man is offline

Regular

Join Date: Jul 2002

Location: Belgium

Posts: 99

This is how I connect to the database and populate the Recordset. It works in every other function but not in the new function I made!!

Code:

dim Con As New ADODB.Connection
dim adocmd As New ADODB.Command
dim sqlq As String
dim rs As Recordset

Con.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;PORT=3306;DATABASE=dbUsed; USER=user01;PASSWORD=****;OPTION=3;"

Set adocmd = New ADODB.Command
    With adocmd
        .ActiveConnection = Con
        .CommandText = sqlq
        .CommandType = adCmdText
    End With
    
Set rs = adocmd.Execute

06-07-2005, 05:21 AM

Shurik12

Steppe Walker

Retired Moderator
* Expert *

Join Date: Jul 2002

Location: Ukraine/Russia/Belgium

Posts: 7,227

What's the exact sql string?

06-07-2005, 07:01 AM

King_George King_George is offline

Junior Contributor

Join Date: Feb 2005

Location: Oakville, Ontario Canada

Posts: 353

Just for a test, try using this:

Code:

If Not rs.EOF And Not rs.BOF Then
 rs.movefirst
 do until rs.eof
     'code
     rs.movenext
 loop

end if

It's what I use and I've never seen that problem.

HTH

__________________
Energy can neither be created or destroyed. It can only be wasted.

Red Green

Last edited by Shurik12; 06-07-2005 at 07:33 AM.

06-07-2005, 08:09 AM

Nowhere Man Nowhere Man is offline

Regular

Join Date: Jul 2002

Location: Belgium

Posts: 99

@King_George: That didn't work, it stil gives me the same error

The SQL string is not always the same. It depends on the user login and what items are selected on a form etc.
I've tested all 4 possible SQL string independetly on the database and they all work just fine!

It's really strange, the first few items are being displayed it's just later on nearly the last record that the problem occurs

just to be sure here it is:

Code:

sqlq = "SELECT naam, voornaam, stamboeknr, geboortedatum, leerjaar, klas " & _
                        "FROM leerling, klas, leerkracht " & _
                        "WHERE leerling.klasnr = klas.klasid AND leerkracht.klasid=klas.klasid AND " & _
                        "leerkracht.gebruikersid=" & Act_Gebr.UserNr

Last edited by Shurik12; 06-07-2005 at 08:18 AM.

06-07-2005, 08:25 AM

Shurik12

Steppe Walker

Retired Moderator
* Expert *

Join Date: Jul 2002

Location: Ukraine/Russia/Belgium

Posts: 7,227

From what you've been saying I can only suggest you'll change the way you open you recordset

Code:

Dim Con As New ADODB.Connection
'Dim adocmd As New ADODB.Command
Dim sqlq As String
Dim rs As New ADODB.Recordset

Con.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;PORT=3306;DATABASE=dbUsed; USER=user01;PASSWORD=****;OPTION=3;"

'Set adocmd = New ADODB.Command
'    With adocmd
'        .ActiveConnection = Con
'        .CommandText = sqlq
'        .CommandType = adCmdText
'    End With
 rs.CursorLocation=adUseClient
 rs.Open sqlq, Con, adOpenStatic, adLockreadOnly   

'Set rs = adocmd.Execute

' display you recordset
'close it here and set to nothing

06-07-2005, 11:20 AM

Nowhere Man Nowhere Man is offline

Regular

Join Date: Jul 2002

Location: Belgium

Posts: 99

This is even worse,

now nothing works! I can't even log into the system anymore. The database connection is totaly not working!

I'm really getting depressed now

06-07-2005, 11:34 AM

Shurik12

Steppe Walker

Retired Moderator
* Expert *

Join Date: Jul 2002

Location: Ukraine/Russia/Belgium

Posts: 7,227

>now nothing works! I can't even log into the system anymore. The database connection is totaly not working!

Sorry, how opening a recordset in a different way can influence your connection?

#10

06-07-2005, 11:38 AM

Nowhere Man Nowhere Man is offline

Regular

Join Date: Jul 2002

Location: Belgium

Posts: 99

Well,
I get the "Object variable or With block variable not set" error when I try to execute a query

the error is raised on this line

Code:

rs.CursorLocation = adUseClient

#11

06-07-2005, 11:43 AM

Shurik12

Steppe Walker

Retired Moderator
* Expert *

Join Date: Jul 2002

Location: Ukraine/Russia/Belgium

Posts: 7,227

What's the exact code you have now (including the declaration of the recordset and connection)?

#12

06-07-2005, 12:01 PM

Nowhere Man Nowhere Man is offline

Regular

Join Date: Jul 2002

Location: Belgium

Posts: 99

But if the way I use a recordset is the problem then why does it only occurs now and not the thousands of times before when I also used this exact same code?!?!

#13

06-07-2005, 12:22 PM

Shurik12

Steppe Walker

Retired Moderator
* Expert *

Join Date: Jul 2002

Location: Ukraine/Russia/Belgium

Posts: 7,227

Well if you want to stick to the code of you I don't mind, but if I get it right there was something wrong with it (otherwise you would not have posted the question)
I suggested a possile solution now asking to show the code causing the error.
It's for you to choose.

Regards,
Shurik.

#14

06-07-2005, 12:35 PM

Nowhere Man Nowhere Man is offline

Regular

Join Date: Jul 2002

Location: Belgium

Posts: 99

As I assumed the problem was NOT with the database connection or the recordset but was somehow related to the CallByName function used to open the form that invoked the query.

I'm not sure what the exact cause is but I wil surely invetigate it further because I find this very interesting!

thx for all the help everybody

#15

06-07-2005, 01:11 PM

NoahBody

Senior Contributor

Join Date: Apr 2000

Location: Edge City, CA

Posts: 799

Maybe the cow's out of the barn and your off trouble shooting this thing, but, I need to chime in anyhow.

First, the error message means your asking for a record!field and the cursor (the pointer to the current record) is null. This virtually always happens when the current record is deleted but the recordset is not updated OR when the move commands have pushed it before the beginning or after the end of the recordset.

Also, I highly suggest that if your using this same code a lot of places, you make it a single, public, generic function that only exists in one place.

Hope This Helps,
->Noah