Classic ASP Discussion - Multiple Input Boxes Writing to 1 Field in DB

Multiple Input Boxes Writing to 1 Field in DB

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=28878
Printed Date: 28 March 2026 at 8:59pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Multiple Input Boxes Writing to 1 Field in DB

Posted By: kennywhite
Subject: Multiple Input Boxes Writing to 1 Field in DB
Date 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!

Replies:

Posted By: dpyers
Date 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.

Posted By: kennywhite
Date 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.

Posted By: kennywhite
Date 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!

Posted By: kennywhite
Date 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!

Posted By: WebWiz-Bruce
Date 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:-

http://www.google.co.uk/search?sourceid=chrome&ie=UTF-8&q=1+to+many+relationship" rel="nofollow - http://www.google.co.uk/search?sourceid=chrome&ie=UTF-8&q=1+to+many+relationship

-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting

Posted By: kennywhite
Date 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.

Posted By: WebWiz-Bruce
Date 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.

Posted By: kennywhite
Date Posted: 14 October 2010 at 6:22pm

That was simple enough.

I do have one final question.

In my code above I need to also add the total quantity that was entered into all of the "DamagedQTYx" form fields into a field in the *IRR* table called "TotalDamagedQTY".

So I would need to do a loop to add the numerical values of "DamagedQTY1", "DamagedQTY2" and so on. How would I go about doing that?