Problems with SQL Execution in VB

[np3il]

I am trying to execute SQL commands from VB6

...
strSQL = "SELECT Count([Stores].[StoreID]) AS iTotStore FROM Stores;"
rsStores.Open strSQL, dbConn
cmdSQL.CommandText = strSQL

cmdSQL.Execute

...

This command is executing ok but it is not assigning correctly to iTotStore variable.

What am I doing wrong here ???

Thanks
np3il

 

[dboddington]

In the code you have, you are assigning an alias to the result ie the recordset will contain a field called iToStore. The field has to be assigned to a variable. This is the code I wrote that works:

Private Sub cmdCount_Click()
    Dim rsStores As ADODB.Recordset
    Dim strSQL As String
    Dim intResult As Integer
    Set rsStores = New ADODB.Recordset
    
    rsStores.ActiveConnection = CurrentProject.Connection
    
    rsStores.Open "SELECT Count(EmployeeID) As " &_ 
                    "Result FROM tblEmployee;"
    
    intResult = rsStores!Result
    
    Me.txtNumber = intResult
    
End Sub

This should give you the result, good luck!

Dan

 

[dboddington]

Apologies, this is written in Access VBA - but the SQL techniques are the same.

Dan

 

[np3il]

Dan

That work fine ...

Thanks

np3il