Hidden field

Hi mharcais,

Just add:

<INPUT type=hidden id=AutoNumber Name=AutoNumber Value=<%=Request.Form("AutoNumber")%>>

In each form, as it is passed in the post data it will be assigned to a hidden field and passed again.

Tim Cottee MCSD, MCDBA, CPIM
Brainbench MVP for Visual Basic
http://www.brainbench.com

1st solution :
using Request.form("FieldName")

2nd :
passing the value through querystring ... page.asp?Value=window.document.forms[0].FieldName.value

3rd :
using sessions (your session variable will then be available in all pages)
Session("intValue") = window.document.forms[0].FieldName.value

Do you mean how to determine, and then pass on, the autonumber created as a result of a new record entered into the db on the first page?

If so, use SELECT @@IDENTITY...

From SQL books online (Jet 4.0 now supports @@IDENTITY too):

Remarks
After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY contains the last identity value generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated. If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers. The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.

...

Examples
This example inserts a row into a table with an identity column and uses @@IDENTITY to display the identity value used in the new row.

INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @@IDENTITY AS 'Identity'

And then, throw the value into either a hidden form field, querystring, cookie, or session item...

Personally I would use cookies...

Response.Cookies("value") = "Foo"

if it's a series of forms that are POSTed, I'd suggest using a hidden form field, since there are still people out there that disable cookies in their browsers...

Sorry, I'm a newbie to both ASP and Experts Exchange...

Form 1 inputs data into the appropriate fields in the table. The table generates an autonumber for that record.

How do I bring back the autonumber that was generated to the second part of the form?

And what would the syntax be so that I could tell each subsequent form to input XX data into the table, only where the autonumber was = whatever that value was...

Thank you.

Try adding  "SELECT @@IDENTITY as RecID"  to your insert statement. Normally, you just excute the insert something like this:
<%
dim sql

sql = "INSERT INTO jobs (job_desc,min_lvl,max_lvl) " &_
   "VALUES ('Accountant',12,125)"

oConn.execute(sql)
%>

But now, you'll use a recordset and in the returned recordset will be the new autonumber...
<%
dim sql, rs, newAutoNum

Set rs = Server.CreateObject("ADODB.RecordSet")

sql = "INSERT INTO jobs (job_desc,min_lvl,max_lvl) " &_
   "VALUES ('Accountant',12,125) " &_
   "SELECT @@IDENTITY as RecID"

set rs = oConn.execute(sql)

newAutoNum = rs("RecID")

rs.close
set rs = nothing
%>

Now, the "newAutoNum" variable has the autonumber value, pass the value along to the the_nextpage.asp (or whatever)...

<form name="form2" action="the_nextpage.asp">
<input type="hidden" name="newAutoNum" value="<%= newAutoNum %>">
</form>

I appreciate the time you have taken on your answer, but it is too advanced for me.

Do I have to reduce the points on the question in order to get simple answers?

There are consequences by doing it the other way.

Do you go to a mechanic to fix your car and ask for it to be fixed with scotch tape, or do you want it done the right way?

Sorry, I don't think I can make it much simpler than that. Maybe one of you other experts? :-)

You can use the rs.movelast way, but you're opening a whole bunch of worms going down that route.

Try this method i have done the similar request

1. Need to use a dumynumber store this dumynumber in a session variable and get this dumynumber in all the pages. Store this dumyno in a dumyno table so that when next time if the form is clicked the number will increment.

Store this dumynumber in a table first. For the subsquent form update use the dumynumber to get the matching record and update the correct record.


<form name=f method=post>

