Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Insert Data from Form

Status
Not open for further replies.

puremm

Technical User
Oct 30, 2006
23
GB
I wonder if anyone can help? I have a form which creates a record in a database, when the form has been submitted it takes the user to another page with a form on it.

I'm basically looking to pass the ID of the record created on the first page, and have it inserted into the second form.

Does anyone know how to get the ID to pass from the first page to the second page???

Thanks in advanced.
 
what database are you using? for SQL Server you can use scope_identity() to fetch the recent ID value...

-DNG
 
I'm using Ms Access... unfortunately...
 
Works a treat, thanks very much....
 
Someone else could sneak in with another insert between your insert and looking up the ID.

I think if you insert the record using rs.addnew and so forth, you will get the autonumber back when you do the rs.update
I am pretty sure I have done this in the past. You may need to fiddle about with the ado parameters.
 
Just to point it out, doing a SELECT MAX(ID) is actually fairly dangerous if you have any kind of load, because it would be possible for someone else to do an INSERT before you get to the second page and try to get the ID back.

While it would be less efficient. You may be better off SELECTing back the record after you insert it in the first page. While this does cost you a second query, you should reduce the risk of grabbing the wrong ID.

Basically, if you have something like:
Code:
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "my connection string"

conn.Execute "INSERT INTO MyTable(MyField1,MyField2) VALUES('" & Request.Form("field1") & "','" & Request.Form("field2") & "')"

'example of getting ID back out
Dim rs, idfield
Set rs = conn.Execute("SELECT MyIDField FROM MyTable WHERE Field1 = '" & Request.Form("field1") & "' AND MyField2 = '" & Request.Form("field1") & "'"
If rs.EOF Then
   Response.Write "Oh Noes..Couldn't get id."
Else
   idfield = rs(0)
End If

Set rs = Nothing
conn.Close
Set conn = Nothing

'redirect
Response.Redirect "Page2.asp?id=" & idfield

Another option is available if your using the recordset object to add your new record. Using a recordset to add records to the dastabase is less efficient, but does give you a few extras. (Warning, this example is going to be extremely inefficient to keep it short):
Code:
Dim conn, rs, idfield
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "my connection string"
rs.Open "MyTable",conn

rs.AddNew
rs("MyField1") = Request.Form("field1")
rs("MyField2") = Request.Form("field2")
rs.Update
'the update sends the new record, so now that it has been created we should be able to read in the new ID
idfield = rs("MyIDField")
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

Response.Redirect "Page2.asp?id=" & idfield
%>

I haven't used that second method in years, but I vaguely recall that you might need to set it to dynamic in order for it to work (I could be wrong though). Also, you would want to limit the query instead of opening the whole table to reduce time and load on the system. This method may seem faster, but it is actually doing a great deal more work behind the scenes, compared to the first method that is just doing two simple executions against the db.

-T

 
For MS Access you can execute this command right after the insert:
Code:
select @@IDENTITY as NewID


Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard. [dragon]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top