Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Get the row count into a variable (Read 5276 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Get the row count into a variable
08.08.11 at 16:24:15
 
Get the row count into a variable
 
Quote:
Surely there is a easy direct way to find how many rows your query just returned and assign that number to a variable.  I have scoured the SQLXL website, but am not convinced that I've found a good method.  I'm trying to insert a single log record into one specific table within a loop that is executing many queries.  This insert is right after my select.  Then I go back to the top of the loop and run a new query.

 
A good way to determine the number of rows that a statement returned is to use the row counter SQL*XL uses when it moves through the records. That will always return the correct number of rows, so even if you canceled the retrieval of rows.
 
As a demonstration I have written the following short macro. Explanation:
I am querying authors that contains some 6000 records (see the biblio database in our test databases collection).
The .execute statement starts pumping the data back to Excel. Once it completes or when the user presses cancel the next statement is run. Here we copy the .dynaset.row valiable into our local lngRows variable. This is the number of rows it fetched.
 
Sub Rows_retrieved()
  Dim lngRows as Long
 
  SQLXL.Sql.setText "select  * from authors"
  SQLXL.Targets(litExcel).StartFromCell = "$C$3"
   
  With SQLXL.Sql.Statements(1)
    Set .Target = SQLXL.Targets(litExcel)
    .optimiseForLargeQuery = False
    .ShowParametersDlg = False
    .ShowResultsetDlg = False
    .Execute
    lngRows = .dynaset.Row
 
    MsgBox "Numer of rows retrieved is " & lngRows
  End With
End Sub
Back to top
 
« Last Edit: 08.08.11 at 16:28:44 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Row count: using SQLQueryValue
Reply #1 - 08.08.11 at 16:39:09
 
Row count: using SQLQueryValue
 
The preferred method to get the number of rows returned is by using the dynaset.row value. However it can also be obtained by running an additional query. A very quick way to do this is through the SQLQueryValue function.  
 
Example:
 
Public Sub CountRows()
  Dim lng As Long
   
  lng = SQLQueryValue("select count(*) from authors")
  MsgBox lng
End Sub
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1