count records with a sql statement 1

[ndp]

Hi,
In my application, I am looping through recordset. for each record, I want to run a sql statement and if there is any record then proceed with other functions else go to the next record.

I have already created a recordset with ado in VB6. But, I don't know how to run the sql statement each time in loop and check whether the sql statement got any records or not.

Thanks
ndp

 

[NNIsarahd]

The Recordset object should have a RecordCount property. You may want to look into using that.

 

[esamsalah]

The record set object has a RecordCount property

Try this Example :
1 - Using ADO Control:
' u should use the propert pages to set the conn. string
' and the record source
Adodc1.Refresh
MsgBox Adodc1.Recordset.RecordCount

2 - Using Code
Dim sConn As String
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Program Files\Microsoft Visual Studio\VB98\NWIND.MDB;"

Dim oConn As New ADODB.Connection
oConn.Open sConn

Dim rsEmpCount As New ADODB.Recordset

Set rsEmpCount = oConn.Execute("SELECT Count(*) AS EMP_COUNT FROM Employees&quot

MsgBox rsEmpCount!EMP_COUNT

 

[zemp]

If you are using ADO code it will only return a .recordcount if your cursor type is static (adOpenStatic).

Thanks and Good Luck!

zemp

 

[ndp]

Actually, is there a way to count records through sql statement and store that value in variable without creating recordset?
Because, I am already looping through the recordset and each time don't want to create new recordset with the passed value. There must be a simple way. My sql statement gives count based on the value I pass through loop. If I use connection.execute strSQL, can I get that count in variable?

I hope I am clear enough!
Thank you all for helping me!
ndp

 

[zemp]

You can try looking into the Count() affggregate function. For example,

mySQL = "SELECT COUNT(fldSalary) FROM tblEmployees WHERE fldSalary > 30000"
RESULT: Find out how many employees have salary higher than 30,000

But I am not sure if this is what you are looking for. If not post your code and please explain again.





Thanks and Good Luck!

zemp

 

[Golom]

'fraid not. The only thing that "connection.Execute" returns is the "RecordsAffected" property. Besides, running your SQL from

conn.Execute SQL

still runs an SQL statement just as does

rs.Open SQL, ...

If you're worried about opening many recordsets then do something like

rs.Open "Select Key, Count(*) From tbl Group By Key"

and then use the rs.Find methods to locate the key value that you are looking for.

 

[ndp]

Thanks to all of you!

Finally I got it worked! I ended up using the method esamsalah suggested. It works excellent! Thank you so much.

Thanks
ndp