RecordCount with adOpenStatic = -1

Hi All,

I'm having a hell of a time with this...

From researching multiple websites, specifying the asOpenStatic cursor should return the correct RecordCount.

Here is my code:

sql = "SELECT UserID,UserName,Password,AccessLevelID,LockThreshold,Active FROM tbl_users WHERE UserName = '" & username & "' and Password = '" & password &"' "

set rs_login=Server.CreateObject("ADODB.Recordset")
rs_login.Open sql, conn, adOpenStatic', adLockReadOnly, adCmdTable

if rs_login.RecordCount <> 0 then

Response.Write("Login Successful" & "<br>")
Response.Write("Record Count: " & rs_login.RecordCount)

end if

I know I can use the select count(*) instead, but I've been researching this for quite some time now and I would like to know what's wrong.

Thx,

Why not use

If YourRecordSet.EOF Then
response.Write "Sorry No Records Found"
Else
'Output of Records found
End If

?????????

Just a simple question, but have you defined the variable adOpenStatic in your asp pages? adOpenStatic isn't defined by default, you have to define it a) yourself or b) include adodb.inc which is available from microsoft which defines all ADODB constants.


Dim lRS: Set lRS = CreateOBject("ADODB.RecordSet")
lRS.Open lCmd, , adOpenStatic

Response.Write lRS.RecordCount

-> w/o definition of adOpenStatic (which is then the same as "") it returns -1

if i do adOpenStatic = 3 and the execute it, it returns the valid RecordCount.

I use the .inc file from MS.

Perhaps your provider/driver doesn't support adOpenStatic? Which driver are you using and which database?

there are some properties, that are not supported on mySQL server; one of such is adUseServer cursorLocation for counting the total of affected rows.
if you use adUseServer cursorLocation, you will get -1 as a number of affected rows, no matter what (even, if you properly open the connection, ie. adOpenStatic)

right solution is to use adUseClient cursorLocation. Here is a sample snippet of code:

MyConn = ...connectionString...
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open MyConn, "", ""
Set RS = Server.CreateObject("ADODB.Recordset")
pogoj = "SELECT * FROM database"
RS.CursorLocation = adUseClient
RS.Open pogoj, Conn, adOpenStatic, adLockOptimistic
counter = RS.RecordCount

hope, this will help