Adding multiple records to a database

[Freeky]

Hi ... i have been trying this for a long time....

I have a list of items that a user would need to select but am stuck..

I have two tables...

1 Resources
- - resID
- - resName
- - resDesc
- - resLocation

2 Order
- - orderID
- - o_resID
- - o_date
- - o_quantity

I would like to have the list of resources shown, with a text box for quantity, the user would just add the quantity of whatever resource they need for the department, and update the order table with the results. a new record for each resource that was ordered.

any help would be appreciated...
if I didnt make any sense, sorry... :o

[russell]

is the orderId field an identity / auto-incrementing column?

[Freeky]

100% - yes, it is. and o_resID is the linked resource ID or code

[Freeky]

anybody?!

[russell]

ok, quick and dirty, but it goes like this. be sure to add error handling, and such. I added some comments to try to explain the logic

<%
main

Sub main()
Dim strErr '' use to report any db errors
strErr = ""

'' If they submitted the form, look for orders to insert
'' else, display the order form

If Len(Request.Form("sbt") Then '' they pressed the submit button
If processOrder() Then
Response.Write "Order Placed"
Else
Response.Write "An Error Occurred<br>" & strErr
End If
Else '' they didn't submit yet, show the form
showOrderForm
End If
End Sub

Function processOrder(byRef strErr)
Dim f
Dim resID
Dim qty

'' Find the items ordered, they all start with "qty_"
For Each f in Request.Form
If left(f, 4) = "qty_" Then '' we found one
resID = Clng(Right(f, len(f)-4))
qty = Request.Form(f)
If len(qty) Then '' did they enter a value?
qty = clng(qty)
UploadOrder resID, qty, strErr '' yes, so upload to the db
End If
End If
Next

If len(strErr) Then
processOrder = False
Else
processOrder = True
End If
End Function

Sub UploadOrder(byVal resID, byVal qty, byRef strErr)
Dim cmd
Dim sql

sql = "INSERT INTO [Order] (o_resID, o_date, o_quantity) " &_
"VALUES(" & resID & ", '" & Date() & "', " & qty & ")"

'' Important! Comment out the next line while testing/debugging
On Error Resume Next

Set cmd = Server.CreateObject("ADODB.Command")

With cmd
.ActiveConnection = YOUR_CONNECTION_STRING_HERE
.CommandType = 1
.CommandText = sql
.Execute
End With

If Err.number <> 0 Then
strErr = err.number & " " & err.Description
End If

Set cmd = Nothing

On Error Goto 0
End Sub

Sub showOrderForm()
Dim ar
Dim i

ar = getAvailableItems()

If Not isArrray(ar) Then
Response.Write "No Available Items"
Exit Sub
End If

With Response
.Write "<table>" & vbCrLf
writeTableHead

For i = 0 To ubound(ar, 2)
.Write "<tr>" & vbCrLf
.Write "<td>" & ar(0, i) & "</td>" & vbCrLf '' resID
.Write "<td>" & ar(1, i) & "</td>" & vbCrLf '' resName
.Write "<td>" & ar(2, i) & "</td>" & vbCrLf '' resDesc
.Write "<td><input type=text size=6 maxlength=4 name=qty" & ar(0, i) & "></td>" & vbCrLf '' resID
.Write "</tr>" & vbCrLf
Next

.Write "</table>" & vbCrLf
.Write "<input type=submit name=sbt value=""Place Order"">" & vbCrLf
End With
End Sub

Sub writeTableHead()
With Response
.Write "<tr>" & vbCrLf
.Write "<td>ID</td>" & vbCrLf
.Write "<td>Name</td>" & vbCrLf
.Write "<td>Description</td>" & vbCrLf
.Write "<td>Qty</td>" & vbCrLf
.Write "</tr>" & vbCrLf
End With
End Sub

Function getAvailableItems()
Dim cmd
Dim rs
Dim sql

sql = "SELECT resID, resName, resDesc FROM Resources"

Set cmd = Server.CreateObject("ADODB.Command")
Set rs = Server.CreateObject("ADODB.Recordset")

With cmd
.ActiveConnection = YOUR_CONNECTION_STRING_HERE
.CommandType = 1
.CommandText = sql

rs.Open .Execute
End With

If Not rs.EOF Then
getAvailableItems = rs.GetRows
rs.Close
End If

Set rs = Nothing
Set cmd = Nothing
End Function
%>

[Freeky]

Thanks Russel...
I read thru your code and some other sites i found and have come up with this, that works, but is a bit crude and not too secure.... but it works for now...

I you have any recommendations, please let me know.

<%
Dim iChoice

' Read in the choice the user clicked on.
' This will equal "" (an empty string) if no choice is there
iChoice = Request.QueryString("cmd")

' Execute the appropriate branch based upon the value of the variable we just read in.
Select Case iChoice

'The Order is confirmed and sent to the database
Case "ordered"

For x = 1 To Request.Form.count()


if Request.Form.item(x) <> "" then '(1) Removes Blanks
if Request.Form.item(x) <> "Submit" then '(2) Removes the Submit Button value
if Request.Form.item(x) <> 0 then '(3) Removes Zero values
if left(Request.Form.key(x),4) = "ord_" then '(4) Build only itemz with ( ord_ ) prefix

'Builds Variable
cus = Request.Form("useNum")
itemz = mid(Request.Form.key(x),5,6)
order = Request.Form.item(x)

'Run SQL Insert Code
response.write("SQL insert ," & cus & " - " & itemz & " - " & order & "<br>")

'Build Ends

END IF '(4)
END IF '(3)
END IF '(2)
END IF '(1)
Next

'response.redirect("test.asp?cmd=done")


'The order is complete and was processed - move to finnish page
Case "done"

response.write("All gone!")

'No CASE command - Display Order Form and prepare to accept order.
Case Else
%>


<!--#include file="db.asp"-->

<form method="POST" action="test.asp?cmd=ordered" name="orderform">




Usernumber - <input type="text" name="useNum" size="20"><br><br>


<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.open xDb_Conn_Str
Set rs = conn.Execute( "Select * from [vc_Pack_Size]")
Do While (Not rs.Eof)
If Not rs.EOF Then
%>


<%=rs("PACK SIZE CODE")%> - <%=rs("PACK SIZE")%><input type="text" name="ord_<%=rs("PACK SIZE")%>" size="20"><br>

<%
End If

rs.MoveNext
loop
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
%>


<input type="submit" value="Submit" name="B1">
<input type="reset" value="Reset" name="B2">
</form>



<%
End Select
%>