Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Request.Update to Re-Number Recordset Prob.

Reply
Thread Tools

Request.Update to Re-Number Recordset Prob.

 
 
J.D. Buehls
Guest
Posts: n/a
 
      06-01-2004
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
 
Reply With Quote
 
 
 
 
Steven Burn
Guest
Posts: n/a
 
      06-01-2004
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) om...
> 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



 
Reply With Quote
 
 
 
 
J.D. Buehls
Guest
Posts: n/a
 
      06-01-2004


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!
 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      06-01-2004
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.


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
ASP Problem Updating Recordset =?Utf-8?B?a2JyYWQ=?= Microsoft Certification 3 04-10-2004 11:42 AM
recordset error/perl messages gary artim Perl 0 02-06-2004 01:50 AM
RecordSet.Move or RecordSet.AbsolutePosition?? Hung Huynh ASP General 8 09-24-2003 11:07 AM
win32 adodb/mdb; con.execute returns undef and not recordset. Vikas Yadav Perl 0 09-09-2003 09:10 AM
reuse repeater with new recordset Bob ASP .Net 0 07-07-2003 07:03 PM



Advertisments