ADO RecordCount doesn't

Discussion in 'ASP General' started by MikeR, Oct 4, 2005.

  1. MikeR

    MikeR Guest

    When I use the following, the RS.recordcount is -1. I also can't do a RS.moveprevious.
    What have I overlooked?
    Thanks,
    Mike

    dbname="DBQ=" & Server.MapPath("../db/my.mdb")
    set Conn=server.createobject("adodb.connection")
    Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & dbname

    sql = "SELECT * FROM Zips Where zipcode = '"& Request("zip") & "'"
    response.write "<p>" & sql
    set RS = Server.CreateObject("ADODB.recordset")
    RS.cursortype = adOpenDynamic
    RS.open sql, conn
     
    MikeR, Oct 4, 2005
    #1
    1. Advertising

  2. MikeR wrote:
    > When I use the following, the RS.recordcount is -1. I also can't do a
    > RS.moveprevious. What have I overlooked?
    > Thanks,
    > Mike
    >
    > dbname="DBQ=" & Server.MapPath("../db/my.mdb")
    > set Conn=server.createobject("adodb.connection")
    > Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & dbname


    Nothing to do with your problem, but: http://www.aspfaq.com/show.asp?id=2126

    >
    > sql = "SELECT * FROM Zips Where zipcode = '"& Request("zip") & "'"


    Again, nothing to do with your problem, but:
    http://www.aspfaq.com/show.asp?id=2096

    > response.write "<p>" & sql
    > set RS = Server.CreateObject("ADODB.recordset")
    > RS.cursortype = adOpenDynamic


    You think you're getting a dynamic cursor, but:
    http://www.adopenstatic.com/faq/jetcursortypes.asp

    > RS.open sql, conn


    I don't work with Jet, so I am a little surprised that the keyset or static
    cursor you are getting does not support recordcount. If you change the
    cursorlocation to adUseClient, you are guaranteed to get a cursor that
    supports bookmarks and recordcount. However, there are alternatives that
    will perform better:
    http://www.aspfaq.com/show.asp?id=2193

    For a further explanation of the effect of cursor type on record count, see
    here:
    http://msdn.microsoft.com/library/en-us/ado270/htm/mdprorecordcount.asp

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Oct 4, 2005
    #2
    1. Advertising

  3. MikeR

    Bob Lehmann Guest

    Change adOpenDynamic to adOpenKeySet.

    Or better yet, stop using select *, and add a column to your query for the
    record count and stop using the Open method.

    Set conn = Server.CreateObject("ADODB.Connection")
    conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & dbname

    sql = "SELECT whatever1, whatever2, whatever3, COUNT(*) as record_count FROM
    Zips Where zipcode = '"& Request("zip") & "'"

    Set rs = conn.Execute(sql)

    RecordCount = rs("record_count")

    Bob Lehmann

    "MikeR" <> wrote in message
    news:%...
    > When I use the following, the RS.recordcount is -1. I also can't do a

    RS.moveprevious.
    > What have I overlooked?
    > Thanks,
    > Mike
    >
    > dbname="DBQ=" & Server.MapPath("../db/my.mdb")
    > set Conn=server.createobject("adodb.connection")
    > Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & dbname
    >
    > sql = "SELECT * FROM Zips Where zipcode = '"& Request("zip") & "'"
    > response.write "<p>" & sql
    > set RS = Server.CreateObject("ADODB.recordset")
    > RS.cursortype = adOpenDynamic
    > RS.open sql, conn
     
    Bob Lehmann, Oct 4, 2005
    #3
  4. MikeR

    MikeR Guest

    Bobs -
    Thank you gentlemen!
    Mike

    MikeR wrote:
    > When I use the following, the RS.recordcount is -1. I also can't do a
    > RS.moveprevious. What have I overlooked?
    > Thanks,
    > Mike
    >
    > dbname="DBQ=" & Server.MapPath("../db/my.mdb")
    > set Conn=server.createobject("adodb.connection")
    > Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & dbname
    >
    > sql = "SELECT * FROM Zips Where zipcode = '"& Request("zip") & "'"
    > response.write "<p>" & sql
    > set RS = Server.CreateObject("ADODB.recordset")
    > RS.cursortype = adOpenDynamic
    > RS.open sql, conn
     
    MikeR, Oct 4, 2005
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Steve Caliendo

    RecordCount of a datareader

    Steve Caliendo, Jun 2, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    1,426
    John Oakes
    Jun 2, 2004
  2. nita
    Replies:
    1
    Views:
    904
    Saravana
    Nov 20, 2004
  3. ronaldlee

    Transfer ADO Code to ADO.NET

    ronaldlee, Dec 17, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    491
    Kevin Spencer
    Dec 17, 2004
  4. Replies:
    0
    Views:
    1,345
  5. Navin
    Replies:
    1
    Views:
    761
    Ken Schaefer
    Sep 9, 2003
Loading...

Share This Page