Web Wiz - Green Windows Web Hosting
Web Wiz Homepage Search Web Wiz

  New Posts New Posts RSS Feed - Multiple Input Boxes Writing to 1 Field in DB
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Multiple Input Boxes Writing to 1 Field in DB
 Post Reply Post Reply Page  12>
Author
  Topic Search Topic Search  Topic Options Topic Options
kennywhite View Drop Down
Groupie
Groupie


Joined: 26 February 2009
Location: Indy
Status: Offline
Points: 106 		Post Options Post Options   Thanks (0) Thanks(0)   Quote kennywhite Quote  Post ReplyReply Direct Link To This Post Topic: Multiple Input Boxes Writing to 1 Field in DB
    Posted: 28 September 2010 at 7:41pm
I have a form where users will enter a part number and a quantity.

Later on someone else will view this data in a table.

The challenge here is that they could enter anywhere between 1 and 100 different part numbers and quantities. 

I'm looking for the best way to do this. I have two ideas at the moment.

1, I could create 100 new columns in my MS Access data base, using Javascript to add in the desired number of input boxes. This is obviously not the way to go.

2, I could use Javascript to add in the desired number of input boxes, making the needed html tags for the table part of the input boxes value, so that each new box added would write the html for a new row and column as needed.

Follow that? 

Is there another simpler/cleaner way to do this?

I know each new input box putting data into the same field in the database would add a comma to the end of the data in that box.

If I go with the sloppy method of inserting all of the html to write the rows and columns into the database, is there a way to get rid of those commas?

OR!!!

Is there a way that I can count those commas using ASP to have the data behind it placed into the needed cells of my html table? 


Thanks! 
Back to Top
dpyers View Drop Down
Senior Member
Senior Member


Joined: 12 May 2003
Status: Offline
Points: 3937 		Post Options Post Options   Thanks (0) Thanks(0)   Quote dpyers Quote  Post ReplyReply Direct Link To This Post Posted: 30 September 2010 at 9:11am
Do it as two tables.

TABLE1:
date
customer id
order number
other customer fields

TABLE2:
order number
part
quantity


Table2 should contain a separate record for each part/quantity - order number is your shared key and is a unique key for table1. Putting all your part numbers or quantities into a single field separated by commas is a move you'll regret in the future.

I would use java script to generate new form rows and have the user hit a submit button when they're ready to update all rows.

Lead me not into temptation... I know the short cut, follow me.
Back to Top
kennywhite View Drop Down
Groupie
Groupie


Joined: 26 February 2009
Location: Indy
Status: Offline
Points: 106 		Post Options Post Options   Thanks (0) Thanks(0)   Quote kennywhite Quote  Post ReplyReply Direct Link To This Post Posted: 30 September 2010 at 6:13pm
I have added both tables into my SQL statement on the page where the data will be viewed like this:
strSQL = "SELECT * FROM irr, orders WHERE irr.Order = orders.Order AND irr.ID = " & intID & ";"

Any idea on how I can loop the part numbers and quantities through the table? I had one idea, but it didn't work out.
Back to Top
kennywhite View Drop Down
Groupie
Groupie


Joined: 26 February 2009
Location: Indy
Status: Offline
Points: 106 		Post Options Post Options   Thanks (0) Thanks(0)   Quote kennywhite Quote  Post ReplyReply Direct Link To This Post Posted: 30 September 2010 at 9:19pm
Also,

I am able to write the order number to both database, but how can I write each part number into the database as a new record?

Here is my page that adds everything to the database.
<%
'Dimension variables
Dim adoCon          'Holds the Database Connection Object
Dim rsAddirr   'Holds the recordset for the new record to be added
Dim strSQL          'Holds the SQL query to query the database


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

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("irr.mdb")

'Create an ADO recordset object
Set rsAddirr = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT * FROM irr, orders ORDER BY irr.ID DESC;"

'Set the cursor type we are using so we can navigate through the recordset
rsAddirr.CursorType = 2

'Set the lock type so that the record is locked by ADO when it is updated
rsAddirr.LockType = 3

'Open the recordset with the SQL query 
rsAddirr.Open strSQL, adoCon

'Tell the recordset we are adding a new record to it
rsAddirr.AddNew