'Write this code after the form definition
if Session("dumyno") = "" then
                         ' When the first time form is clicked
                      ' Create dumyno table with DumyID(autonumber Primarykey to this) and Dumyno. Store the dumyno in this table.
             query = "Select * from dumyno ORDER BY dumyid DESC;"

             SET Rs = Server.CreateObject("ADODB.Recordset")
             Rs.Open Query, DbObjmembers, adOpenKeyset, adLockOptimistic
             IF rs.eof then
                                                 ' very first time when the form clicked, there won't be any record. I start from 1000 you may give from 1
                  dumyno = 1000
                  rs.Addnew
                  rs("dumyno") = dumyno
                  rs.Update
                                  ELSE
                                       dumyno = rs("dumyno") + 1
                                       rs.Addnew
                                       rs("dumyno") = dumyno
                                       rs.Update
                                  end if
                        session("dumyno") = dumyno
                         response.write "<input type=hidden name=dumyno value="&dumyno&">"
else
   session("dumyno") = dumyno
    response.write "<input type=hidden name=dumyno value="&Session("dumyno")&">"

end if
</form>
You can store this dumyno in a hidden field in subsquent forms and retrieve the record using this dumyno when you submit the subsquest forms and it will write to the matching record.

QUERY = Select * from table Where Dumyno="&request.form("dumyno")

Make this session dumyno to nothing Which is important once you update all the form elements.
Session("dumyno") = ""
This is the correct method you can follow if you understand this properly.  Need help?

I think I can work with this last posting from aneethat, though it may take a few more questions to clear up a couple  of things for me.

First of all: I need to create a new table in my access database, with one field 'DumyID' and this field will be an autonumber.

I then need to go to my asp page that is processing the info submitted and add the following code:

if Session("dumyno") = "" then
                                    query = "Select * from dumyno ORDER BY dumyid DESC;"

           SET Rs = Server.CreateObject("ADODB.Recordset")
           Rs.Open Query, DbObjmembers, adOpenKeyset, adLockOptimistic
           IF rs.eof then
                                                dumyno = 1000
               rs.Addnew
               rs("dumyno") = dumyno
               rs.Update
                                  ELSE
                                      dumyno = rs("dumyno") + 1
                                      rs.Addnew
                                      rs("dumyno") = dumyno
                                      rs.Update
                                  end if
                        session("dumyno") = dumyno
                         response.write "<input type=hidden name=dumyno value="&dumyno&">"
else
   session("dumyno") = dumyno
    response.write "<input type=hidden name=dumyno value="&Session("dumyno")&">"

end if

That works great if only one person visits your site at a time.

@mharcais -- is this the goal of your original question:

How to determine, and then pass on (to subsequent forms or pages), the autonumber created as a result of a new record entered into the db via the first form?

If so, do you have a column in your table that is 'autonumber' datatype and is the identity column?

