Solved

How to get the total rows in Mysql table using visual basic 6?

Posted on 2014-02-07
5
676 Views
Last Modified: 2014-02-07
Hi guys,

I need to pull the total rows in  a MySQL Query and put it in a variable, the code works fine but I don't know how to pull the total of each city


cSQLBusca = "SELECT Residence_city, Count(*) FROM transacciones WHERE Fecha_registra BETWEEN '" & cFechaIni & "' and '" & cFechaFin & "' GROUP BY Residence_city

I need to ouput the result like this:

Alahambra       3
Montebello      5
Los Angeles    11

And so on.

Thank you guys.
0
Comment
Question by:elm9999
  • 3
  • 2
5 Comments
 
LVL 5

Expert Comment

by:advfinance
ID: 39842478
I'm assuming you're already using ADO to query the database but cannot access the second field in the returned set (the Count(*))?

Either alias the field, like so

Count(*) AS CityCount

Open in new window


and refer to the field by name, or refer to the field by its ordinal number.

rs.Fields(1).Value

Open in new window


--
Chris
0
 

Author Comment

by:elm9999
ID: 39842545
Yes Chris I'm using ADO.

   
 Set rsTransacciones = New ADODB.Recordset
'cSQLBusca = "SELECT * FROM transacciones WHERE Fecha_registra BETWEEN '" & cFechaIni & "' and '" & cFechaFin & "' ORDER BY Ciudad_residencia"
rsTransacciones.Open cSQLBusca, conn, adOpenStatic, adLockOptimistic
   
If not (rsTransacciones.BOF And rsTransacciones.EOF) Then
     
cCiudadBase = rstransacciones!Ciudad_residencia
nTotal = ?


I just need to know how many records there are each city?
0
 
LVL 5

Accepted Solution

by:
advfinance earned 500 total points
ID: 39842557
With your original query (with the Count(*) in it).

nTotal = rstransacciones.Fields(1).Value

Open in new window


--
Chris
0
 

Author Comment

by:elm9999
ID: 39842627
Thank you very much Chris

That's all I needed and woks perfect!

 nTotal = rstransacciones.Fields(1).Value

Have a great weekend.

:)
0
 

Author Closing Comment

by:elm9999
ID: 39842628
Thank you.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MsgBox 4 61
How to send multiple emails at the same time in PHP 12 61
MySQL  on Tomcat 8 47
running code or pseudo code of table structure 5 27
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 …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

831 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