Updating recordset with line numbers 1

[DebbieDavis]

hello,

I have a recordset that I need to update with line numbers by job ID. For instance:

db.execute("update job set line_no=??? where jobid=1")

How do I go about looping through this recordset and adding line_no + 1 to the next and so on?

I've tried iCount=iCount+1 in a do loop and outside of a do loop and under a do loop, etc. but I keep getting the same number in the line_no field for each record (like 3) instead of incrementing (like 1,2,3). I hope this makes sense.

Lost and frustrated. Thank you in advance for your thoughts.

dd

 

[MrGreed]

try:

iCount=0 'default it

do until iCount=10 'you can replace with your own evaluation

iCount=iCount+1

db.execute("update job set line_no=" & iCount & " where jobid=1")


loop

Is this what you mean?


MrGreed

"did you just say Minkey?, yes that's what I said."

 

[DebbieDavis]

Yes, but I don't know what the last number count will be. In your example you have 10 but it could be 25, it could be 200. How would I handle that?

Thank you VERY much.

 

[rsshetty]

rs.open "select count(*) totcount from job where jobid = '1'"

lastcount = rs("totcount")
do until iCount=lastcount

iCount=iCount+1

db.execute("update job set line_no=" & iCount & " where jobid=1")


loop

rsshetty.

It's always in the details.

 

[MrGreed]

You would have to know somehow what your total count is, or how many records you need to update. If your going to use a loop you need to know when to stop it.



MrGreed

"did you just say Minkey?, yes that's what I said.

 

[DebbieDavis]

Hi rsshetty,

Thanks for your response.

In my test page I have 8 lines and after I ran your code, each line_no says 8. I need 1 for line 1, 2 for line 2, etc. Any other thoughts?

 

[MrGreed]

rs.open "select count(*) totcount from job where jobid = '1'"

lastcount = rs("totcount")
do until iCount=lastcount

iCount=iCount+1

if iCount=1 then
db.execute("update job set line_no=" & iCount & " where jobid=1")
else
'update records ignoring last update
db.execute("update job set line_no=" & iCount & " where jobid=1 and line_no<>" iCount-1 )

end if



loop


MrGreed

"did you just say Minkey?, yes that's what I said.

 

[MrGreed]

One change:

if iCount=lastcount+1 then


MrGreed

"did you just say Minkey?, yes that's what I said.

 

[MrGreed]

ok, totally retarded ignore my last post of if iCount=lastcount+1 then


MrGreed

"did you just say Minkey?, yes that's what I said.

 

[MrGreed]

and make this change:

'update records ignoring last update
db.execute("update job set line_no=" & iCount & " where jobid=1 and line_no>" iCount-1 )

get rid of <>

and that's it for me today.. to many brain farts.



MrGreed

"did you just say Minkey?, yes that's what I said.

 

[rsshetty]

Can u give me some field which is unique to each record?
in this eg,i've assumed jobname is a field and is unique to each record.


set rs = fairdb.execute("select * from job where jobid = '1'")
icount = 1
while not rs.eof
name = rs("jobname")[\b]
response.Write(name)
fairdb.execute ("update test set ln = '"&icount&"' where jobname = '"&name&"'")
response.Write("<br>")
icount = icount+1
rs.movenext
wend


Hope this helps
You need some unique field to differentiate between each record.

rsshetty.

It's always in the details.

 

[rsshetty]

I wanted to highlight it and hence the tags and [\b]

name = rs("jobname")[\b]

its actually

name = rs("jobname")

sorry abt that.
hope this helps.
rsshetty.


It's always in the details.

 

[DebbieDavis]

Well, rsshetty, what do you know, it worked great. I have an ID field, so that's what I used. Many, many thanks. That helped me a lot. Now I can move on to something else.

dd