ASP Insert Many-To-One: How-To?

[blyxx86]

Hello all,
I'm somewhat familiar with ASP, SQL and VBScript... Sooo.. I can get it done, but somehow I think there is a better way than I am thinking.

I am trying to create a page for people to add multiple line items to a request. I have two tables set up in a many-to-one relationship. tblRequest and tblRequestDetail

tblRequest
RequestID; Autonumber; Indexed No Dupes
 CustomerID; PK
CustomerTicket; PK (multiple customers can have the same ticket number, but no one customer can have the same ticket number, so this is how the PK is related)
Etc;
 
 tblRequestDetail
 RequestDetailID; PK; AutoNumber
RequestID; FK
ProductID;
ModelID;
etc;
 

I am a little weary on how to set up the "Request Form"... Especially when it comes to handling the insert of the data in the tblRequestDetail table.

Do I have to call a SELECT tblRequest.RequestID FROM tblRequest WHERE ((tblRequest.CustomerTicket = X) AND (tblRequest.CustomerID = Y)) to insert the value into tblRequestDetail.RequestID??

Maybe I can just do an INSERT INTO.... WHERE ((tblRequest.CustomerTicket = X) AND (tblRequest.CustomerID = Y)) after I call the .Update from the first insert of the tblRequest table??

I don't want to really go headlong into this (it's my first attempt at creating an insert form) and be going the wrong direction.

THANK YOU!!!!

[blyxx86]

I have created the following testform...

