Solved

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

Posted on 2014-02-07
5
709 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
CLI command keep running after close 7 56
MySQL recovery 7 31
Use Select Query to Return Results as a Form 9 43
Why is my $_POST not going to results page 10 39
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Creating and Managing Databases with phpMyAdmin in cPanel.
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…
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…

733 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