Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 573
  • Last Modified:

Problems retrieving 60 or more records with ODBC MySQL / ADO

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
glennj-swe
Asked:
glennj-swe
  • 10
  • 8
  • 5
  • +4
1 Solution
 
SethiCommented:
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
 
glennj-sweAuthor Commented:
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
 
MYLimCommented:
1.update your MDAC to latest one(MDAC2.8)
2.update your VB6 service pack to VB SP5.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
glennj-sweAuthor Commented:
Hi!
Sorry, but i´m already running VB SP5, and MDAC2.8
0
 
glennj-sweAuthor Commented:
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
 
MYLimCommented:
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
 
MYLimCommented:
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
 
MYLimCommented:
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
 
MYLimCommented:
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
 
MYLimCommented:
that all
0
 
glennj-sweAuthor Commented:
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
 
glennj-sweAuthor Commented:
Squeebee:
The database contains over 1000 records.

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


0
 
MYLimCommented:
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
 
MYLimCommented:
Pls post your source code
0
 
glennj-sweAuthor Commented:
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
 
SqueebeeCommented:
Try dropping the cachesize value, it could possibly give unexpected results.
0
 
SqueebeeCommented:
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
 
glennj-sweAuthor Commented:
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
 
MYLimCommented:
have you try to set your cursorlocation to AdUseClient (client side cursor) ?
0
 
SqueebeeCommented:
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
 
MYLimCommented:
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
 
Anthony PerkinsCommented:
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
 
SqueebeeCommented:
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
 
glennj-sweAuthor Commented:
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
 
SqueebeeCommented:
If possible show table structure and what these guilty rows look like.
0
 
ayufansCommented:
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
 
Computer101Commented:
PAQed, with points refunded (150)

Computer101
E-E Admin
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 10
  • 8
  • 5
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now