473,324 Members | 2,248 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Request.Update to Re-Number Recordset Prob.

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
Jul 19 '05 #1
3 1616
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" <bu**************@yahoo.com> wrote in message
news:73**************************@posting.google.c 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

Jul 19 '05 #2


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!
Jul 19 '05 #3
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.
Jul 19 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: JT | last post by:
is there a way to clear out the request.form collection, or the entire request object, for that matter?? im using server.transfer and this can be troublesome in cases where you need the...
5
by: Jack | last post by:
Hi, I am trying to get a thorough understanding of a code where a addition or deletion of records can be done from a list of records. For addition part of the form, data is being obtained from set...
6
by: Drew | last post by:
I have been trying to figure out why I am getting this message... I have an application that is 20 different ASP pages that works in sequential order (step1, step2, step3...). After each step the...
5
by: mvr | last post by:
Hi all IIS 5.0, ASP, and https:// I have "DataEntrypage.asp" which is a data entry page(about 250 data elements includes text boxes, radio buttons, check boxes, drop down boxes etc). ...
5
by: Prabhat Nath | last post by:
Hi All, Is that possible that I can send request to IIS Server after a .ASP page is displayed? My Requirement is: Clinet will Fill the Details in one Request Form after the details are...
7
by: Shapiro | last post by:
I have a scenario where I log a resquest to a database table and update the request with a corresponding response including the response time. I am using an HttpModule to do this. My challenge...
2
by: Martin Z | last post by:
I'm using the TableAdapterHelper to set the connection and transaction properties on all the commands of all my typed table adapters.... I've checked at the time of the error and all the commands...
5
by: Jimmy | last post by:
How can I do that? Is it possible to send a server request (i.e. GET) without refreshing the web page? Using Javascript? Or Ajax (i.e. AjaxAnywhere)? Or if there's a way (i.e. using...
5
by: chromis | last post by:
Hi there, I've recently been updating a site to use locking on application level variables, and I am trying to use a commonly used method which copies the application struct into the request...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.