creating order/order detail insert forms

[GLT]

Hi guys!

I have an order table and an order detail table in a database. I am trying to create insert forms using ASP for these so the user can insert new order details. I created an order details table to solve the many to many relationship between the order table and the product table. I am not sure how to do this... any ideas??

What i have so far are two ASP pages one with an insert form for the order table and one for the insert form for the order detail table. Once the user enters details of the order in the first form they are transfered to the page for the order details table, in this form they fill out the product details for the order. I need to be able to link these forms together somehow. I thought that the order details table and form would hold the order ID from the first insert form but i cannot get the ID to pass. I have tried using master detail page set but this doesnt work in this situation.

I would have thought that creating insert pages for order and order details tables would be quite common.. i cant find anything!

Any ideas would be great!

I am a student on placement with not long left and i need to finish my application ASAP. I have asked a few other questions on this forum that nobody seems to know the answer to. PLEASE HELP!

Thanks!
GLT

[GLT]

would use a stored procedure to join and insert the ID into the details table??

any ideas??

[anto_nee]

Try to go to the next page for the order details to enter, with the order main details as hidden fields.. then use two different queries to insert both the tables..
Procedures possible when u have the master table values GLT..

[GLT]

how would i do this? I thought only one insert was allowed per page??

[GLT]

if i did use the method you suggested it would not solve the ID problem which was the original problem. I need to add the ID of the order that has just been entered into the orderline table as a foreign key. This is the original problem how can i do this??

Thanks

[anto_nee]

once u come into the order details From the master order page, u will have the ID right.. insert both at same time.. means write the queries according to the situation..

[vicky_rawat]

I cann't understand why you cann't pass id.
Simply get the last inserted id and pass it to next page.

[vicky_rawat]

In the order page (1st one), add record in to order table and get the order_id
using the following syntex

str = "set nocount on "
str = str & "insert statement "
str = str & " select sn=@@identity set nocount off"
set rssno = oConn.execute(str)
sn = rssno("sn")

pass this value as hidden variable in next page and then you can use this to populate the foreign key in order details table.

[GLT]

I cann't understand why you cann't pass id.
Simply get the last inserted id and pass it to next page.

I cant do it because ive never done it before... have to start somewhere somehow.
Thanks for your help! ill try that now.

[GLT]

Hi!

I cant seem to get it to work. I am a newbi to this sort of thing so im not sure exactly where to put this code or how to edit it (if at all) for my page.

Can someone please help me and tell me what i should do. I cant find any tutorials or anything on this so I cant learn any other way.

Thanks
GLT

[GLT]

I tried using a stored procedure to insert and retieve the ID. here is what i have:-

stored procedure:-

CREATE PROCEDURE spInsertRetrieveID 
	-- Add the parameters for the stored procedure here
	@CustomerID int = 0, 
	@HireOrderEnquiry varchar(50) = 0,
	@DatePlaced varchar(10) = 0,
	@Quantity varchar(50) = 0,
	@Description text,
	@ExtraInfo text
AS

INSERT INTO HireOrderEnquiry (CustomerID, HireOrderEnquiry, DatePlaced, Quantity, Description, ExtraInfo)
VALUES (@CustomerID, @HireOrderEnquiry, @DatePlaced, @Quantity, @Description, @ExtraInfo)
SELECT @@Identity AS HireOrderEnquiryID
RETURN @@Identity
SET NOCOUNT OFF
Go

ASP command:-

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/Silverwingdatabase.asp" -->
<%

Dim cmdInsertRetrieve__CustomerID
cmdInsertRetrieve__CustomerID = ""
if(Request("CustomerID") <> "") then cmdInsertRetrieve__CustomerID = Request("CustomerID")

Dim cmdInsertRetrieve__HireOrderEnquiry
cmdInsertRetrieve__HireOrderEnquiry = ""
if(Request("HireOrderEnquiry") <> "") then cmdInsertRetrieve__HireOrderEnquiry = Request("HireOrderEnquiry")

