Solved

Hidden field

Posted on 2003-12-09
49
840 Views
Last Modified: 2007-12-19
Hi.

How do you carry through a series of forms the autonumber that was entered on the first form, so that the subsequent forms are adding the data to the same record rather than to a new one each time?

Thanks.
0
Comment
Question by:mharcais
  • 23
  • 18
  • 4
  • +3
49 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 9905303
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
0
 
LVL 5

Expert Comment

by:DesertWarrior
ID: 9905313
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
0
 
LVL 18

Expert Comment

by:SquareHead
ID: 9905398
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...
0
 
LVL 11

Expert Comment

by:thunderchicken
ID: 9905448
Personally I would use cookies...

Response.Cookies("value") = "Foo"
0
 
LVL 18

Expert Comment

by:SquareHead
ID: 9905521
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...
0
 

Author Comment

by:mharcais
ID: 9905534
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.
0
 
LVL 18

Expert Comment

by:SquareHead
ID: 9905679
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>


0
 

Author Comment

by:mharcais
ID: 9905722
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?
0
 
LVL 11

Expert Comment

by:thunderchicken
ID: 9905734
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?
0
 
LVL 18

Expert Comment

by:SquareHead
ID: 9905887
Sorry, I don't think I can make it much simpler than that. Maybe one of you other experts? :-)
0
 
LVL 11

Expert Comment

by:thunderchicken
ID: 9905901
You can use the rs.movelast way, but you're opening a whole bunch of worms going down that route.
0
 

Expert Comment

by:aneethat
ID: 9910217
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?


0
 

Author Comment

by:mharcais
ID: 9920308
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


0
 
LVL 11

Expert Comment

by:thunderchicken
ID: 9920369
That works great if only one person visits your site at a time.
0
 
LVL 18

Expert Comment

by:SquareHead
ID: 9920535
@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?


0
 

Author Comment

by:mharcais
ID: 9920566
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.

0
 
LVL 18

Expert Comment

by:SquareHead
ID: 9920938
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?
0
 

Author Comment

by:mharcais
ID: 9921177
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?

0
 
LVL 18

Expert Comment

by:SquareHead
ID: 9922306
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...

0
 
LVL 18

Expert Comment

by:SquareHead
ID: 9922335
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...

0
 

Expert Comment

by:aneethat
ID: 9926365
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.
0
 

Author Comment

by:mharcais
ID: 9927245
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 ='.
0
 

Author Comment

by:mharcais
ID: 9927812
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.
0
 

Author Comment

by:mharcais
ID: 9927813
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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 18

Accepted Solution

by:
SquareHead earned 300 total points
ID: 9929609
>>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 ='.


Please do a
<%
response.write strSQL
%>
to output the SQL string to the screen, then post that string here...
0
 
LVL 18

Expert Comment

by:SquareHead
ID: 9929624
>> <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?

0
 

Author Comment

by:mharcais
ID: 9929681
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
0
 
LVL 18

Expert Comment

by:SquareHead
ID: 9930020
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?
0
 

Author Comment

by:mharcais
ID: 9957455
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
0
 
LVL 18

Expert Comment

by:SquareHead
ID: 9958428
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?
0
 
LVL 18

Expert Comment

by:SquareHead
ID: 9958455
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
------------------------------------------^
0
 

Author Comment

by:mharcais
ID: 9958538
No. This does not work.
0
 
LVL 18

Expert Comment

by:SquareHead
ID: 9959770
OK, what doesn't work?
0
 
LVL 18

Expert Comment

by:SquareHead
ID: 9961600
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/Web/Web_Languages/ASP/Q_20820756.html (this post)
http://oldlook.experts-exchange.com/Web/Web_Languages/ASP/Q_20824113.html
http://oldlook.experts-exchange.com/Web/Web_Languages/ASP/Q_20828077.html
0
 

Author Comment

by:mharcais
ID: 9963154
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.

0
 
LVL 18

Expert Comment

by:SquareHead
ID: 9965766
>> "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.
0
 

Author Comment

by:mharcais
ID: 9965914
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!
0
 
LVL 18

Expert Comment

by:SquareHead
ID: 9969347
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.
0
 

Author Comment

by:mharcais
ID: 9970889
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...
0
 
LVL 18

Expert Comment

by:SquareHead
ID: 9971988
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


0
 
LVL 18

Expert Comment

by:SquareHead
ID: 9972137
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.
0
 

Author Comment

by:mharcais
ID: 9972331
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?


0
 
LVL 18

Expert Comment

by:SquareHead
ID: 9973342
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?
0
 

Author Comment

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

0
 
LVL 18

Expert Comment

by:SquareHead
ID: 9973481
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?
0
 

Author Comment

by:mharcais
ID: 9973656
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...

0
 
LVL 18

Expert Comment

by:SquareHead
ID: 9973674
Lets see the code you're using on this page, particularly the UPDATE statement...
0
 

Author Comment

by:mharcais
ID: 9984105
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

0
 
LVL 18

Expert Comment

by:SquareHead
ID: 9984911
Ample indeed, thanks.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now