Yes, there is a column in the table with autonumber which is the ID (It's called Event_ID)

The problem is that I submit form 1, and it enters the data no problem. Forwards me to form 2. That submits no problem too...but it creates a NEW RECORD!

So I get record 1 with a,b and C. And where form 2 should continue on with d, e, f etc in that same record, instead I have record 1 with a, b, c and record 2 with d, e, and f.

Dop you see my predicament?

I need to tell the second form to insert d, e and f into the database where Event_ID= ***

And getting *** back to the second form is the key thing for me here.

Right, so what you need to do is get the autonumber value WHILE you are adding the record (the first time)...

There are 2 ways to do this (besides the dumyno table):

1. If you are using an INSERT SQL statement, you can just add on one more line to this statement and get back the autonumber -- this is described in my post above -- the SELECT @@IDENTITY idea.

2. The other way would be an ADO method:

For example,

<%

dim query, rs, Event_ID_value  '<-- variable that will contain the autonumber

query = "Select * from yourtable "

          SET rs = Server.CreateObject("ADODB.Recordset")
          rs.Open query, DbObjmembers, adOpenKeyset, adLockOptimistic

          rs.Addnew
          rs("mycolumnA") = "whateverA"
          rs("mycolumnB") = "whateverB"
          rs("mycolumnC") = "whateverC"
'and so on...
          rs.Update

'Now, move the cursor to the last record, which is the record you just added...
          rs.Movelast
          Event_ID_value = rs("Event_ID")

%>


Now, however you are forwarded to Form 2, send along the autonumber using the Event_ID_value variable:

The easiest way is to throw the value into a session variable, then retrieve that value when doing the second insert...

So this:
'Now, move the cursor to the last record, which is the record you just added...
          rs.Movelast
          Event_ID_value = rs("Event_ID")

changes to this:

'Now, move the cursor to the last record, which is the record you just added...
          rs.Movelast
          session("Event_ID") = rs("Event_ID")
'         ^^^^^^^^^^^^^

So, when you are  doing your second insert, your SQL would look like:

query = "select * from myTable WHERE Event_ID = " & session("Event_ID")

OR

query = "insert into myTable (colD, colE, colF) VALUES(valD, valE, valF) WHERE Event_ID = " & session("Event_ID")

Make sense?

The select @@identity idea looks easier...I'd like to try that.

Am I right in thinking that the correct code would look like:

'Add a new record to the recordset
rsAddComments.Fields("2") = Request.Form("2")
rsAddComments.Fields("3") = Request.Form("3")
SELECT @@IDENTITY AS 'Event_ID'

And what would I need to have in order to insert that Event ID into a hidden field you previously mentioned?

No, that wouldn't work. If you're inserting new records that way (via ADO methods), you'd have to use the 2nd suggestion. Like this:

dim Event_ID_value

'Add a new record to the recordset
rsAddComments.Fields("2") = Request.Form("2")
rsAddComments.Fields("3") = Request.Form("3")

rsAddComments.Movelast   '<-- do this after you're done inserting your new record
Event_ID_value = rsAddComments.Fields("Event_ID")


I am assuming that after your new record insertion, the user end up on the page that has the second form to be submitted. This second form just needs a hidden field added...

<form name="whatever" action="whatever">
   <your form fields...>
   <your submit button...>

<input type="hidden" name="Event_ID" value="<%= Event_ID_value %>">

</form>


Now, on the page that receives these form values, you're probably doing a select statement to open up another recordset for updating. In this select statement you'll add the Event_ID to the where clause. So, a simplified example would be:

query = "SELECT * FROM myTable WHERE Event_ID = " & request.form("Event_ID")

So when you execute this select, you're only getting back the record that has that Event_ID...

How many pages do you have for this process and where are the forms located in these pages?

For example:

Page 1 has form 1 and submits to page 2, which has form 2, which submits to... and so on...

I clear the doubt that you have.

Yes you have to create a dumyno table with two fields one is autono field another one is for dumyno to store. Because according to the latest dumyno we increment the value in the main form when it is clicked.

It's not neccessary that only one person use the form at one time. My Project is an Internet website we concurrently add record and we tested our system.

Because whenever the main form is clicked from different computer there won't be any session of dumycode so it will increment the value from the latest dumyno in the table and give a new dumyno.

It works fine for me. my system has main form that has popup form to update in different table and then the user will submit the main form.

if you call the subsequent forms after submitting and updating the main form value, then from the Update.asp page when you call the subsequent form you can send the autonumber in a querystring and store this autonumber in a hidden field in subsequent forms.

Update.asp page
after you update with the recordset then
autoid = rs("AUTOID")
if DbObjmembers.Errors.Count > 0 then
else
      Response.write ("<script language=""javascript"">")
      Response.write ("{alert(""Record Updated"")}")
      Response.write "javascript:{location.href='Form2.asp?RecordID="&("autoID")&"';}"
     response.write ("</script>")      
end if

aneethat.

Squarehead:

I put the following code in:

strSQL = "SELECT * FROM myTable WHERE Event_ID = " & request.form("Event_ID")

But I get the following error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Event_ID ='.

I also noticed that on the second form - the one that has the hidden field with the Event_ID, the code for the page has this code:

<input type="hidden" name="Event_ID" value="">

Should the code not have actually brought in an actual value??

Could that be the problem.

I am increasing this to 300 points.

I also noticed that on the second form - the one that has the hidden field with the Event_ID, the code for the page has this code:

<input type="hidden" name="Event_ID" value="">

Should the code not have actually brought in an actual value??

Could that be the problem.

I am increasing this to 300 points.

>> <input type="hidden" name="Event_ID" value="">

>> Should the code not have actually brought in an actual value??

>> Could that be the problem.

Yes, it sounds like you haven't retrieved the autonumber value... Is it possible to post the code from the pages that contain form 1 and form 2?

Form 1 is just a straight form, the processing is done by a file it submits itself to, called add_to_database1.asp or add_to_database2.asp etc etc

Code on add_to_database 1 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.Connection")


'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.Recordset")


'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")
rsAddComments.Fields("3") = Request.Form("3")



'Write the updated recordset to the database
rsAddComments.Update

'Reset server objects
rsAddComments.Close
Set rsAddComments = Nothing
Set adoCon = Nothing

You still need to retrieve the Event_ID value (your autonumber column)


'Write the updated recordset to the database
rsAddComments.Update

rsAddComments.Movelast
Event_ID_value = rsAddComments.Fields("Event_ID")



Now, "Event_ID_value" holds the autonumber, so pass it to the next form...

What happens after add_to_database1.asp? How is the user taken to the page with Form #2 on it?

Squarehead,

Can you suggest what I need to modify within this (slimmed down) version of the code:

Form 1:

<form name="form" method="post" action="add_to_database_roi1.asp">
<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.Connection")


'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.Recordset")

'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_roi2.asp">
<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.Connection")


'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.Recordset")

'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

In add_to_database_roi1.asp:

'(with the rest of the dim statements...)
dim Event_ID_value

...

'Write the updated recordset to the database
rsAddComments.Update

rsAddComments.Movelast       '<-- NEW LINE
Event_ID_value = rsAddComments.Fields("Event_ID")  '<-- NEW LINE
Session("Event_ID") = Event_ID_value   '<-- NEW LINE

'Reset server objects
rsAddComments.Close


Now, on any other page (like "add_to_database_roi2.asp") that updates the newly-added record, retrieve the Event_ID from the session object you've created and use it as part of your select statement:

strSQL = "SELECT * FROM types WHERE Event_ID = "& session("Event_ID") &";"

The session item will be available to the user (as long as he has cookies enabled -- most do) until he ends his session by closing his browsers (or he hits a page of yours that abandons his session), or by timing out after a set amount of time has passed where he is inactive (not requesting any pages, etc).


If you don't like using Session objects (and plenty of people don't, but I use them in moderation), another option would be to use a cookie...
If you don't like either session objects or cookies (and you may not if any of your users have cookies disabled), another option would be to pass the Event_ID value from page to page using a hidden form field...

