Solved

MYSQL Recordcount

Posted on 2004-09-14
23
2,127 Views
Last Modified: 2013-12-25
hi i used the coding to open the mysql connection

        Dim DBmysql As ADODB.Connection
        Dim usr As ADODB.Recordset
        Set DBmysql = New ADODB.Connection
        DBmysql.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
                    & "SERVER=" & Txt_HostName.Text & ";" _
                    & "DATABASE=demo;" _
                    & "UID=aaaa;" _
                    & "PWD=aa23;" _
                    & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384
        DBmysql.Open

        Set usr = New ADODB.Recordset
        usr.Open "select * from userinfo ", DBmysql, adOpenDynamic
msgbox usr.recordcount

whenever i run it returns recordcount as -1.  but i have som records in the table userinfo .

can any one help me
0
Comment
Question by:abith
  • 7
  • 4
  • 4
  • +5
23 Comments
 
LVL 10

Expert Comment

by:ADSaunders
ID: 12055474
Hi abith,
    try setting the cursorlocation to client:
            Set usr = New ADODB.Recordset
            usr.CursorLocation = 3 'adUseClient
            usr.Open "select * from userinfo ", DBmysql, adOpenDynamic

Regards .. Alan
0
 
LVL 8

Accepted Solution

by:
mladenovicz earned 500 total points
ID: 12055505
The property returns -1 when ADO cannot determine the number of records or if the provider or cursor type does not support RecordCount.

Try
DBmysql.CursorLocation = adUseClient
0
 
LVL 7

Expert Comment

by:SQL_Stu
ID: 12055655
I always make sure the recordset is populated by doing:

usr.MoveLast
usr.MoveFirst

before trying to return the record count.

I'm not sure why, but it works for me!!!
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:mladenovicz
ID: 12055715
Use this to check if RS is populated

If (adoRS.BOF And adoRS.EOF) Then   '--  empty recordset
   
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 12056895
>>I always make sure the recordset is populated by doing:

>>usr.MoveLast
>>usr.MoveFirst

This was the case before, but it is not needed with ADO.  Setting CursorLocation = adUseClient, should be enough.

Leon
0
 
LVL 5

Author Comment

by:abith
ID: 12061510
i used CursorLocation but i returned error

Runtime Error 3001

Arguments are of wrong type or out of acceptable range or arein conflit with one other.

mladenovicz  may be right
0
 
LVL 2

Expert Comment

by:x50Fade
ID: 12062736
hi abith....

The problem is with the MySQL driver that they released in order to access their DB.

I tried moving my app from MS SQL to MySQL several months ago, but alas....

Eventually I found some info on the MySQL site that said that the MySQL drivers does not support the recordcount propperty..which is very weird....cause it's supposed to kick MS SQL's ass.

Anyhows, I got some help from a few people who suggested I use the count function in a query in order to determine the number of records in that query.

I eventually ended up going back to MS SQl
0
 
LVL 2

Expert Comment

by:x50Fade
ID: 12062801
Oh Ja..I almost forgot...you can get this going by setting the cursor to adUseClient, but if I recall correctly, this had a massive performance decrease on my app...took like 15 seconds to return 10000 records.

Anyhows:

http://dev.mysql.com/doc/connector/odbc/en/manual.html

Here is a quote from the page:

ADO
When you are coding with the ADO API and Connector/ODBC you need to put attention in some default properties that aren't supported by the MySQL server. For example, using the CursorLocation Property as adUseServer will return for the RecordCount Property a result of -1. To have the right value, you need to set this property to adUseClient, like is showing in the VB code below: Dim myconn As New ADODB.Connection
Dim myrs As New Recordset
Dim mySQL As String
Dim myrows As Long

myconn.Open "DSN=MyODBCsample"
mySQL = "SELECT * from user"
myrs.Source = mySQL
Set myrs.ActiveConnection = myconn
myrs.CursorLocation = adUseClient
myrs.Open
myrows = myrs.RecordCount

myrs.Close
myconn.Close



Hope this helps
Regards
Fade
0
 
LVL 2

Expert Comment

by:x50Fade
ID: 12062811
http://dev.mysql.com/tech-resources/articles/vb-cursors-and-locks.html

Quote from Page:

adUseServer
When using the adUseServer server-side cursorlocation, responsibility for handling the data generated by a query lies with the database server. MySQL itself does not support server-side cursors, so the data handling is actually done by the Connector / ODBC driver. The benefit of server-side cursors is that we gain access to the dynamic cursor type. This allows us to see any changes to the data that are made by other users in the data our application is accessing.

For example: let's say we are selling tickets to a concert with our application, we need to know that a given seat is available for sale in real-time to ensure we do not double-book the seat. With a server-side cursor, we can be sure that the data we are manipulating is the most current possible. In addition, we have the ability to lock the data we are working on as we edit it, to make sure our changes are going to be posted to the database successfully.

With a server-side cursor (adUseServer), we have access to the adOpenDynamic and adOpenForwardOnly cursor types, and all four of the recordset lock types, which will be discussed below.

It should be noted that using a server-side cursor, and the adOpenDynamic cursor in particular, will result in a significant performance loss, and should be avoided if at all possible. In addition, certain functionality, such as the RecordCount property of a Recordset and the GetChunk and Appendchunk function for handling BLOB data, will fail or return abnormal results when used with a server-side cursor.