<% If Request.Form("CustomerID")<>"" And Request.Form("CustomerTicket")<>"" Then %>
<% Dim rs, sql, reqID %>
<% Set rs = server.createObject("adodb.recordset") %>
<% sql = "SELECT * FROM tblRequest" %>
<% rs.open sql, Conn,2,3 %>
 
    <%=Request.Form("CustomerID") %> <br />
    <%=Request.Form("CustomerTicket") %> <br />
    <%=Session("Logon") %> <br />
    <%
      rs.addNew
      rs("EnteredByNet") = Session("Logon")
      rs("CustomerID") = CInt(Request.Form("CustomerID"))
      rs("CustomerTracking") = Request.Form("CustomerTicket")
      rs("RequestTypeID") = 1
      rs.update
      rs.close
 
      sql = "SELECT tblRequest.RequestID FROM tblRequest WHERE tblRequest.CustomerID = 1 AND tblRequest.CustomerTracking=""" & Request.Form("CustomerTicket") & """"
      rs.open sql, Conn, 2, 3
      reqID = rs("RequestID")
      rs.close
 
      sql = "SELECT * FROM tblRequestDetail"
      rs.open sql, Conn, 2, 3
      rs.addNew
      rs("RequestID") = reqID
      rs("ProductID") = 39
      rs("EnteredByNet") = Session("Logon")
      rs.update
      %>
 
<% Else %>
<form action="request.asp" method="post">
    <input name="CustomerID" type="text" />
    <input name="CustomerTicket" type="text" />
    <input name="" type="submit" />
</form>
<% End If %>
 

It's not very clean, and not very efficient (proof of concept design)...

What I really need to do is generate something that first asks for the information in the tblRequest table, and once that is input have a new section display, that shows tblRequestDetail table information. To display something like....

<table width="85%" border="1">
  <tr>
    <td>tblRequest.CustomerID</td>
    <td>tblRequest.CustomerTicket</td>
    <td>tblRequest.LocationCode</td>
    <td>tblRequest.EnteredBy</td>
    <td>tblRequest.ReqType</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>tblRequestDetail.ProductID</td>
    <td>tblRequestDetail.Qty</td>
    <td>tblRequestDetail.Serial</td>
    <td>tblRequestDetail.Type</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>tblRequestDetail.ProductID</td>
    <td>tblRequestDetail.Qty</td>
    <td>tblRequestDetail.Serial</td>
    <td>tblRequestDetail.Type</td>
  </tr>
  <tr>
  <form action="request.asp" method="post">
    <td><input type="submit" value="Add New" /></td>
    <td><input name="tblRequestDetail.ProductID" type="text" /></td>
    <td><input name="tblRequestDetail.Qty" type="text" /></td>
    <td><input name="tblRequestDetail.Serial" type="text" /></td>
    <td><input name="tblRequestDetail.Type" type="text" /></td>
  </form>
  </tr>
</table>
 

See the last row in the table would have the ability to add more items to the request. I am merely looking for guidance on where to go with the selection structure to accomplish a pseudo-shopping cart? Any help is greatly appreciated.

[omerbutt]

you havent mentioned the names of the files which code is in which file atleast mention it so that we could further see that code
Regards,
Omer

[blyxx86]

Thanks for the reply. :) (Even though it didn't show up when I hit "Reply" it showed me your quoted text.)

I'll give you as much information as possible.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<% 
If Session("Logon") = "" Then
Session("LastPage") = Request.ServerVariables("URL")
Response.Redirect("login.asp")
End if
%>
<!--#include file="connstr.asp"-->
<!--#include file="includes\common.asp"-->
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Request New</title>
</head>
 
<body>
<% If Request.Form("CustomerID")<>"" And Request.Form("CustomerTicket")<>"" Then %>
<% Dim rs, sql, reqID %>
<% Set rs = server.createObject("adodb.recordset") %>
<% sql = "SELECT * FROM tblRequest" %>
<% rs.open sql, Conn,2,3 %>
 
    <%=Request.Form("CustomerID") %> <br />
    <%=Request.Form("CustomerTicket") %> <br />
    <%=Session("Logon") %> <br />
    <%
      rs.addNew
      rs("EnteredByNet") = Session("Logon")
      rs("CustomerID") = CInt(Request.Form("CustomerID"))
      rs("CustomerTracking") = Request.Form("CustomerTicket")
      rs("RequestTypeID") = 1
      rs.update
      rs.close
 
      sql = "SELECT tblRequest.RequestID FROM tblRequest WHERE tblRequest.CustomerID = 1 AND tblRequest.CustomerTracking=""" & Request.Form("CustomerTicket") & """"
      rs.open sql, Conn, 2, 3
      reqID = rs("RequestID")
      rs.close
 
      sql = "SELECT * FROM tblRequestDetail"
      rs.open sql, Conn, 2, 3
      rs.addNew
      rs("RequestID") = reqID
      rs("ProductID") = 39
      rs("EnteredByNet") = Session("Logon")
      rs.update
      rs.close
      %>
<% Else %>
<form action="request.asp" method="post">
    <input name="CustomerID" type="text" />
    <input name="CustomerTicket" type="text" />
    <input name="" type="submit" />
</form>
<% End If %>
<table width="85%" border="1">
  <tr>
    <td>tblRequest.CustomerID</td>
    <td>tblRequest.CustomerTicket</td>
    <td>tblRequest.LocationCode</td>
    <td>tblRequest.EnteredBy</td>
    <td>tblRequest.ReqType</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>tblRequestDetail.ProductID</td>
    <td>tblRequestDetail.Qty</td>
    <td>tblRequestDetail.Serial</td>
    <td>tblRequestDetail.Type</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>tblRequestDetail.ProductID</td>
    <td>tblRequestDetail.Qty</td>
    <td>tblRequestDetail.Serial</td>
    <td>tblRequestDetail.Type</td>
  </tr>
  <tr>
<!-- This is the part that I need to be able to add multiple lines to the Request -->
<!-- Maybe I'm hoping it will be short and easy, but there really is a lengthy loop involved, is there not? -->
  <form action="request.asp" method="post">
    <td><input type="submit" value="Add New" /></td>
    <td><input name="tblRequestDetail.ProductID" type="text" /></td>
    <td><input name="tblRequestDetail.Qty" type="text" /></td>
    <td><input name="tblRequestDetail.Serial" type="text" /></td>
    <td><input name="tblRequestDetail.Type" type="text" /></td>
  </form>
  </tr>
</table>
 
</body>
</html>
 

[omerbutt]

Hello all,
Maybe I can just do an INSERT INTO.... WHERE ((tblRequest.CustomerTicket = X) AND (tblRequest.CustomerID = Y)) after I call the .Update from the first insert of the tblRequest table??

I don't want to really go headlong into this (it's my first attempt at creating an insert form) and be going the wrong direction.

THANK YOU!!!!

you cannot use WHERE clause in the insert statement in the SQL query

[blyxx86]

I realized that not too long after I wrote it when I tried. The HTML I provided showed a different approach that is working now, but I still need help creating the form. I thought it would be easy, but it's not being too easy.

So far what I have works... Using a SELECT of the PK(s) in the related table. However, I am thinking I may want to try for an array set-up, before sending the update to the table (can do error checking and everything in the array first).

I am stumped at how to create a form that will allow me to continuously add new items to it, though. Any guidance there? It would be similar to a shopping cart, except users select from a drop down menu what they want to add. Update the quantity and add any additional notes.