Solved

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

Posted on 2014-02-07
5
728 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

728 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