Solved

MYSQL Recordcount

Posted on 2004-09-14
23
2,067 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
 
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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…
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…

757 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

21 Experts available now in Live!

Get 1:1 Help Now