?
Solved

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

Posted on 2014-02-07
5
Medium Priority
?
762 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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

718 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