sql statement

[rozzay]

hi I was wondering if some could please help me with this.
I am trying to retrieve a count of how many records are in the table. so i would do a sql statement and executing and then pull the value into the textbox, unfortunately it does not do that. thanks in advance
============================================================
Private Sub TotalPartners()

Dim strConnect As String
Dim rsTotal As Recordset
Dim strSQL As String

Set gcnAP = New Connection
Set rsTotal = New Recordset

If bTestProd = True Then
strSQL = "SELECT COUNT (*) FROM registuser"
Else
strSQL = "SELECT COUNT (*) FROM registuser_pro"
End If

strConnect = "provider sqloledb;server=hqsql02;database=edi;uid=" & struid &";pwd="&strpwd
rsTotal.Open strSQL, strConnect, adOpenKeyset,
adLockOptimistic, adCmdText
txtTotal.Text = rsTotal

End Sub

 

[chadt]

If its a small recordset then use rsTotal.RecordCount.

If its a big recordset you may have to do

Dim x as integer
x = 0
Do while not rs.eof
x = x + 1
rsTotal.movenext
loop

 

[Guest_imported]

I think you are doing it mostly right ( I am assuming that your connection string settings are valid). I think you need to change your SQL statement and the way you are trying to get results from the recordset:

strSQL = "SELECT COUNT (*) AS CNT FROM registuser"


txtTotal.Text = rsTotal.Fields("CNT&quot.Value

 

[jmarler]

You could use RecordCount in either a big recordset or a small recordset as long as you are working with a client side cursor. Although if all you want is the cound of records, you should not bring back all the records and use recordcount. Your first approach use the SQL count command is the correct approach (brings back only 1 record as opposed to several). Your problem is that you were setting the text directly to the recordset and not the value in the field. try this instead . . .

Private Sub TotalPartners()

Dim strConnect As String
Dim rsTotal As Recordset
Dim strSQL As String
   
Set gcnAP = New Connection
Set rsTotal = New Recordset

If bTestProd = True Then
   strSQL = "SELECT COUNT (*) AS TotalCount FROM registuser"
Else
   strSQL = "SELECT COUNT (*) AS TotalCount FROM registuser_pro"
End If

strConnect = "provider sqloledb;server=hqsql02;database=edi;uid=" & struid &";pwd="&strpwd
rsTotal.Open strSQL, strConnect, adOpenKeyset,
adLockOptimistic, adCmdText
txtTotal.Text = rsTotal.Fields("TotalCount").Value

End Sub

- Jeff Marler B-)

 

[CasperTFG]

Or you could do it this way. Which would be the esaiest in that it would require the least in the way of code changes.
[tt]
Dim strConnect As String
Dim rsTotal As Recordset
Dim strSQL As String

Set gcnAP = New Connection
Set rsTotal = New Recordset

If bTestProd = True Then
strSQL = "SELECT COUNT (*) FROM registuser"
strSQL = "SELECT * FROM registuser"
Else
strSQL = "SELECT COUNT (*) FROM registuser_pro"
strSQL = "SELECT * FROM registuser_pro"
End If

strConnect = "provider sqloledb;server=hqsql02;database=edi;uid=" & struid &";pwd="&strpwd

rsTotal.Open strSQL, strConnect, adOpenKeyset,
adLockOptimistic, adCmdText
txtTotal.Text = rsTotal.RecordCount
[/tt]
Craig, mailto:sander@cogeco.ca

"Procrastination is the art of keeping up with yesterday."

I hope my post was helpful!!!

 

[CasperTFG]

There are just too many ways to code the same thing...

LOL LOL LOL LOL LOL LOL LOL LOL LOL LOL LOL LOL

Good work everyone. Craig, mailto:sander@cogeco.ca

"Procrastination is the art of keeping up with yesterday."

I hope my post was helpful!!!

 

[jmarler]

Craig,
I was looking at performance (specifically netowrk traffic) Why bring the entire recordset across the network (assuming this is a networked application) when all you need is one row with the count. Let the DB server count the record for you locally and then simply return the total.
But yes, in most cases the code you wrote would work. However, remember that on an ADO recordset, the record count property will return a 0 when using a server side cursor (the default setting) with a forward only cursor.
Just some thoughts . . . - Jeff Marler B-)