[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

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

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

591 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