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.