Request.Update to Re-Number Recordset Prob.

[J.D. Buehls]

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]

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]

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

 

[Bob Barrows [MVP]]

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