And lastly, I notice that you are using response.redirect to move to the next page(s). You could easily pass the the Event_ID along in the querystring, like this:

(this would occur on "add_to_database_roi1.asp")
Response.Redirect "h&S_form_roi3.asp?event_id=" & Event_ID_value

Each page after that could retrieve and use the value like this:

strSQL = "SELECT * FROM types WHERE Event_ID="& request.querystring("event_id") &";"

And then redirect again to the next page:
Response.Redirect "h&S_form_roi4.asp?event_id=" & request.querystring("event_id")

And so on...

Got it?

minor typo: "3" should be "2"

(this would occur on "add_to_database_roi1.asp")
Response.Redirect "h&S_form_roi2.asp?event_id=" & Event_ID_value
------------------------------------------^

No. This does not work.

OK, what doesn't work?

You know mharcais, several people here (including me) have put in a considerable effort in attempting to help. Posting the same question 3 different times is not appreciated (by me, at least):

http://oldlook.experts-exchange.com/questions/20820756/Hidden-field.html (this post)
http://oldlook.experts-exchange.com/questions/20824113/Hidden-field-with-autonumber-so-that-each-form-updates-the-same-record.html
http://oldlook.experts-exchange.com/questions/20828077/Update-same-record-from-different-forms.html

I appreciate the time you have put in to attempting to help with this question. However, on at least one posting I have clearly stated that I am a beginner to ASP and EE, and only wanted people to reply if they were prepared to be patient and work with my existing code.