0
 
LVL 45

Expert Comment

by:aikimark
ID: 12094010
Notes:
1a. ADO does not always supply you with a valid recordcount parameter for recordset variables.
1b. some databases do not supply you with a valid recordcount parameter for recordset variables.
2. In general, I find that the recordcount property is not as reliable as it once was, especially for determining if there are records in my recordset variable that can be processed with a looping structure (DO / FOR)
3. Use the .BOF or .EOF recordset properties to determine the existence of records in a recordset variable.
4. If you need to know the number of records, run a separate query and store the results in a separate recordset variable.
Example:
mySQL = "SELECT Count(*) As RecCount From user"
myrs.Source = mySQL
Set myrs.ActiveConnection = myconn
myrsCount.CursorLocation = adUseClient
myrsCount.Open
myrows = myrsCount!RecCount

***For better performance, use a forward-only snapshot recordset type.
***For best performance, store this SQL in your database as a stored procedure.
0
 
LVL 5

Author Comment

by:abith
ID: 12099258
hi

how to get store in the database and retrive the record count.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 12101450
If you have the latest version of MySQL, look at the Create Procedure syntax.

You will instantiate an ADO command object and build your recordset object/variable upon the command object.
0
 
LVL 5

Author Comment

by:abith
ID: 12102051
hi
Can you get me some sample example or coding.  and how could i call the stored proc in the visual basic to get the record count.
0
 
LVL 7

Expert Comment

by:kmorris1186
ID: 12117490
you could just use this SQL statement.

Lets say you have a Database called TEST that has a table called Users, with a field called UserName that has EVERY record populated......

ConnString = "Driver={MySQL ODBC 3.51 Driver};Server=localhost; Port=3306; Option=131072; Stmt=; Database=TEST; Uid=USERNAME; Pwd=PASSWORD"
Set Conn = CreateObject("ADODB.Connection")
Conn.Open ConnString
QR1 = "Select Count(UserName) as TotalCount from Users;"
Set RS1 = Conn.Execute(QR1)
Dim RecordCount as integer
RecordCount = RS1.fields("TotalCount")
debug.print RecordCount

That is a rather easy workaround....
0
 
LVL 5

Author Comment

by:abith
ID: 12119716
hi

this example was stated to me in the previous replay by many perople.  you said something on the stores proc that is what i am to.

According to your programmer i have to write two sql statement to get count, and to get the records.
Any way thanks for your support.

Regards
Abith
0
 
LVL 7

Expert Comment

by:kmorris1186
ID: 12122762
sorry, i must have missed that suggestion.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 12123864
Abith,

The dynamic SQL examples we have supplied you will work.  As you have the time, learn about stored procs and using ADO Command objects with parameters in VB.
0
 
LVL 5

Author Comment

by:abith
ID: 12134780
hi all

so there is no way to get the record count if i use the ODBC driver from the www.mysql.com .  is there any other ODBC driver to connect to MYSQL database.

is there any class file avail that can connect to the mysql database

Regards
abith
0
 
LVL 7

Expert Comment

by:kmorris1186
ID: 12135064
I have not found anything other then the ODBC driver supplied at www.mysql.com.

i pulled this from the MySQL documentation at http://dev.mysql.com/doc/connector/odbc/en/manual.html.

ADO
    When you are coding with the ADO API and Connector/ODBC you need to put attention in some default properties that aren't supported by the MySQL server. For example, using the CursorLocation Property as adUseServer will return for the RecordCount Property a result of -1. To have the right value, you need to set this property to adUseClient, like is showing in the VB code below:

Dim myconn As New ADODB.Connection
Dim myrs As New Recordset
Dim mySQL As String
Dim myrows As Long

myconn.Open "DSN=MyODBCsample"
mySQL = "SELECT * from user"
myrs.Source = mySQL
Set myrs.ActiveConnection = myconn
myrs.CursorLocation = adUseClient
myrs.Open
myrows = myrs.RecordCount

myrs.Close
myconn.Close

    Another workaround is to use a SELECT COUNT(*) statement for a similar query to get the correct row count.
0
 
LVL 7

Expert Comment

by:kmorris1186
ID: 12135069
nevermind. i seen that was already posted....
0
 
LVL 5

Author Comment

by:abith
ID: 12137689
hi kmorris1186

kind attention before replying read the other suggestion given by the user.  anyway thanxs for you support.  is there any other driver.

Regards
Abith .K
0
 
LVL 45

Expert Comment

by:aikimark
ID: 12138624
abith,

I think I wrote, accurately, that ADO will not reliably populate the RecordCount property of recordset objects.  Your use of different drivers will not change this behavior.

Please use the example we have supplied you and report whether or not it solves your problem.

=============================
Although it isn't very efficient, you might be able to use the following SQL:

usr.Open "select *, (Select Count(*) from userinfo) As RecCount from userinfo ", DBmysql, adOpenDynamic
0
 
LVL 5

Author Comment

by:abith
ID: 12591908
hi
thanks mladenovicz
according to him the cursur location should must be set to the database not to the recordset variables

this solved me

Regards
Abith .K
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
passing a value with stream reader AFTER a ";" 3 83
MS Date Picker 64 bit 32 bit issue 12 63
using web browser with BING 40 136
How to read File Date Created using VB6 8 61
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

749 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