Dim cmdInsertRetrieve__DatePlaced
cmdInsertRetrieve__DatePlaced = ""
if(Request("DatePlaced") <> "") then cmdInsertRetrieve__DatePlaced = Request("DatePlaced")

Dim cmdInsertRetrieve__Quantity
cmdInsertRetrieve__Quantity = ""
if(Request("Quantity") <> "") then cmdInsertRetrieve__Quantity = Request("Quantity")

Dim cmdInsertRetrieve__Description
cmdInsertRetrieve__Description = ""
if(Request("Description") <> "") then cmdInsertRetrieve__Description = Request("Description")

Dim cmdInsertRetrieve__ExtraInfo
cmdInsertRetrieve__ExtraInfo = ""
if(Request("ExtraInfo") <> "") then cmdInsertRetrieve__ExtraInfo = Request("ExtraInfo")

%>
<%

set cmdInsertRetrieve = Server.CreateObject("ADODB.Command")
cmdInsertRetrieve.ActiveConnection = MM_Silverwingdatabase_STRING
cmdInsertRetrieve.CommandText = "dbo.spInsertRetrieveID"
cmdInsertRetrieve.Parameters.Append cmdInsertRetrieve.CreateParameter("@RETURN_VALUE", 3, 4)
[B]cmdInsertRetrieve.Parameters.Append cmdInsertRetrieve.CreateParameter("@CustomerID", 3, 1,50,cmdInsertRetrieve__CustomerID)[/B]cmdInsertRetrieve.Parameters.Append cmdInsertRetrieve.CreateParameter("@HireOrderEnquiry", 200, 1,50,cmdInsertRetrieve__HireOrderEnquiry)
cmdInsertRetrieve.Parameters.Append cmdInsertRetrieve.CreateParameter("@DatePlaced", 200, 1,10,cmdInsertRetrieve__DatePlaced)
cmdInsertRetrieve.Parameters.Append cmdInsertRetrieve.CreateParameter("@Quantity", 3, 1,50,cmdInsertRetrieve__Quantity)
cmdInsertRetrieve.Parameters.Append cmdInsertRetrieve.CreateParameter("@Description", 200, 1,5000,cmdInsertRetrieve__Description)
cmdInsertRetrieve.Parameters.Append cmdInsertRetrieve.CreateParameter("@ExtraInfo", 200, 1,5000,cmdInsertRetrieve__ExtraInfo)
cmdInsertRetrieve.CommandType = 4
cmdInsertRetrieve.CommandTimeout = 0
cmdInsertRetrieve.Prepared = true
cmdInsertRetrieve.Execute()

%>

I think this should work but im getting the error:-

ADODB.Command (0x800A0D5D)
Application uses a value of the wrong type for the current operation.
/sqlsite/adminaddnewhireorderenquiry.asp, line 36

line 36 is the line in bold.

anyone any ideas what i should do PRETTY PLEASE??

Thanks
GLT

[vicky_rawat]

Hi,

If you can post your code, then we will be in better position to understand, what actually is your requirement.

even the page layout will be helpful.

thanks,
Vivek

[vicky_rawat]

Hi,
Can you show me the structure of table where you are trying to insert the record.

Thanks,
Vivek

[GLT]

ok start again....

I have an order table and an order detail table (called HireOrderEnquiry and HireOrderEnquiryLine).

I want to insert the ID that is automatically generated when details are entered into the order table into the order detail table as the foreign key to keep all details of the same order together.

I would have thought that this problem would be a simple and common one but i cant find anything to help me.

I am a bit of a newbi so I dont really know much coding.

I have read that i should retrieve the last inserted ID so I can then insert it into the order detail table with the other order info. I tried to do this through a stored procedure which I gave the code for in my last message but i cannot get my code to work i keep getting an error message (also given in my last message).

can someone please help??