So far, I have found most of the replies to my posting(s) confusing, and none of them have worked for me. I have paid good money to use this service, and am prepared to close whatever questions I post, but only if the website does what it says: namely to provide people like me with access to answers and help that our work situation does not.

There is no point in someone posting replies that do not work, or are not clearly explained. That way, believe me, I end up having to post new qustions in order to attract experts and get this problem sorted.


Or maybe I am expecting too much from the site.

>> "replies that do not work, or are not clearly explained"

Rather than saying "No. This does not work." why don't you specify which lines in my examples/explanations you do not understand -- or -- which lines do not work?

For example, if you are receiving an error message, post the error message here. If the error message includes a line number, post the line of code that the number corresponds to.

If you post "Can you suggest what I need to modify within this (slimmed down) version of the code:", and then I do that, you need to tell me what is not clear to you.

I actually already have posted up an error message I received, on one of the postings, and got no reply. That is one of the reasons I have opened up new questions. I copied the code suggested, pasted it in as directed, got an error, replied with the browser error, and someone else replied to it suggesting I go down a completely different direction. At least that's how it seems...

Not helpful, just frustrating.

Anyway, would you be prepared to look at the code stage by stage?

Eg. Get the code sorted out for Form 1.

Then get the add_to_database1.asp processing page sorted.

Then get form 2 sorted

Then get  add_to_database2.asp  sorted

Once I get these four pages sorted (and the real problems are the add_to_database ones) then I can go off and modify the code on the rest of the 15 pages without anyone's assistance.

If you are willing to help me do that, please have a look at this code for Form 1. Is this correct so far?

<form name="form" method="post" action="add_to_database_roi1.asp">
<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>


I am presuming this is fine, because it's just a basic form, but you may well tell me I would need something else.

Stage by stage, and I'll be out of your hair before you know it!

Sure, let's do it that way. And apologies for my tone above.

Form 1 looks fine to me except for this line:

<option value="Dangerous Occurence     ">

Unless you need that gap following the value, I'd remove it. On to add_to_database_roi1.asp

Question: Are the forms located on the same pages as the 'add to database' code? I am assuming not, but clarify that for me.

Thank you very much.

No, the add_to_database pages are all seperate. Each form has its own add_to_database page. E.g. form1.asp submits itself to add_to_database1.asp, which adds the data to the database and then forwards the user to form2.asp (which submits the data to the database and forwards the user to form3.asp etc)

So, at this stage, the form code is fine. Great. It has collected the data and it submits itself to add_to_database_roi1.asp. I need this asp page to enter the fields we have just collected from the form into the access database, and then forward on to form2.asp - keeping a note of what the autonumber generated by the Event_ID field in the database was.

So here's what I have for 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
Dim Event_ID_value

'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")


'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.Recordset")

'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


'I know in the example above there is just one
'Add a new record to the recordset
rsAddComments.Fields("1") = Request.Form("1")

'Write the updated recordset to the database
rsAddComments.Update

rsAddComments.Movelast       '<-- NEW LINE
Event_ID_value = rsAddComments.Fields("Event_ID")  '<-- NEW LINE
Session("Event_ID") = Event_ID_value   '<-- NEW LINE

'Reset server objects
rsAddComments.Close
Set rsAddComments = Nothing
Set adoCon = Nothing

'Redirect to the entry page
Response.Redirect "h&S_form_roi2.asp"
%>


Currently this code brings up the following browser error:

Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
/prototype_intranet/stores/add_to_database_roi1.asp, line 59


Line 59 on the code is:
Event_ID_value = rsAddComments.Fields("Event_ID")  '<-- NEW LINE


Hope this helps...

Sounds like my suggestion is a little more complicated that I thought. OK, this is a 3-step process.

