mharcais
asked on
Hidden field with autonumber so that each form updates the same record
Form 1. Collects data from 5 fields. Sends it to ASP page which enters it into database. Forwards to...
Form 2. Collects data from 6 fields. Sends it to ASP page which enters it into the SAME record as the previous form.
This is important.
You will need to be patient, I am beginner, asked to do this hard (for me) task.
Please do not answer if you are not prepared to help me get the thing up and running, with real, easily adaptable, examples of code.
form1 and form2 are on the same page? and ,by the way , it's regular Asp or Asp.NET?
ASKER
This is ordinary ASP, not ASP.NET
Form 1 submits itself to another ASP page which enters the data (add_to_database1.asp) and then forwards me to Form 2 which submits itself to add_to_database2.asp etc etc
Add_to_database1.asp code currently is:
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsAddComments 'Holds the recordset for the new record to be added to the database
Dim strSQL 'Holds the SQL query for the database
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("mydatabase
'Create an ADO recordset object
Set rsAddComments = Server.CreateObject("ADODB
'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT * FROM mytable;"
'Set the cursor type we are using so we can navigate through the recordset
rsAddComments.CursorType = 2
'Set the lock type so that the record is locked by ADO when it is updated
rsAddComments.LockType = 3
'Open the Timepoint table using the SQL query held in the strSQL varaiable
rsAddComments.Open strSQL, adoCon
'Tell the recordset we are adding a new record to it
rsAddComments.AddNew
'Add a new record to the recordset
rsAddComments.Fields("2") = Request.Form("2")
rsAddComments.Fields("3") = Request.Form("3")
rsAddComments.Movelast
'Write the updated recordset to the database
rsAddComments.Update
'Reset server objects
rsAddComments.Close
Set rsAddComments = Nothing
Set adoCon = Nothing
============
Thanks
Form 1 submits itself to another ASP page which enters the data (add_to_database1.asp) and then forwards me to Form 2 which submits itself to add_to_database2.asp etc etc
Add_to_database1.asp code currently is:
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsAddComments 'Holds the recordset for the new record to be added to the database
Dim strSQL 'Holds the SQL query for the database
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("mydatabase
'Create an ADO recordset object
Set rsAddComments = Server.CreateObject("ADODB
'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT * FROM mytable;"
'Set the cursor type we are using so we can navigate through the recordset
rsAddComments.CursorType = 2
'Set the lock type so that the record is locked by ADO when it is updated
rsAddComments.LockType = 3
'Open the Timepoint table using the SQL query held in the strSQL varaiable
rsAddComments.Open strSQL, adoCon
'Tell the recordset we are adding a new record to it
rsAddComments.AddNew
'Add a new record to the recordset
rsAddComments.Fields("2") = Request.Form("2")
rsAddComments.Fields("3") = Request.Form("3")
rsAddComments.Movelast
'Write the updated recordset to the database
rsAddComments.Update
'Reset server objects
rsAddComments.Close
Set rsAddComments = Nothing
Set adoCon = Nothing
============
Thanks
Using your existing code:
Comment from thunderchicken
Date: 12/12/2003 10:47AM EST
Your Comment
page1.asp
<form method = post action=page2.asp>
<input type=text name = txt1><br>
<input type=text name = txt2><br>
<input type=text name = txt3><br>
<input type=text name = txt4><br>
<input type=text name = txt5><br>
<input type=submit>
</form>
page2.asp
<%
'Database connection
set rs = cn.execute("Insert into table (field1,field2,field3,fiel
id = rs(0)
%>
<form method = post action=page3.asp>
<input type=text name = txt6><br>
<input type=text name = txt7><br>
<input type=text name = txt8><br>
<input type=text name = txt9><br>
<input type=text name = txt10><br>
<input type=text name = txt11><br>
<input type=hidden name = id value =<%=id%>><BR>
<input type=submit>
</form>
page3.asp
<%
'Database connection
cn.execute("Update table set column6 = '" & request("txt6") & "', column7 = '" & request("txt7") & "', column8 = '" & request("txt8") & "', column9 = '" & request("txt9") & "', column10 = '" & request("txt10") & "', column11 = '" & request("txt11") & "', )
%>
Comment from mikosha
Date: 12/12/2003 10:48AM EST
Comment
form1 and form2 are on the same page? and ,by the way , it's regular Asp or Asp.NET?
Comment from mharcais
Date: 12/12/2003 10:55AM EST
Author Comment
This is ordinary ASP, not ASP.NET
Form 1 submits itself to another ASP page which enters the data (add_to_database1.asp) and then forwards me to Form 2 which submits itself to add_to_database2.asp etc etc
Add_to_database1.asp code currently is:
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsAddComments 'Holds the recordset for the new record to be added to the database
Dim strSQL 'Holds the SQL query for the database
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("mydatabase
'Create an ADO recordset object
Set rsAddComments = Server.CreateObject("ADODB
'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT * FROM mytable;"
'Set the cursor type we are using so we can navigate through the recordset
rsAddComments.CursorType = 2
'Set the lock type so that the record is locked by ADO when it is updated
rsAddComments.LockType = 3
'Open the Timepoint table using the SQL query held in the strSQL varaiable
rsAddComments.Open strSQL, adoCon
'Tell the recordset we are adding a new record to it
rsAddComments.AddNew
'Add a new record to the recordset
rsAddComments.Fields("2") = Request.Form("2")
rsAddComments.Fields("3") = Request.Form("3")
rsAddComments.Movelast
'Write the updated recordset to the database
rsAddComments.Update
Dim id
id = rsaddcomments.Fields("ID")
'Reset server objects
Comment from thunderchicken
Date: 12/12/2003 10:47AM EST
Your Comment
page1.asp
<form method = post action=page2.asp>
<input type=text name = txt1><br>
<input type=text name = txt2><br>
<input type=text name = txt3><br>
<input type=text name = txt4><br>
<input type=text name = txt5><br>
<input type=submit>
</form>
page2.asp
<%
'Database connection
set rs = cn.execute("Insert into table (field1,field2,field3,fiel
id = rs(0)
%>
<form method = post action=page3.asp>
<input type=text name = txt6><br>
<input type=text name = txt7><br>
<input type=text name = txt8><br>
<input type=text name = txt9><br>
<input type=text name = txt10><br>
<input type=text name = txt11><br>
<input type=hidden name = id value =<%=id%>><BR>
<input type=submit>
</form>
page3.asp
<%
'Database connection
cn.execute("Update table set column6 = '" & request("txt6") & "', column7 = '" & request("txt7") & "', column8 = '" & request("txt8") & "', column9 = '" & request("txt9") & "', column10 = '" & request("txt10") & "', column11 = '" & request("txt11") & "', )
%>
Comment from mikosha
Date: 12/12/2003 10:48AM EST
Comment
form1 and form2 are on the same page? and ,by the way , it's regular Asp or Asp.NET?
Comment from mharcais
Date: 12/12/2003 10:55AM EST
Author Comment
This is ordinary ASP, not ASP.NET
Form 1 submits itself to another ASP page which enters the data (add_to_database1.asp) and then forwards me to Form 2 which submits itself to add_to_database2.asp etc etc
Add_to_database1.asp code currently is:
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsAddComments 'Holds the recordset for the new record to be added to the database
Dim strSQL 'Holds the SQL query for the database
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("mydatabase
'Create an ADO recordset object
Set rsAddComments = Server.CreateObject("ADODB
'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT * FROM mytable;"
'Set the cursor type we are using so we can navigate through the recordset
rsAddComments.CursorType = 2
'Set the lock type so that the record is locked by ADO when it is updated
rsAddComments.LockType = 3
'Open the Timepoint table using the SQL query held in the strSQL varaiable
rsAddComments.Open strSQL, adoCon
'Tell the recordset we are adding a new record to it
rsAddComments.AddNew
'Add a new record to the recordset
rsAddComments.Fields("2") = Request.Form("2")
rsAddComments.Fields("3") = Request.Form("3")
rsAddComments.Movelast
'Write the updated recordset to the database
rsAddComments.Update
Dim id
id = rsaddcomments.Fields("ID")
'Reset server objects
You can use another way too.
Using this procedure ,you can collect all your data from all the pages and update db only once:
sub get_submited_fields()
for each Item in Request.Form
Response.Write "<input type='hidden' name='" & Item & "' value='" & Request.Form(Item) & "'>"
next
end sub
Let's say :
page1.asp
<form method = post action=page2.asp>
<input type=text name = txt1><br>
<input type=text name = txt2><br>
<input type=text name = txt3><br>
<input type=text name = txt4><br>
<input type=text name = txt5><br>
<input type=submit>
</form>
page2.asp
<form method = post action=page3.asp>
<input type=text name = txt6><br>
<input type=text name = txt7><br>
<input type=text name = txt8><br>
<input type=text name = txt9><br>
<input type=text name = txt10><br>
<input type=text name = txt11><br>
<%
call get_submited_fields
%>
<input type=submit>
</form>
page3.asp
<%
'UPDATE THE SQL FOR UPDATE :ADD column1 = '" & request("txt1") & "' for all Fields from page1
'Database connection
cn.execute("Update table set column6 = '" & request("txt6") & "', column7 = '" & request("txt7") & "', column8 = '" & request("txt8") & "', column9 = '" & request("txt9") & "', column10 = '" & request("txt10") & "', column11 = '" & request("txt11") & "', )
%>
Using this procedure ,you can collect all your data from all the pages and update db only once:
sub get_submited_fields()
for each Item in Request.Form
Response.Write "<input type='hidden' name='" & Item & "' value='" & Request.Form(Item) & "'>"
next
end sub
Let's say :
page1.asp
<form method = post action=page2.asp>
<input type=text name = txt1><br>
<input type=text name = txt2><br>
<input type=text name = txt3><br>
<input type=text name = txt4><br>
<input type=text name = txt5><br>
<input type=submit>
</form>
page2.asp
<form method = post action=page3.asp>
<input type=text name = txt6><br>
<input type=text name = txt7><br>
<input type=text name = txt8><br>
<input type=text name = txt9><br>
<input type=text name = txt10><br>
<input type=text name = txt11><br>
<%
call get_submited_fields
%>
<input type=submit>
</form>
page3.asp
<%
'UPDATE THE SQL FOR UPDATE :ADD column1 = '" & request("txt1") & "' for all Fields from page1
'Database connection
cn.execute("Update table set column6 = '" & request("txt6") & "', column7 = '" & request("txt7") & "', column8 = '" & request("txt8") & "', column9 = '" & request("txt9") & "', column10 = '" & request("txt10") & "', column11 = '" & request("txt11") & "', )
%>
ASKER
This is a massive form, broken down into 15 pages. There would be no way I could run the risk of somebody getting to form 14 of 15 and it crashes and they have to start all over again.
The solution I want is that each form adds the appropriate data to the appropriate fields, but updates the record it started in form 1 each time...
The solution I want is that each form adds the appropriate data to the appropriate fields, but updates the record it started in form 1 each time...
ASKER
Here is my existing code:
Are there any obvious modifications? PLease!
Form 1:
<form name="form" method="post" action="add_to_database_ro
<select name="1">
<option selected>Select Type of occurrence...
<option value="Incident">Incident
<option value="First Aid">First Aid
<option value="Medical Aid">Medical Aid
<option value="Environmental Spill">Environmental Spill
<option value="Traffic Accident">Traffic Accident
<option value="Fire">Fire
<option value="Dangerous Occurence ">Dangerous Occurence
</select>
<input type="image" src="submit.gif" name="submit" value="SUBMIT">
</form>
add_to_database_roi1.asp:
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsAddComments 'Holds the recordset for the new record to be added to the database
Dim strSQL 'Holds the SQL query for the database
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("health.mdb
'Create an ADO recordset object
Set rsAddComments = Server.CreateObject("ADODB
'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT * FROM types;"
'Set the cursor type we are using so we can navigate through the recordset
rsAddComments.CursorType = 2
'Set the lock type so that the record is locked by ADO when it is updated
rsAddComments.LockType = 3
'Open the Timepoint table using the SQL query held in the strSQL varaiable
rsAddComments.Open strSQL, adoCon
'Tell the recordset we are adding a new record to it
rsAddComments.AddNew
'Add a new record to the recordset
rsAddComments.Fields("1") = Request.Form("1")
'Write the updated recordset to the database
rsAddComments.Update
'Reset server objects
rsAddComments.Close
Set rsAddComments = Nothing
Set adoCon = Nothing
Response.Redirect "h&S_form_roi2.asp"
%>
Form 2:
<form name="form" method="post" action="add_to_database_ro
<select name="2">
<option selected>Select Choice...
<option value="Incident">A
<option value="First Aid">B
<option value="Medical Aid">C
<option value="Environmental Spill">D
<option value="Traffic Accident">E
<option value="Fire">F
<option value="Dangerous Occurence ">G
</select>
<input type="image" src="submit.gif" name="submit" value="SUBMIT">
</form>
add_to_database_roi2.asp:
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsAddComments 'Holds the recordset for the new record to be added to the database
Dim strSQL 'Holds the SQL query for the database
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("health.mdb
'Create an ADO recordset object
Set rsAddComments = Server.CreateObject("ADODB
'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT * FROM types;"
'Set the cursor type we are using so we can navigate through the recordset
rsAddComments.CursorType = 2
'Set the lock type so that the record is locked by ADO when it is updated
rsAddComments.LockType = 3
'Open the Timepoint table using the SQL query held in the strSQL varaiable
rsAddComments.Open strSQL, adoCon
'Tell the recordset we are adding a new record to it
rsAddComments.AddNew
'Add a new record to the recordset
rsAddComments.Fields("2") = Request.Form("2")
'Write the updated recordset to the database
rsAddComments.Update
'Reset server objects
rsAddComments.Close
Set rsAddComments = Nothing
Set adoCon = Nothing
Response.Redirect "h&S_form_roi3.asp"
%>
Etc etc for approx 15 forms
Are there any obvious modifications? PLease!
Form 1:
<form name="form" method="post" action="add_to_database_ro
<select name="1">
<option selected>Select Type of occurrence...
<option value="Incident">Incident
<option value="First Aid">First Aid
<option value="Medical Aid">Medical Aid
<option value="Environmental Spill">Environmental Spill
<option value="Traffic Accident">Traffic Accident
<option value="Fire">Fire
<option value="Dangerous Occurence ">Dangerous Occurence
</select>
<input type="image" src="submit.gif" name="submit" value="SUBMIT">
</form>
add_to_database_roi1.asp:
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsAddComments 'Holds the recordset for the new record to be added to the database
Dim strSQL 'Holds the SQL query for the database
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("health.mdb
'Create an ADO recordset object
Set rsAddComments = Server.CreateObject("ADODB
'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT * FROM types;"
'Set the cursor type we are using so we can navigate through the recordset
rsAddComments.CursorType = 2
'Set the lock type so that the record is locked by ADO when it is updated
rsAddComments.LockType = 3
'Open the Timepoint table using the SQL query held in the strSQL varaiable
rsAddComments.Open strSQL, adoCon
'Tell the recordset we are adding a new record to it
rsAddComments.AddNew
'Add a new record to the recordset
rsAddComments.Fields("1") = Request.Form("1")
'Write the updated recordset to the database
rsAddComments.Update
'Reset server objects
rsAddComments.Close
Set rsAddComments = Nothing
Set adoCon = Nothing
Response.Redirect "h&S_form_roi2.asp"
%>
Form 2:
<form name="form" method="post" action="add_to_database_ro
<select name="2">
<option selected>Select Choice...
<option value="Incident">A
<option value="First Aid">B
<option value="Medical Aid">C
<option value="Environmental Spill">D
<option value="Traffic Accident">E
<option value="Fire">F
<option value="Dangerous Occurence ">G
</select>
<input type="image" src="submit.gif" name="submit" value="SUBMIT">
</form>
add_to_database_roi2.asp:
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsAddComments 'Holds the recordset for the new record to be added to the database
Dim strSQL 'Holds the SQL query for the database
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("health.mdb
'Create an ADO recordset object
Set rsAddComments = Server.CreateObject("ADODB
'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT * FROM types;"
'Set the cursor type we are using so we can navigate through the recordset
rsAddComments.CursorType = 2
'Set the lock type so that the record is locked by ADO when it is updated
rsAddComments.LockType = 3
'Open the Timepoint table using the SQL query held in the strSQL varaiable
rsAddComments.Open strSQL, adoCon
'Tell the recordset we are adding a new record to it
rsAddComments.AddNew
'Add a new record to the recordset
rsAddComments.Fields("2") = Request.Form("2")
'Write the updated recordset to the database
rsAddComments.Update
'Reset server objects
rsAddComments.Close
Set rsAddComments = Nothing
Set adoCon = Nothing
Response.Redirect "h&S_form_roi3.asp"
%>
Etc etc for approx 15 forms
IF you have 15 pages or so, you should create a record and create a unique guid. You can pass this in the querystring or in a cookie. After each page is submitted, you should update that record.
That way, if a user disconnects, they can start all over.
That way, if a user disconnects, they can start all over.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<form method = post action=page2.asp>
<input type=text name = txt1><br>
<input type=text name = txt2><br>
<input type=text name = txt3><br>
<input type=text name = txt4><br>
<input type=text name = txt5><br>
<input type=submit>
</form>
page2.asp
<%
'Database connection
set rs = cn.execute("Insert into table (field1,field2,field3,fiel
id = rs(0)
%>
<form method = post action=page3.asp>
<input type=text name = txt6><br>
<input type=text name = txt7><br>
<input type=text name = txt8><br>
<input type=text name = txt9><br>
<input type=text name = txt10><br>
<input type=text name = txt11><br>
<input type=hidden name = id value =<%=id%>><BR>
<input type=submit>
</form>
page3.asp
<%
'Database connection
cn.execute("Update table set column6 = '" & request("txt6") & "', column7 = '" & request("txt7") & "', column8 = '" & request("txt8") & "', column9 = '" & request("txt9") & "', column10 = '" & request("txt10") & "', column11 = '" & request("txt11") & "', )
%>