Heres my full page code as has been asked for... its quite alot sorry!

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/Silverwingdatabase.asp" -->
<%

Dim cmdInsertRetrieve__CustomerID
cmdInsertRetrieve__CustomerID = ""
if(Request("CustomerID") <> "") then cmdInsertRetrieve__CustomerID = Request("CustomerID")

Dim cmdInsertRetrieve__HireOrderEnquiry
cmdInsertRetrieve__HireOrderEnquiry = ""
if(Request("HireOrderEnquiry") <> "") then cmdInsertRetrieve__HireOrderEnquiry = Request("HireOrderEnquiry")

Dim cmdInsertRetrieve__DatePlaced
cmdInsertRetrieve__DatePlaced = ""
if(Request("DatePlaced") <> "") then cmdInsertRetrieve__DatePlaced = Request("DatePlaced")

Dim cmdInsertRetrieve__Quantity
cmdInsertRetrieve__Quantity = ""
if(Request("Quantity") <> "") then cmdInsertRetrieve__Quantity = Request("Quantity")

Dim cmdInsertRetrieve__Description
cmdInsertRetrieve__Description = ""
if(Request("Description") <> "") then cmdInsertRetrieve__Description = Request("Description")

Dim cmdInsertRetrieve__ExtraInfo
cmdInsertRetrieve__ExtraInfo = ""
if(Request("ExtraInfo") <> "") then cmdInsertRetrieve__ExtraInfo = Request("ExtraInfo")

%>
<%

set cmdInsertRetrieve = Server.CreateObject("ADODB.Command")
cmdInsertRetrieve.ActiveConnection = MM_Silverwingdatabase_STRING
cmdInsertRetrieve.CommandText = "dbo.spInsertRetrieveID"
cmdInsertRetrieve.Parameters.Append cmdInsertRetrieve.CreateParameter("@CustomerID", 3, 1,50,cmdInsertRetrieve__CustomerID)
cmdInsertRetrieve.Parameters.Append cmdInsertRetrieve.CreateParameter("@HireOrderEnquiry", 200, 1,50,cmdInsertRetrieve__HireOrderEnquiry)
cmdInsertRetrieve.Parameters.Append cmdInsertRetrieve.CreateParameter("@DatePlaced", 200, 1,10,cmdInsertRetrieve__DatePlaced)
cmdInsertRetrieve.Parameters.Append cmdInsertRetrieve.CreateParameter("@Quantity", 3, 1,50,cmdInsertRetrieve__Quantity)
cmdInsertRetrieve.Parameters.Append cmdInsertRetrieve.CreateParameter("@Description", 200, 1,500,cmdInsertRetrieve__Description)
cmdInsertRetrieve.Parameters.Append cmdInsertRetrieve.CreateParameter("@ExtraInfo", 200, 1,500,cmdInsertRetrieve__ExtraInfo)
cmdInsertRetrieve.CommandType = 4
cmdInsertRetrieve.CommandTimeout = 0
cmdInsertRetrieve.Prepared = true
cmdInsertRetrieve.Execute()

