Request.Update to Re-Number Recordset Prob.

Discussion in 'ASP General' started by J.D. Buehls, Jun 1, 2004.

  1. J.D. Buehls

    J.D. Buehls Guest

    I have a bit of code that goes through a recordset and renumbers the
    records from 1 to X. I could swear that this code worked before but
    now when it is run,
    all of the records end up being numbered the same thing (the number of
    total records in the set). For example if there were 16 records they
    would all end up being renumbered 16 instead of 1, 2, 3... Can
    someone take a look at this. If I cant get this to work I might have
    to abandon the recordset method but this seems a simple enough task
    and the recordsets are pretty small.



    strSQL = "SELECT POSCODE, IDCODE1, IDCODE2, RESP_NUM, RESP FROM
    jambue.RESP WHERE POSCODE = '" & id & "' AND IDCODE1 ='" & dept & "'
    AND IDCODE2 ='" & unit & "' ORDER BY RESP_NUM"

    ' Response.Write strsql
    Set rstDBEdit = Server.CreateObject("ADODB.Recordset")
    rstDBEdit.Open strsql, CONN_STRING, adOpenStatic, adLockOptimistic
    'dim reNum
    reNum=1
    do while not rstdbedit.eof
    rstDBEdit.fields("RESP_NUM").Value = reNum
    rstDBEdit.Update
    Response.Write reNum & " "
    reNum=reNum+1
    Response.Write reNum
    rstDBEdit.MoveNext
    loop
    rstDBEdit.Close
    Set rstDBEdit = Nothing
     
    J.D. Buehls, Jun 1, 2004
    #1
    1. Advertising

  2. J.D. Buehls

    Steven Burn Guest

    valNum = 1
    Do Until Rst.EOF
    rst("TheIDField")=valNum + 1
    rst.MoveNext
    Loop
    rst.Update

    --

    Regards

    Steven Burn
    Ur I.T. Mate Group
    www.it-mate.co.uk

    Keeping it FREE!


    "J.D. Buehls" <> wrote in message
    news:...
    > I have a bit of code that goes through a recordset and renumbers the
    > records from 1 to X. I could swear that this code worked before but
    > now when it is run,
    > all of the records end up being numbered the same thing (the number of
    > total records in the set). For example if there were 16 records they
    > would all end up being renumbered 16 instead of 1, 2, 3... Can
    > someone take a look at this. If I cant get this to work I might have
    > to abandon the recordset method but this seems a simple enough task
    > and the recordsets are pretty small.
    >
    >
    >
    > strSQL = "SELECT POSCODE, IDCODE1, IDCODE2, RESP_NUM, RESP FROM
    > jambue.RESP WHERE POSCODE = '" & id & "' AND IDCODE1 ='" & dept & "'
    > AND IDCODE2 ='" & unit & "' ORDER BY RESP_NUM"
    >
    > ' Response.Write strsql
    > Set rstDBEdit = Server.CreateObject("ADODB.Recordset")
    > rstDBEdit.Open strsql, CONN_STRING, adOpenStatic, adLockOptimistic
    > 'dim reNum
    > reNum=1
    > do while not rstdbedit.eof
    > rstDBEdit.fields("RESP_NUM").Value = reNum
    > rstDBEdit.Update
    > Response.Write reNum & " "
    > reNum=reNum+1
    > Response.Write reNum
    > rstDBEdit.MoveNext
    > loop
    > rstDBEdit.Close
    > Set rstDBEdit = Nothing
     
    Steven Burn, Jun 1, 2004
    #2
    1. Advertising

  3. J.D. Buehls

    J.D. Buehls Guest

    This (the update outside the loop) just gives me an error saying either
    BOF or EOF or record is deleted error.
    I am doing this little operation after I delete out a record from the
    database with this bit of code. I then open up a new recordset which is
    what I need to renumber since the recordset will be nonsequential. I
    cant figure out why instead of renumbering them as it should it numbers
    them all the Number of records there were after the delete.

    Set cnnDBEdit = Server.CreateObject("ADODB.Connection")
    cnnDBEdit.Open CONN_STRING
    strSQLdelresp = "DELETE FROM jambue.RESP " _
    & "WHERE (POSCODE = '" & id & "' AND IDCODE1 ='" & dept & "' AND
    IDCODE2 ='" & unit & "') AND RESP_NUM IN (" & Request.Form("chk") &
    ")"
    ' Response.Write strsqldelresp
    cnnDBEdit.Execute(strSQLdelresp)
    cnnDBEdit.Close
    Set cnnDBEdit = Nothing



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    J.D. Buehls, Jun 1, 2004
    #3
  4. J.D. Buehls wrote:
    > I have a bit of code that goes through a recordset and renumbers the
    > records from 1 to X. I could swear that this code worked before but
    > now when it is run,
    > all of the records end up being numbered the same thing (the number of
    > total records in the set). For example if there were 16 records they
    > would all end up being renumbered 16 instead of 1, 2, 3... Can
    > someone take a look at this. If I cant get this to work I might have
    > to abandon the recordset method but this seems a simple enough task
    > and the recordsets are pretty small.
    >
    >
    >
    > strSQL = "SELECT POSCODE, IDCODE1, IDCODE2, RESP_NUM, RESP FROM
    > jambue.RESP WHERE POSCODE = '" & id & "' AND IDCODE1 ='" & dept & "'
    > AND IDCODE2 ='" & unit & "' ORDER BY RESP_NUM"
    >
    > ' Response.Write strsql
    > Set rstDBEdit = Server.CreateObject("ADODB.Recordset")
    > rstDBEdit.Open strsql, CONN_STRING, adOpenStatic, adLockOptimistic
    > 'dim reNum


    Why did you comment that line out?

    > reNum=1
    > do while not rstdbedit.eof
    > rstDBEdit.fields("RESP_NUM").Value = reNum
    > rstDBEdit.Update


    You should do this here just to check:
    response.write rstDBEdit("RESP_NUM").Value

    > Response.Write reNum & " "
    > reNum=reNum+1
    > Response.Write reNum
    > rstDBEdit.MoveNext
    > loop
    > rstDBEdit.Close
    > Set rstDBEdit = Nothing


    I'm not clear about why you are bothering to renumber these records. I
    assume they are already in the proper order given that your ORDER BY clause
    is working ... however,
    Instead of that reNum business, I would use the recordset's
    AbsolutePosition. Plus, I would use an explicit connection object and a
    disconnected recordset:

    Set rstDBEdit = Server.CreateObject("ADODB.Recordset")
    rstDBEdit.cursorlocation = adUseClient
    Set cn= Server.CreateObject("ADODB.Connection")
    cn.Open CONN_STRING
    rstDBEdit.Open strsql, cn, , adLockBatchOptimistic,adCmdText
    Set rstDBEdit.ActiveConnection = Nothing
    cn.close
    do while not rstdbedit.eof
    response.write "<BR>before: " & rstDBEdit("RESP_NUM").Value
    rstDBEdit("RESP_NUM").Value = rstDBEdit.AbsolutePosition
    response.write "<BR>after: " & rstDBEdit("RESP_NUM").Value
    rstDBEdit.Update
    rstDBEdit.MoveNext
    loop
    cn.Open
    Set rstDBEdit.ActiveConnection = cn
    rstDBEdit.UpdateBatch
    rstDBEdit.Close: Set rstDBEdit=nothing
    cn.close: set cn=nothing

    HTH,
    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Jun 1, 2004
    #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. drishtik

    dataset update prob

    drishtik, Sep 9, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    632
  2. paraag

    vhdl 2 blif prob

    paraag, Feb 18, 2004, in forum: VHDL
    Replies:
    0
    Views:
    1,200
    paraag
    Feb 18, 2004
  3. Brian Birtle
    Replies:
    2
    Views:
    2,112
    John Saunders
    Oct 16, 2003
  4. Hung Huynh
    Replies:
    8
    Views:
    332
    Bob Barrows
    Sep 24, 2003
  5. andrewdreib

    Request.querystring or Recordset.GetString?

    andrewdreib, Feb 28, 2005, in forum: ASP General
    Replies:
    1
    Views:
    168
    Jason Brown [MSFT]
    Mar 3, 2005
Loading...

Share This Page