recordcount problem .

[GOBI]

Dim SDaT As String
Dim CTiM As String
 SDaT = Format(Date, "dd/mm/yy")
 CTiM = Format(Time, "hh:mm")
     conn.connectionsettings="AW"
 Dim rs As New ADODB.Recordset
 Set rs = New ADODB.Recordset
 rs.Open "SELECT * FROM Attendance where RIC='rnames(fi)' & DateIn='SDaT' ", conn, adOpenStatic, adLockOptimistic, adCmdTable
 msgbox rs.RecordCount

i run the above code , but somehow i wasn't able to get any recordcount even though the record exist in the database but the code just by pass the msgbox rs.recordcount and execute the next line ? is any thing wrong with mine rs.open statement ?

[pnish]

In your Open statement you need to replace adOpenStatic with adOpenKeyset or adOpenDynamic

[GOBI]

but i could get -1 froom the record count , anyway parameter still need to be changed ?

[brucevde]

The RecordCount property will always be -1 when using a Server Side cursor. Change the CursorLocation property to adUseClient.

Do you use On Error Resume Next? I always thought using a SQL statement and adCmdTable in the rs.Open statement would raise an error. Maybe it depends on the provider.

[szlamany]

I cannot even see how that SELECT statement is working - you are not concatenating the two VB variables into the string...

But at any rate you could use a trick...

SELECT (SELECT COUNT(*) FROM ...),* FROM ...

Here I am adding a SUBQUERY to the start of the SELECT list that is the exact same WHERE clause as the SELECT itself, but does a COUNT(*) - which will be the record count.

Here you get a RECORDCOUNT in each ROW of the total number of rows in the recordset.

Sloppy, ugly, not elegant - but it will work.

[Armbruster]

Originally posted by szlamany
I cannot even see how that SELECT statement is working - you are not concatenating the two VB variables into the string...

But at any rate you could use a trick...

SELECT (SELECT COUNT(*) FROM ...),* FROM ...

Here I am adding a SUBQUERY to the start of the SELECT list that is the exact same WHERE clause as the SELECT itself, but does a COUNT(*) - which will be the record count.

Here you get a RECORDCOUNT in each ROW of the total number of rows in the recordset.

Sloppy, ugly, not elegant - but it will work.

szlamany is absolutely correct. You use two conditions in your where clause but you forgot to use the AND operator and you call external functions from within the sql string. Try it like this . . .

Dim SDaT As String
Dim CTiM As String
 SDaT = Format(Date, "dd/mm/yy")
 CTiM = Format(Time, "hh:mm")
     conn.connectionsettings = "AW"
 Dim rs As New ADODB.Recordset
 Set rs = New ADODB.Recordset
 rs.Open "SELECT * FROM Attendance WHERE RIC='" & rnames(fi) & "' AND DateIn='SDaT'", conn, adOpenStatic, adLockOptimistic, adCmdTable
 MsgBox rs.RecordCount

[Armbruster]

Sometimes, it's also easier to build the sql string first. Makes the code a little cleaner . . . .

Dim SDaT As String
Dim CTiM As String
Dim strSQL As String
 SDaT = Format(Date, "dd/mm/yy")
 CTiM = Format(Time, "hh:mm")
     conn.connectionsettings = "AW"
 Dim rs As New ADODB.Recordset
 Set rs = New ADODB.Recordset
 strSQL = "SELECT * FROM Attendance WHERE RIC='" & rnames(fi) & "' AND DateIn='SDaT'"
 rs.Open strSQL, conn, adOpenStatic, adLockOptimistic, adCmdTable
 MsgBox rs.RecordCount