03-02-2012, 07:47 PM
I am working on a page with a webform and a subform. I have two related tables where my main form will populate the one side of the relationship and the subform will be for the many side.
The only thing I can think of is to have my page's onload event create a "blank" record (generating the index and storing it in a hidden field) and then essentially make the proceesing script be an update rather than a create. This of course would involve creating some sort of delete function if the user leaves the page without submiting the form.
I am not sure this is the best approach but it is all I can come up with. I am open to any ideas or guidance from the community on either my subform idea or processing script idea and I thank you in advance.
03-02-2012, 10:57 PM
First of all, what is a "subform"?? You can't have more than one <Form> on a page if you expect use the <form> to submit all data to server-side processing.
I am having trouble with the design of the form processing script, however as since the index on the parent record does not exist at the time of processing I can't think of a way to populate the link field for the child records.
03-02-2012, 11:18 PM
03-03-2012, 12:30 AM
Just create the page with, say, space for 20 or so detail items.
03-03-2012, 03:48 AM
Thanks guys for all your good advice. Old Pedant, I see that there really is no such thing as a "subform" for web applications (this is an Access thing and makes the entry of records on the many side of a one to many relationship very simple).
I guess that by setting up a table to hold the repeating fields it is essentially the same thing. One question, though... how would I set up the page to have the "detail items" repeating the same set of fields without getting a duplicate field id error? I would think that if I tried to add a field with the same id as another field on my form I would get an error.
As it is, I have a table with one row containing the fields for the detail items and now need to repeat those fields for each record of detail items.
03-03-2012, 03:57 AM
One question, though... how would I set up the page to have the "detail items" repeating the same set of fields without getting a duplicate field id error?
Add a number to the end so as to make the ids unique. If there are 20 repetitions of the field then use 1 through 20 on the end of the id. If you are using a loop server side to generate the form fields then you should already have a loop counter you can use.
03-03-2012, 04:12 AM
Since you are used to using Access, I'll assume your server-side code will be ASP.
We should move this to the ASP forum, but here's a simple example:
<form action="saveData.asp" method="post">
Name: <input name="customerName">
<td>1</td><td><input name="product1"/></td><td><input name="quantity1"/></td>
<td>2</td><td><input name="product3"/></td><td><input name="quantity1"/></td>
<td>3</td><td><input name="product3"/></td><td><input name="quantity1"/></td>
<input type="submt" />
And we'll assume you have this for your DB:
CREATE TABLE customers (
custid INT AUTONUMBER PRIMARY KEY,
CREATE TABLE orders (
orderid INT AUTONUMBER PRIMARY KEY,
custid INT FOREIGN KEY REFERENCES customers(custid),
orderdate DATETIME DEFAULT NOW()
CREATE TABLE orderitems (
itemid INT AUTONUMBER PRIMARY KEY,
orderid INT FOREIGN KEY REFERENCES orders(orderid),
productid INT FOREIGN KEY REFERENCES products(productid),
[products table not shown]
And so your ASP code might look like this:
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "...your connection string..."
custname = Trim(Request("customername"))
Set custrs = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT * FROM customers WHERE customername = '" & custname & "'"
custrs.Open SQL, conn, 3, 3 ' static cursor, open for write
If custrs.EOF Then
custrs("customername") = custname
custid = custrs("custid")
SQL = "INSERT INTO orders(custid)"
Set ors = conn.Execute( "SELECT @@IDENTITY") ' this is how you get autonumber of just-created record
orderid = ors(0)
For itemnum = 1 TO 3 ' or to however many rows are in form
prodid = 0 : qty = 0
On Error Resume Next
prodid = CLNG(Trim(Request.Form("productid" & itemnum))
qty = CLNG(Trim(Request.Form("quantity" & itemnum))
On Error GoTo 0
If prodid > 0 AND qty > 0 THen
SQL = "INSERT INTO orderitems(orderid,prodid,quantity) " _
& " VALUES(" & orderid & "," & prodid & "," & qty & ")"
howmany = 0
On Error Resume Next
conn.Execute SQL, howmany
On Error GoTo 0
IF howmany <> 1 Then
Response.Write "Invalid product id for item #" & itemnum & "<br/>"
There are a few shortcuts in there, but not any that matter much.
See? Not really hard, at all.
03-03-2012, 05:03 AM
Wow! Very cool. I am actually using PHP for my server side script (trying to break the MS teather) but the ASP logic is easy enough to follow and much like the code I am used to. Are there any advantages to ASP over PHP?
Thank you guys sooo much for your help.