Solved

Problems retrieving 60 or more records with ODBC MySQL / ADO

Posted on 2003-11-12
27
561 Views
Last Modified: 2013-12-25
Everytime i try to make i simple program with VB and MySQL, everything ends up with the same problem. I can´t get more than 60 records from the database!

I have a ADO Control, all values 'standard'
The connection works fine.

I can use recordsource´s like 'select * from customers where cust_ID = 59' without any problems

I can also use for example 'select * from customers' without any errors.
The problems begins when i trying to step trough the recordset.
Code:

adodata.recordset.movefirst
for x = 1 to 100
  print adodata.recordset!cust_ID
  adodata.recordset.movenext
next
 


this returns 'Data provider or other service returned an E_FAIL status.'
but only when x>59

I hope this is a basic problem you all know how to solve, if not
i can publish all of the source code.

Btw, I also tried use paging, but it ends up with the same problem.
Ex. pages of 20 records ,returns error at the last record in page 3.

BR
Glenn
0
Comment
Question by:glennj-swe
  • 10
  • 8
  • 5
  • +4
27 Comments
 
LVL 18

Expert Comment

by:Sethi
ID: 9730205
Check the value of the 60th record in the database. the value must be corrupt. Remove that record and try again. there doesn't seem to be any other problem.
0
 

Author Comment

by:glennj-swe
ID: 9730259
No, that´s not the problem. The same problem occours in three different tables. And everytime when i try to access a record >59 in the recordset.
 
0
 
LVL 8

Expert Comment

by:MYLim
ID: 9730358
1.update your MDAC to latest one(MDAC2.8)
2.update your VB6 service pack to VB SP5.
0
 

Author Comment

by:glennj-swe
ID: 9730385
Hi!
Sorry, but i´m already running VB SP5, and MDAC2.8
0
 

Author Comment

by:glennj-swe
ID: 9730389
Additional info:

'select * from customers where cust_ID = 157' alose works fine.
I simply have problems filling a recordset with more records than 60
0
 
LVL 8

Expert Comment

by:MYLim
ID: 9730454
PAQ Accept Answer:
That kind of problem happened to me when I was working with Informix Databases.
A field who supossed contain an integer values was filled for mistake with nothing (?? true,there was no value)
That problem returned the E_Fail status.
So ,the problem could be the database,not the OLE DB provider.
0
 
LVL 8

Expert Comment

by:MYLim
ID: 9730461
Accepted Answer from Podnov
Date: 06/08/2003 02:43PM PDT
 Accepted Answer  

i recieved this same error when having more than one recordset open at a time.  i fixed it by setting the activeconnection property to nothing"

...
rst.CursorLocation = adUseClient
rst.Open
set rst.ActiveConnection = Nothing
...
 
0
 
LVL 8

Expert Comment

by:MYLim
ID: 9730463
Accepted Answer from Squeebee
Date: 07/28/2003 10:47AM PDT
 Accepted Answer  

Are you checking for an EOF on the recordset when using movenext? You queried for an aggregate value, so you should only have one row. With only one row what do you expect to get with a movenext?

I always use

Do Until rs.EOF
       jalhaslhs
       rs.movenext
Loop


Regards,
Mike Hillyer
www.vbmysql.com
0
 
LVL 8

Expert Comment

by:MYLim
ID: 9730474
Ok now,is my opinion:
'just add a space after your debug.print

adodata.recordset.movefirst
for x = 1 to 100
  print adodata.recordset!cust_ID & " "
  adodata.recordset.movenext
next
0
 
LVL 8

Expert Comment

by:MYLim
ID: 9730476
that all
0
 

Author Comment

by:glennj-swe
ID: 9730483
MYLim:

But the records work just fine. I can read all records, and display it´s data, as long as the recordset not gets bigger than 60.
In ex.
This workaround works, but does not solve the problem itself, but you can
see that the database and the records are ok.

For my_counter=1 to 100
 adodata.recordsource = 'select * from customers where cust_ID =' & str(my_counter)
 adodata.refresh
 print adodata.recordset!customer_name
Next
0
 

Author Comment

by:glennj-swe
ID: 9730503
Squeebee:
The database contains over 1000 records.

I just used For / Next for a simply overview of the problem.


0
 
LVL 8

Expert Comment

by:MYLim
ID: 9730540
why write your code like this ???
For my_counter=1 to 100
 adodata.recordsource = 'select * from customers where cust_ID =' & str(my_counter)
 adodata.refresh
 print adodata.recordset!customer_name
Next

==============================================
should like this:
select * from customers where cust_ID between 1 and 100
or
select * from customers where cust_ID >=1 and cust_ID <=100
==============================================
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 8

Expert Comment

by:MYLim
ID: 9730604
Pls post your source code
0
 

Author Comment