'Add a new record to the recordset
rsAddirr.Fields("Receiver") = Request.Form("Receiver")
rsAddirr.Fields("BOLTracking") = Request.Form("BOLTracking")
rsAddirr.Fields("Carrier") = Request.Form("Carrier")
rsAddirr.Fields("InternalPart") = Request.Form("InternalPart")
rsAddirr.Fields("VendorPart") = Request.Form("VendorPart")
rsAddirr.Fields("POQTY") = Request.Form("POQTY")
rsAddirr.Fields("PackQTY") = Request.Form("PackQTY")
rsAddirr.Fields("PhysicalQTY") = Request.Form("PhysicalQTY")
rsAddirr.Fields("ShortQTY") = Request.Form("ShortQTY")
rsAddirr.Fields("OverQTY") = Request.Form("OverQTY")
rsAddirr.Fields("Notes") = Request.Form("Notes")
rsAddirr.Fields("Location") = Request.Form("Location")
rsAddirr.Fields("Date") = Request.Form("Date")
rsAddirr.Fields("Day") = Request.Form("Day")
rsAddirr.Fields("Year") = Request.Form("Year")
rsAddirr.Fields("IssueType") = Request.Form("IssueType")
rsAddirr.Fields("DescrepType") = Request.Form("DescrepType")
rsAddirr.Fields("StorageLoc") = Request.Form("StorageLoc")
rsAddirr.Fields("Month") = Request.Form("Month")
rsAddirr.Fields("irr.PO") = Request.Form("PO")
rsAddirr.Fields("irr.Order") = Request.Form("Order")
rsAddirr.Fields("orders.PO") = Request.Form("PO")
rsAddirr.Fields("orders.Order") = Request.Form("Order")
rsAddirr.Fields("Serials") = Request.Form("Serials")
rsAddirr.Fields("DamagedQTY") = Request.Form("DamagedQTY")
rsAddirr.Fields("USDIR") = Request.Form("USDIR")
rsAddirr.Fields("RMA") = Request.Form("RMA")


'Write the updated recordset to the database
rsAddirr.Update

'Query the newly created record
rsAddirr.Requery

'Place the new ID into a variable
lngID = rsAddirr("irr.ID")

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


'Redirect to the details page using the new ID
Response.Redirect "returndetails.asp?ID=" & lngID
%> 

As you can see right now I am writing the PO and Order fields to both databases. 

What would be the easiest way to create new records for the part numbers?

Thanks!
Back to Top
kennywhite View Drop Down
Groupie
Groupie


Joined: 26 February 2009
Location: Indy
Status: Offline
Points: 106 		Post Options Post Options   Thanks (0) Thanks(0)   Quote kennywhite Quote  Post ReplyReply Direct Link To This Post Posted: 06 October 2010 at 5:43pm
I am still unable to figure out how to write the data to both tables while table1 gets one record, but table2 gets multiple records.

Is it possible to show me how I can do this with my page above?

Thanks!
Back to Top
WebWiz-Bruce View Drop Down
Admin Group
Admin Group
Avatar
Web Wiz Developer

Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844 		Post Options Post Options   Thanks (0) Thanks(0)   Quote WebWiz-Bruce Quote  Post ReplyReply Direct Link To This Post Posted: 07 October 2010 at 10:42am
You need to do some research in to primary and foreign keys and how tables relate to each other using these.

You would then use a database update query to add a single piece of data to the first table, requery the database to get back the primary key value, then have multiple database update queries to add as many records as you need to the second table using the primary key value from the first table as the foreign key value for all the records in the second table that relate to the first.

In database terms this primary and foreign key relationship between the two tables is called and 1 to many relationship.

The following search on Google may give you some ideas in more detail how this works:-
Web Wiz Forums Hosting
ASP.NET Web Hosting
Back to Top
kennywhite View Drop Down
Groupie
Groupie


Joined: 26 February 2009
Location: Indy
Status: Offline
Points: 106 		Post Options Post Options   Thanks (0) Thanks(0)   Quote kennywhite Quote  Post ReplyReply Direct Link To This Post Posted: 13 October 2010 at 2:36pm
So, here is what I did to complete this:

<%


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

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("irr.mdb")



Set irrRS = Server.CreateObject("ADODB.Recordset")
irrRS.Open "SELECT * FROM irr WHERE 1=0", adocon, 3, 3
irrRS.AddNew