%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html><!-- InstanceBegin template="/Templates/adminpage.dwt.asp" codeOutsideHTMLIsLocked="false" -->
<head>
<!-- InstanceBeginEditable name="doctitle" -->
<title>adminaddnewhireorderenquiry</title>
<!-- InstanceEndEditable --><meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="JavaScript" type="text/JavaScript">
<!--
function MM_goToURL() { //v3.0
  var i, args=MM_goToURL.arguments; document.MM_returnValue = false;
  for (i=0; i<(args.length-1); i+=2) eval(args[i]+".location='"+args[i+1]+"'");
}
//-->
</script>
<style type="text/css">
<!--
.style1 {
	color: #660066;
	font-weight: bold;
}
.style2 {color: #660066}
-->
</style>
<!-- InstanceBeginEditable name="head" -->
<style type="text/css">
<!--
.style3 {	color: #6A2C85;
	font-weight: bold;
}
-->
</style>
<!-- InstanceEndEditable -->
</head>

<body>

<!-- InstanceBeginEditable name="adminpage" -->
<table width="109%" height="37"  border="1" bordercolor="#660066">
  <tr>
    <td width="32%"><img src="images/bg_header.jpg" width="1155" height="101"></td>
  </tr>
  <tr>
    <td><table width="100%" height="257"  border="0" bordercolor="#660066">
        <tr>
          <td width="13%" bgcolor="#99CC99"><table width="99%" height="194"  border="1" bordercolor="#660066">
              <tr> </tr>
              <tr>
                <td><div align="center">
                    <p class="style1"><u>OPTIONS MENU</u></p>
                    <p class="style1">
                      <input name="Search for Customers" type="submit" onClick="MM_goToURL('parent','adminsearchcustomer.asp');return document.MM_returnValue" value="Search for Customers">
                    </p>
                    <p class="style1">
                      <input name="Add New Customer" type="button" onClick="MM_goToURL('parent','adminaddnewcust.asp');return document.MM_returnValue" value="Add New Customer">
                    </p>
                    <p class="style1">
                      <input name="Search For Products" type="submit" onClick="MM_goToURL('parent','adminsearchprod.asp');return document.MM_returnValue" value="Search For Products">
                    </p>
                    <p class="style1">
                      <input name="Add New Product" type="submit" onClick="MM_goToURL('parent','adminaddnewprodmenu.asp');return document.MM_returnValue" value="Add New Product">
                    </p>
                     <p class="style1">
                      <input name="Search Email" type="submit" onClick="MM_goToURL('parent','adminsearchemail.asp');return document.MM_returnValue" value="Search Email">
                    </p>
                    <p class="style1">
                      <input name="Add New Email" type="submit" onClick="MM_goToURL('parent','adminaddnewequip.asp');return document.MM_returnValue" value="Add New Email">
                    </p>
                    <p class="style1">
                      <input name="Search Enquiry/Order" type="submit" onClick="MM_goToURL('parent','searchenquiry.asp');return document.MM_returnValue" value="Search Enquiry/Order">
                    </p>
                    <p class="style1">
                      <input name="Add New Enquiry/Order" type="submit" onClick="MM_goToURL('parent','addnewhireorderenquiry.asp');return document.MM_returnValue" value="Add New Enquiry/Order">
                    </p>
                      <p class="style1">
                      <input name="Search for User" type="button" onClick="MM_goToURL('parent','adminsearchuser.asp');return document.MM_returnValue" value="Search for User">
                    </p>
                    <p class="style1">
                      <input name="Register New User" type="button" onClick="MM_goToURL('parent','adminaddnewuser.asp');return document.MM_returnValue" value="Register New User">
                    </p>
                    <p class="style1">&nbsp; </p>
                    <p class="style1">
<input name="Database Table Info" type="button" onClick="MM_goToURL('parent','admintablesearch.asp');return document.MM_returnValue" value="Database Table Info">                    </p>
                    <p class="style1">&nbsp; </p>
                    <p class="style1">
                      <input name="Go To Homepage" type="button" onClick="MM_goToURL('parent','adminhomepage.asp');return document.MM_returnValue" value="Go To Homepage">
                    </p>
                    <p class="style1">
                      <input name="Go To Welcome Page" type="button" onClick="MM_goToURL('parent','welcomepage.asp');return document.MM_returnValue" value="Go To Welcome Page">
                    </p>
                    <p class="style1">Logout </p>
                </div></td>
          </table></td>
          <td width="87%"><div align="center">
              <p class="style3" style="color: #471E59;">Add new Enquiry, Order or Hire Order </p>
              <p class="style3" style="color: #471E59;">&nbsp;</p>
          
              <form name="form1">
                <table align="center">
                  <tr valign="baseline">
                    <td nowrap align="right">HireOrderEnquiryID:</td>
                    <td></td>
                  </tr>
                  <tr valign="baseline">
                    <td nowrap align="right">CustomerID:</td>
                    <td>
                      <input type="text" name="CustomerID" value="" size="32">
                    </td>
                  </tr>
                  <tr valign="baseline">
                    <td nowrap align="right">HireOrderEnquiry:</td>
                    <td>
                      <select name="HireOrderEnquiry">
                        <option value="ENQUIRY">ENQUIRY</option>
                        <option value="HIRE">HIRE</option>
                        <option value="ORDER">ORDER</option>
                      </select>
</td>
                  </tr>
                  <tr valign="baseline">
                    <td nowrap align="right">DatePlaced:</td>
                    <td>
                      <input type="text" name="DatePlaced" value="" size="32">
                    </td>
                  </tr>
                  <tr valign="baseline">
                    <td nowrap align="right">Quantity:</td>
                    <td>
                      <input type="text" name="Quantity" value="" size="32">
                    </td>
                  </tr>
                  <tr>
                    <td nowrap align="right" valign="top">Description:</td>
                    <td valign="baseline">
                      <textarea name="Description" cols="50" rows="5"></textarea>
                    </td>
                  </tr>
                  <tr>
                    <td nowrap align="right" valign="top">ExtraInfo:</td>
                    <td valign="baseline">
                      <textarea name="ExtraInfo" cols="50" rows="5"></textarea>
                    </td>
                  </tr>
                  <tr valign="baseline">
                    <td nowrap align="right">&nbsp;</td>
                    <td>
                      <input type="submit" onClick="MM_goToURL('parent','adminaddnewhireorderenquiry2.asp');return document.MM_returnValue" value="Insert record">
                    </td>
                  </tr>
                </table>
                
              </form>
              <p><%= cmdInsertRetrieve.Parameters.Item("@RETURN_VALUE").Value %></p>
          </div></td>
        </tr>
    </table></td>
  </tr>
</table>
<!-- InstanceEndEditable -->
</body>
<!-- InstanceEnd -->

[vicky_rawat]

I have noticed two things, that can be creating problem.

1. you have created a parameter

cmdInsertRetrieve.Parameters.Append cmdInsertRetrieve.CreateParameter("@CustomerID", 3, 1,50,cmdInsertRetrieve__CustomerID)

as a varchar, while in stored procedure its declared as integer and I think it will be declared in table also as integer.
change the parameter type to int and this error will go.

2. I had given you the syntex to get last inserted id as follows:

str = "set nocount on "
str = str & "insert statement "
str = str & " select sn=@@identity set nocount off"
set rssno = oConn.execute(str)
sn = rssno("sn")

when you use insert query in a stored procedure, it returns the no fo effected rows.
but we want the stored procedure to return the @@identity.
So, you need to first

set nocount on

to tell sql server to not return the messages.

use the following code.

CREATE PROCEDURE spInsertRetrieveID 
	-- Add the parameters for the stored procedure here
	@CustomerID int = 0, 
	@HireOrderEnquiry varchar(50) = 0,
	@DatePlaced varchar(10) = 0,
	@Quantity varchar(50) = 0,
	@Description text,
	@ExtraInfo text
AS
set nocount off
INSERT INTO HireOrderEnquiry (CustomerID, HireOrderEnquiry, DatePlaced, Quantity, Description, ExtraInfo)
VALUES (@CustomerID, @HireOrderEnquiry, @DatePlaced, @Quantity, @Description, @ExtraInfo)
SELECT HireOrderEnquiryID = @@Identity 
RETURN HireOrderEnquiryID
SET NOCOUNT OFF
Go

I think these things will help you.

Thanks,
Vivek

[GLT]

thanks!

probably silly question but.... where abouts in my code do i put the retrieve code?

[vicky_rawat]

Hi,

The retrieved code is the order id that will be used in order detail page, when you will insert record in order detail table.

Hope this will close this thread

:)

Vivek