1. Near the top of your code, add the include line for the ADOVBS.inc file, like this:

<%@ Language=VBScript %>
<HTML>
<BODY>

<!--#include file=adovbs.inc -->

(Make sure that this file: ADOVBS.inc, exists in the same folder as "add_to_database_roi1.asp" If it doesn't, you can probably find it in the website root folder -- or do a search...)


2. Comment out this section:

'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

and add this section:

rsAddComments.CursorLocation = adUseClient
rsAddComments.Open strSQL, objConn, adOpenStatic, adLockOptimistic


3. Comment out this section:

rsAddComments.Movelast       '<-- NEW LINE
Event_ID_value = rsAddComments.Fields("Event_ID")  '<-- NEW LINE
Session("Event_ID") = Event_ID_value   '<-- NEW LINE

and add this section:

bookmark = rsAddComments.absolutePosition
rsAddComments.Requery
rsAddComments.absolutePosition = bookmark

Event_ID_value = rsAddComments.Fields("Event_ID")
Session("Event_ID") = Event_ID_value


save and run the page, let me know what error you get, if any...

Here is a page from MS support that explains what you're trying to do here:
http://support.microsoft.com/default.aspx?scid=kb;en-us;221931&Product=acc

One thing to try before making those changes:

change your SQL statement from this:

strSQL = "SELECT * FROM types;"

to this:

strSQL = "SELECT * FROM types ORDER BY Event_ID ASC;"

and see if that makes a difference. If not, go with my suggestions above.

I did the second suggestion (strSQL = "SELECT * FROM types ORDER BY Event_ID ASC;") and it seems to have worked...it is now forwarding to Form2.asp.

So progress is reported. Nice one...

We have now added the content of Form 1 to the database and arrived at form 2.

Form 2 is just a straight form, like form 1, but do I need a hidden field too?

First thing:

On form2 page, check to make sure the Event_ID_value is in the session item you created on add_to_database_roi1.asp...

This where you created it:

Session("Event_ID") = Event_ID_value   '<-- NEW LINE

So now, on form2 page, check it like this (just put this somewhere near the top of your page code):

<%
Response.write "Session Event_ID: " & Session("Event_ID") & "<br>"
%>

Then you should see this on your form2 page at the top:

Session Event_ID : 26

or whatever the Event_ID value is... Compare this to your database right away and make sure they match -- that is, make sure the most recent record added to your DB has the same Event_ID as displayed on your form2 page...

I am assuming here that the form2 page is an ASP page, correct?

Yes, they match. The ID in the database is the same as the ID in the code for form2.asp

OK, then you now have that ID in the session object. Which means, on all subsequent pages, that ID will be available.

So, on Form2, you really don't need the ID. But on the "add_to_database1.asp" page, where I am assuming you are querying your DB for this record, your query should end up looking like this:

SELECT * FROM types WHERE Event_ID = 26;

So, to produce this, your code will look like this:

strSQL = "SELECT * FROM types WHERE Event_ID = " & Session("Event_ID") & ";"

If you want to check this SQL, do a response.write then response.end right after the above line, like this:

strSQL = "SELECT * FROM types WHERE Event_ID = " & Session("Event_ID") & ";"
Response.Write strSQL
Response.End

After you've checked it, be sure to comment out or remove those 2 lines (response.write & response.end)

Make sense so far?

I have added strSQL = "SELECT * FROM types WHERE Event_ID = " & Session("Event_ID") & ";"  to add_to_database2.asp, but when I check the database, it is still adding the data to a new record...

Lets see the code you're using on this page, particularly the UPDATE statement...

squarehead!

I think I've got it sorted!!

I commented out an "rsAddComments.AddNew" statement that was in the code, and it now seems to be adding the forms to the same record.

This is brilliant, thank you very, very much for your patience and help with this over the last while.

I am accepting your answers on all my postings.

Hope this is ample reward for your important contribution to my project here in work.

Much appreciated.

Mharcais

Ample indeed, thanks.