'Add a new record to the recordset
irrRS.Fields("Receiver") = Request.Form("Receiver")
irrRS.Fields("BOLTracking") = Request.Form("BOLTracking")
irrRS.Fields("Carrier") = Request.Form("Carrier")
irrRS.Fields("Notes") = Request.Form("Notes")
irrRS.Fields("Location") = Request.Form("Location")
irrRS.Fields("Date") = Request.Form("Date")
irrRS.Fields("Day") = Request.Form("Day")
irrRS.Fields("Year") = Request.Form("Year")
irrRS.Fields("IssueType") = Request.Form("IssueType")
irrRS.Fields("DescrepType") = Request.Form("DescrepType")
irrRS.Fields("StorageLoc") = Request.Form("StorageLoc")
irrRS.Fields("Month") = Request.Form("Month")
irrRS.Fields("PO") = Request.Form("PO")
irrRS.Fields("Order") = Request.Form("Order")
irrRS.Fields("USDIR") = Request.Form("USDIR")
irrRS.Fields("RMA") = Request.Form("RMA")



irrRS.Update
newID = irrRS("ID") ' Captures ID into variable
Issue = irrRS("IssueType")




Set ordRS = Server.CreateObject("ADODB.Recordset")
ordRS.Open "SELECT * FROM orders WHERE 1=0", adocon, 3, 3

For i = 1 To 99999
    VendorPart = Trim(Request("VendorPart" & i))
    If VendorPart = "" Then Exit For ' no more to get

    ordRS.AddNew ' add a new record to put stuff into
    ordRS("IRRID") = newID ' adds the ID variable into orders table
    ordRS("IssueType") = Issue ' adds the Issue variable into orders table
    ordRS("VendorPart") = VendorPart ' we already had this value
    ' add other fields for this record, to match the "i":
    ordRS("DamagedQTY") = Request("DamagedQTY" & i) 
ordRS("PhysicalQTY") = Request("PhysicalQTY" & i) 
ordRS("PackQTY") = Request("PackQTY" & i) 
ordRS("OverQTY") = Request("OverQTY" & i) 
ordRS("ShortQTY") = Request("ShortQTY" & i) 
ordRS("POQTY") = Request("POQTY" & i) 
ordRS("Serials") = Request("Serials" & i) 
ordRS("InternalPart") = Request("InternalPart" & i)

    ordRS.Update
Next





irrRS.Close
ordRS.Close




'Redirect to the details page using the new ID
Response.Redirect (Trim(Issue) & "verifydetails.asp?ID=" & newID)
%> 

On the form I put a number of rows of input boxes for the parts information and named them, VendorPart1, VendorPart2, DamagedQTY1, DamagedQTY2, and so on.

When viewing the information later I match the IRRID field in the orders table to the ID field in the IRR table. 

The last problem that I am having with this site now is this:

There is a page where the user can search for each field above that goes into the orders table. On the page displaying the results I'd place something like this in the WHERE
InternalPart = '" & Request.QueryString("InternalPart") & "'

The problem is that when these orders get closed and should no longer be viewable/searchable in this area of the site a checkbox is ticked in the IRR table, but all of the part numbers and other information are stored in the ORDERS table. 

How can I keep these parts from being pulled up when the order they are associated with are closed?

I hope I explained that well.
Thanks for any help.
Back to Top
WebWiz-Bruce View Drop Down
Admin Group
Admin Group
Avatar
Web Wiz Developer

Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844 		Post Options Post Options   Thanks (0) Thanks(0)   Quote WebWiz-Bruce Quote  Post ReplyReply Direct Link To This Post Posted: 13 October 2010 at 4:40pm
When you run the query on the orders table join it to the irr table and use a WHERE cluase eg:-

SELECT * 
FROM irr, orders
WHERE irr.ID = orders.IRRID AND irr.closed = 0

This will select all the fields from both tables joining the two tables by the irr.ID and orders.IRRID and only including those records where the irr.closed is equal to false.

You would need to change the irr.closed to the same name as the checkbox field you use in your irr tables where you close the order. You may also need to use false or 0 depending on the data type used for the checkbox field.
Web Wiz Forums Hosting
ASP.NET Web Hosting
Back to Top
 Post Reply Post Reply Page  12>

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.08
Copyright ©2001-2026 Web Wiz Ltd.

Become a Fan on Facebook Follow us on X Connect with us on LinkedIn Web Wiz Blogs
About Web Wiz | Contact Web Wiz | Terms & Conditions | Cookies | Privacy Notice

Web Wiz is the trading name of Web Wiz Ltd. Company registration No. 05977755. Registered in England and Wales.
Registered office: Web Wiz Ltd, Unit 18, The Glenmore Centre, Fancy Road, Poole, Dorset, BH12 4FB, UK.

Prices exclude VAT at 20% unless otherwise stated. VAT No. GB988999105 - $, € prices shown as a guideline only.

Copyright ©2001-2026 Web Wiz Ltd. All rights reserved.