ASP Problem for ASP Guru

[digitalmelon]

Ok here is the scenario.

I have a five step registration process. Hence I am using 5 forms. When each form is submitted the data from the form is posted to the database and the user is then sent to the next form. The problem I have having is between forms 1 and 2. When form 1 is posted to the database with the persons personal information including username and password they are given an ID number(autonumber - MS Access DB). The problem is that I need that ID number in the second form. This is how I get the ID number from the DB:

//////////////////

Set rs = Server.Createobject("ADODB.Recordset")
sql2 = "SELECT * FROM DEL_TABLE"
rs.open sql2,conn,3,3
rs.movelast
ID = rs("ID")
rs.close
set rs=nothing
Application.Unlock

'FOR DEBUGGING
'response.write SQL

response.redirect "regstep2.asp?ID=" & ID

//////////////////

Heres the problem. If I let it run the way it is above and move to the next form then the SQL statement does NOT post the data to the database. BUT, if I comment out the response.redirect and uncomment the response.write SQL then it DOES post the data to the database.

Can anyone of you ASP gurus out there make any sense of that? I hope this makes some sense.

Thanks in advance

 

[Khayman]

i'm not entirly sure what you mean.

If you just comment the redirect, and leave the write commented, does it work?

you only need to get the id on the second form, so why does it run in the first form?

anychance of getting a look at the whole page code?

just an FYI, if you select MAX(ID) instead of * you will just return the most recent id and not the whole table, it will be quicker

 

[digitalmelon]

Originally posted by Khayman
i'm not entirly sure what you mean.

See below:

If you just comment the redirect, and leave the write commented, does it work?

Yes it writes the SQL statement to the page without errors and when I check the database the info has been posted.

you only need to get the id on the second form, so why does it run in the first form?

as far as I know it doesn't run in the first page. I don't even have an ID until the first form data is posted to the database and Access then assigns it an autonumber

anychance of getting a look at the whole page code?

I wish I could show you but there are some proprietary code issues. I am not able to share anymore

just an FYI, if you select MAX(ID) instead of * you will just return the most recent id and not the whole table, it will be quicker

thanks, I am not familiar with that code. So the SQL statement would appear: "SELECT MAX(ID) FROM DEL_TABLE" ?

 

[Khayman]

I'm sorry, i seem to have lost what the problem is, and its hard to tell without seing the code. Let me see if i can't work out whats going on

you have one page that takes user details in a form using <form> tags?

does the "action" property send the data to the same page?
if so why not send it direct to the second page, then get the id number, then you won't have to use a redirect

 

[digitalmelon]

You know Kayman thats a darn good idea! And so simple...I don't know why I didn't think of that. Sometimes it just takes a fresh pair of eyes. Thanks for your help man!

 

[digitalmelon]

Crap Khayman! Now I remember why I couldn't do that. I need to use Application.Lock in order to not allow anyone else to write to the database before I get the ID number. I am going to attach the asp file that the form goes to for you to have a look at. Keep in mind that all the coding in the asp file is not complete.

Here is the form action: <form name="form" method="post" action="register.asp?regstep=1">

Also, each forms action points to register.asp and depending on the form register.asp handles it in a different way. Have a look and let me know what you think.

Thanks.

 

[Khayman]

no attachment

 

[aScoundral]

why dont you just write a sub query that dumps the ID number into your forms' textbox/label automatically based on the personal info they provided? OR since it's autonumber, just grab the last record out of the database...

 

[digitalmelon]

Here is what I ended up doing:

<%
dim rs
dim sql2
Application.Lock
conn.execute SQL
Set rs = Server.Createobject("ADODB.Recordset")
SQL2 = "SELECT * FROM DELEGATE"
rs.open SQL2,conn,3,3
rs.movelast
ID = rs("ID")
rs.close
set rs=nothing
Application.Unlock
%>

Worked perfectly!