Submit form to database and display back data

[DrmWvr]

Hi,
is it possible to submit a form to the database (Access) and after the submit button is clicked another asp page loads with the data that has just been submitted, displayed.

I'm new to asp, so any help is appreciated.

Thanks in advance,
Marc
Ottawa,Canada

 

[mwolf00]

Yes, but since you are displaying it again, you may want to use the form itself to display the items.

<%
set objCn = server.createObject(&quot;adodb.connection&quot
set objRS = server.createObject(&quot;adodb.recordset&quot
objCn.Open yourConnectionString

if request.servervariables(&quot;HTTP_METHOD&quot = &quot;post&quot; then
uName = request(&quot;uName&quot
uPhone = request(&quot;uPhone&quot
strSQL = &quot;INSERT INTO myTable (uName, uPhone) &quot;&_
&quot;VALUES ('&quot; & uName & &quot;','&quot; & uPhone & &quot;')&quot;
objCN.execute(strSQL)
end if
'since you already have the info, you don't need to query the db to get it and display it
objCn.close
set objCn = nothing
set objRs = nothing
%>

<form method=post action=&quot;thisPage.asp&quot;>
<input name=&quot;uName&quot; value=&quot;<%=uName%>&quot;>
<input name=&quot;uPhone&quot; value=&quot;<%=uPhone%>&quot;>
<input type=submit>
</form> Get the Best Answers! faq333-2924
Is this an asp FAQ? faq333-3048

mikewolf@tst-us.com

 

[DrmWvr]

Thanks Mike,
the reason I need to query the db for each submit is the get the primary key (ID number), which it will generate.
Any ideas?
Marc

 

[mwolf00]

Do you have any other unique data in the table (other than ID)? I use stored procedures (w/ SQL Server) so I just use the @@identity feature. I don't think that you have that in access. Since that's not available you need to reselect the data you just entered and order by id desc...


if request.servervariables(&quot;HTTP_METHOD&quot = &quot;post&quot; then
uName = request(&quot;uName&quot
uPhone = request(&quot;uPhone&quot
strSQL = &quot;INSERT INTO myTable (uName, uPhone) &quot;&_
&quot;VALUES ('&quot; & uName & &quot;','&quot; & uPhone & &quot;')&quot;
objCN.execute(strSQL)
strSQL = &quot;SELECT TOP 1 * FROM myTable WHERE &quot;&_
&quot;uName = '&quot; & uName & &quot;' &quot; &_
&quot;AND uPhone = '&quot; & uPhone & &quot;' &quot;&_
&quot;ORDER BY pkID DESC&quot;
set objRS = objCN.execute(strSQL)
if not objRS.EOF then
pkID = objRS(&quot;pkID&quot
else
response.write &quot;An error has occurred&quot;
end if

end if
Get the Best Answers! faq333-2924
Is this an asp FAQ? faq333-3048

mikewolf@tst-us.com

 

[BDC2]

Be careful using Access to retrieve the identity key of the last item inserted. If the database is being accessed by more than one user simultanously you may not get back the correct key.

You can use the @@IDENTITY property in SQL,

http://www.kamath.com/tutorials/tut007_identity.asp

but I'm not sure if Access has anything similar, you may have to research.

BDC.

 

[mwolf00]

BDC2 -

That's why I use SELECT TOP 1......ORDER BY pkID DESC

Hopefully only one set of exactly the same data gets inserted in the instant between executing the two queries. Of course, it could happen. Yet another reason that critical data usually requires something more than MS Access. Get the Best Answers! faq333-2924
Is this an asp FAQ? faq333-3048

mikewolf@tst-us.com

 

[BDC2]

We posted at the same time mwolf00, so mine wasn't a reply to your thread.

That always happens to me, maybe I should learn to type faster ;-)

 

[john1carrot]

On the same tack is it possible to &quot;POST&quot; data through redirection?
Using Ultradev creates a &quot;revisited&quot; page with the contents of the form filled. I want to pass a field's contents on to the next page without appending it to the URL.But I can't find a way to resubmit a form from within the Code portion of the page.

 

[BDC2]

You can't post form data without submitting the form

I would resubmit the form using javascript

document.TheFormName.submit();

BDC.

 

[exodus300]

You could put the form data in a cookie or session variable before redirecting, then on the next page read from the cookie/session variable? [Thanks in advance|Hope I helped you]
Exodus300

 

[dHart719]

' This is my advise
Set Conn = Server.CreateObject(&quot;ADODB.Connection&quot
Conn.Open dbConnection
set Rs = Server.Createobject(&quot;ADODB.Recordset&quot
Rs.Open &quot;yourTable&quot;, Conn, 2, 2
Rs.Addnew
Rs(&quot;field1&quot=valueforfield1
Rs(&quot;field2&quot=valueforfield2
...
Rs.Update
Rs.Movelast
this_is_your_key_id = Rs(&quot;Autonumber_ID&quot
Rs.Close
Set Rs=Nothing
Conn.Close
Set Conn=Nothing
Response.Write this_is_your_id 'which was given out after you have inserted the values in a new record of your table.

 

[DrmWvr]

Thanks for your help guys!!!
Your examples are great!!!

Marc