Insert Data from Form

[puremm]

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.

 

[DotNetGnat]

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

-DNG

 

[puremm]

I'm using Ms Access... unfortunately...

 

[DotNetGnat]

try:

Select Max(ID) from mytable

on the second page...

-DNG

 

[puremm]

Works a treat, thanks very much....

 

[DotNetGnat]

no problem...glad to assist.

-DNG

 

[petermeachem]

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.

 

[Tarwn]

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:

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):

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

 

[tsdragon]

For MS Access you can execute this command right after the insert:

select @@IDENTITY as NewID

Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.