#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2002
    Location
    Vancouver, BC, Canada
    Posts
    1
    Rep Power
    0

    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,
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2002
    Posts
    2
    Rep Power
    0
    Why not use

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

    ?????????
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2002
    Location
    Bavaria, Germany
    Posts
    140
    Rep Power
    12
    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.
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2002
    Posts
    2
    Rep Power
    0
    I use the .inc file from MS.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2002
    Location
    Bavaria, Germany
    Posts
    140
    Rep Power
    12
    Code:
    <%
        Dim lConn
        Set lConn = CreateObject("ADODB.Connection")
    
        lConn.ConnectionString = "Provider=SQLOLEDB.1;User ID=username;Password=password;Data Source=hostname;Initial Catalog=database"
        lConn.Open
    
        Dim lRS
        Set lRS = CreateOBject("ADODB.RecordSet")
        lRS.Open "SELECT * FROM TTodo", lConn, 3
    
        Response.Write lRS.RecordCount
    %>
    Perhaps your provider/driver doesn't support adOpenStatic? Which driver are you using and which database?
  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Location
    Slovenia
    Posts
    1
    Rep Power
    0

    cursorLocation


    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
    Last edited by faithBHP; August 28th, 2003 at 05:05 AM.

IMN logo majestic logo threadwatch logo seochat tools logo