by:glennj-swe
ID: 9730873
MyLim:

>why write your code like this ???

I know, it´s a crazy thing to do, but it works, becouse it keeps the recordset to
1 record at a time. With your example i still will get a recordset with 100 records, and this is the problem, i get an error stepping to record no 60 in the recordset.

select * from customers where cust_ID >=1 and cust_ID <=100
Will generate an error, at cust_ID=60
select * from customers where cust_ID >=101 and cust_ID <=200
Will generate an error, at cust_ID=160

This is my code:
The x=x+1 is there for debugging. I  can change the sql statement, for example
select * from customers where customer_adress like 'g%'; will generate a complete new set of records (recordset :)  ), and I still get the same error when x equals 60.

Dim iRecs As Long
    Dim goConn As ADODB.Connection
       
    Set goConn = New ADODB.Connection
    goConn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;DSN=trekiaread;DESC=MySQL ODBC 3.51 Driver DSN;DATABASE=trekia;SERVER=xxxxx;UID=xxxx;PASSWORD=xxxx;PORT=3306;OPTION=3;STMT=;OPTION=18475"
    goConn.Open
   
    Dim oRs As ADODB.Recordset
    Set oRs = New ADODB.Recordset
   
    With oRs
        .CursorLocation = adUseClient
        .ActiveConnection = goConn
        .Source = "select * from customers"
        .CacheSize = 5
        .Open
        .MoveFirst
       
        Do While Not .EOF
            x = x + 1
            Print .Fields(1).Value;
            .MoveNext  ' Will generate error, when x equals 60
        Loop
    End With
0
 
LVL 17

Expert Comment

by:Squeebee
ID: 9731412
Try dropping the cachesize value, it could possibly give unexpected results.
0
 
LVL 17

Expert Comment

by:Squeebee
ID: 9731422
What I can tell you is that after writing a ton of vb/mysql code (and running a website on the subject (<shameless plug>www.vbmysql.com</shameless plug>), I have never encountered your issue, but then I don't use the cachesize property so I am leaning towards it.
0
 

Author Comment

by:glennj-swe
ID: 9737967
Squeebee:

Changing / Removing cachesize didn´t do the trick.

----

I have solved the problem by linking the table trough a MS Access Database.
Know everything works ok.

Will keep this thread open, becouse there must be a solution to this problem.
Think the trouble my be in the MySQL server, will try to install a local version of
MySQL.
0
 
LVL 8

Expert Comment

by:MYLim
ID: 9738085
have you try to set your cursorlocation to AdUseClient (client side cursor) ?
0
 
LVL 17

Expert Comment

by:Squeebee
ID: 9751450
You have a client-side cursor on your recordset but a server-side cursor on your connection object, and the connection object's cursor is trumping the client's, set your connection object's cursorlocation to client-side.

See an example at www.vbmysql.com/samplecode/simpleinsert.html

and try building the start to your code from it.
0
 
LVL 8

Expert Comment

by:MYLim
ID: 9752312
thank mike support my comment :)
actually,MySQL does not support server side cursor.
but you still can use server side cursor that support by ODBC driver.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9752633
Squeebee,

>>You have a client-side cursor on your recordset but a server-side cursor on your connection object, and the connection object's cursor is trumping the client's, set your connection object's cursorlocation to client-side. <<

Where ever did you read that?  You certainly could not have tested that, otherwise you woud know different.  The Recordset object will inherit the CursorLocation from the Connection object, UNLESS you set it prior to opening the Recordset, in which case it will override the setting in the Connection object.

Anthony
0
 
LVL 17

Expert Comment

by:Squeebee
ID: 9752715
Well, my mistake.

glennj-swe: I have just run through 18000 records on a set of code identical except for the connectionstring, could you try the DSNless connection string I provided in the link above and see if it gets you any closer?

What version of MyODBC are you using?
0
 

Author Comment

by:glennj-swe
ID: 9753398
Squeebee:

Just tried that, same problem.

I´m using MyODBC 3.51

Update:
I have found that there maybe is some kind of dataproblem in every 60th record in our database.
I made a new database, and put som data in, know i get the same error when x=76.
Can´t figurate what kind of data problem anyhow. This database is used daily in a Debian Linux enviorment without any
problems. Also, Access can, as described above, read all records correctly, and pass them trough a access database, witch still
is my workaround.

Thanks for all help sofar.

0
 
LVL 17

Expert Comment

by:Squeebee
ID: 9754504
If possible show table structure and what these guilty rows look like.
0
 
LVL 1

Expert Comment

by:ayufans
ID: 10458508
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

- PAQ'd and points refunded

Please leave any comments here within the next four days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

ayufans
Cleanup Volunteer
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 10490851
PAQed, with points refunded (150)

Computer101
E-E Admin
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now