User Name Password Register
DaniWeb IT Discussion Community
Home
Forums
Tutorials
Code Snippets
Blogs
Link Directory
All
•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the ASP section within the Web Development category of DaniWeb, a massive community of 391,609 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,615 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Join our community!
Please support our ASP advertiser: Lunarpages ASP Web Hosting
Views: 1353 | Replies: 16
Reply
Page 2 of 2 < 1 2
•
•
Join Date: Aug 2007
Posts: 98
Reputation: GLT is an unknown quantity at this point 
Rep Power: 1
Solved Threads: 0
GLT GLT is offline Offline
Junior Poster in Training

Re: creating order/order detail insert forms

  #11  
Jun 23rd, 2008
I tried using a stored procedure to insert and retieve the ID. here is what i have:-

stored procedure:-

Help with Code Tags 
 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:-

Help with Code Tags 
<%@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)
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,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
Reply With Quote  
•
•
Join Date: Jun 2008
Location: Delhi
Posts: 119
Reputation: vicky_rawat is an unknown quantity at this point 
Rep Power: 1
Solved Threads: 15
vicky_rawat's Avatar
vicky_rawat vicky_rawat is offline Offline
Junior Poster

Re: creating order/order detail insert forms

  #12  
Jun 24th, 2008
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
Reply With Quote  
•
•
Join Date: Jun 2008
Location: Delhi
Posts: 119
Reputation: vicky_rawat is an unknown quantity at this point 
Rep Power: 1
Solved Threads: 15
vicky_rawat's Avatar
vicky_rawat vicky_rawat is offline Offline
Junior Poster

Re: creating order/order detail insert forms

  #13  
Jun 24th, 2008
Hi,
Can you show me the structure of table where you are trying to insert the record.

Thanks,
Vivek
Reply With Quote  
•
•
Join Date: Aug 2007
Posts: 98
Reputation: GLT is an unknown quantity at this point 
Rep Power: 1
Solved Threads: 0
GLT GLT is offline Offline
Junior Poster in Training

Re: creating order/order detail insert forms

  #14  
Jun 24th, 2008
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!

Help with Code Tags 
<%@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 -->
Reply With Quote  
•
•
Join Date: Jun 2008
Location: Delhi
Posts: 119
Reputation: vicky_rawat is an unknown quantity at this point 
Rep Power: 1
Solved Threads: 15
vicky_rawat's Avatar
vicky_rawat vicky_rawat is offline Offline
Junior Poster

Re: creating order/order detail insert forms

  #15  
Jun 24th, 2008
I have noticed two things, that can be creating problem.

1. you have created a parameter

Help with Code Tags 
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:
Help with Code Tags 
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
Help with Code Tags 
set nocount on
to tell sql server to not return the messages.

use the following code.
Help with Code Tags 
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
Reply With Quote  
•
•
Join Date: Aug 2007
Posts: 98
Reputation: GLT is an unknown quantity at this point 
Rep Power: 1
Solved Threads: 0
GLT GLT is offline Offline
Junior Poster in Training

Re: creating order/order detail insert forms

  #16  
Jun 25th, 2008
thanks!

probably silly question but.... where abouts in my code do i put the retrieve code?
Reply With Quote  
•
•
Join Date: Jun 2008
Location: Delhi
Posts: 119
Reputation: vicky_rawat is an unknown quantity at this point 
Rep Power: 1
Solved Threads: 15
vicky_rawat's Avatar
vicky_rawat vicky_rawat is offline Offline
Junior Poster

Re: creating order/order detail insert forms

  #17  
Jun 25th, 2008
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
Reply With Quote  
Reply
Page 2 of 2 < 1 2

•
•
•
•

Only community members can participate in forum threads. You must register or log in to contribute.

Register
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

 

•
•
•
•
•
•
•
•
DaniWeb ASP Marketplace
•
•
•
•
Thread Tools Display Modes
Linear Mode Linear Mode

Similar Threads
Other Threads in the ASP Forum

Advertising Opportunities on DaniWeb
Contact Us - Newsletter Archive - Sitemap - Privacy Statement
All times are GMT -4. The time now is 12:09 am.
Forum system based on vBulletin Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
©2003 - 2008 DaniWeb® LLC