Unix Technical Forum

stored procedure and asp problems

This is a discussion on stored procedure and asp problems within the SQL Server forums, part of the Microsoft SQL Server category; --> Hi- I'm having problems with the following stored procedure. Below first I have the stored proc, then I have ...


Go Back   Unix Technical Forum > Database Server Software > Microsoft SQL Server > SQL Server

stored procedure and asp problems stored procedure and asp problems

FAQ Members List Calendar Search Today's Posts Mark Forums Read

Reply

 

LinkBack Thread Tools Display Modes
  #1 (permalink)  
Old 02-28-2008, 07:34 PM
bigDWK
 
Posts: n/a
Default stored procedure and asp problems
Hi-
I'm having problems with the following stored procedure. Below
first I have the stored proc, then I have the asp code that calls it.
Randomly it won't insert the order into the database and a way out of
sequence order id is returned. My connection string looks like this:

Provider=SQLOLEDB;Data Source=192.168.1.2;Initial
Catalog=myDatabase;Network Library=DBMSSOCN;Persist Security
Info=True;User Id=myUser;Password=myPassword

The web server and the sql server are directly connected using a
crossover cable. Now for the sproc:

CREATE PROCEDURE myUser.sp_orderInital
@iCustID VarChar(40),
@iPayID VarChar(40),
@iAddrID VarChar(40),
@orderAmount VarChar(25),
@orderComments NText,
@orderSTax VarChar(25),
@orderShipMethod VarChar(200),
@orderShippingAmount VarChar(25),
@orderBrowser VarChar(250),
@orderGrandTotal VarChar(25),
@orderPaymentMethod VarChar(100),
@orderCheckAcctNumber VarChar(100),
@orderCheckNumber VarChar(100),
@orderBankName VarChar(250),
@orderRoutingNumber VarChar(100),
@orderPurchaseOrderName VarChar(250),
@orderPurchaseOrderNumber VarChar(100),
@orderRemoteAddress VarChar(30),
@orderTradingPartner VarChar(250),
@orderHttpReferrer VarChar(250)
AS
DECLARE @orderID INT

INSERT INTO sfOrders (orderCustId, orderPayId, orderAddrId,
orderAmount, orderComments, orderSTax, orderShipMethod,
orderShippingAmount, orderGrandTotal, orderPaymentMethod,
orderCheckAcctNumber, orderCheckNumber, orderBankName,
orderRoutingNumber, orderPurchaseOrderName,
orderPurchaseOrderNumber,
orderRemoteAddress, orderHttpReferrer, orderTradingPartner,
orderBrowser)
VALUES (@iCustID, @iPayID, @iAddrID,
@orderAmount, @orderComments, @orderSTax, @orderShipMethod,
@orderShippingAmount, @orderGrandTotal, @orderPaymentMethod,
@orderCheckAcctNumber, @orderCheckNumber, @orderBankName,
@orderRoutingNumber, @orderPurchaseOrderName,
@orderPurchaseOrderNumber,
@orderRemoteAddress, @orderHttpReferrer, @orderTradingPartner,
@orderBrowser)

SELECT @orderID = @@IDENTITY
RETURN @orderID
GO

************************************************** ******
Now the asp code starts
************************************************** ******

Dim iOrderID

Dim cmdOrders
Set cmdOrders = Server.CreateObject("ADODB.Command")

With cmdOrders
.ActiveConnection = cnn
.CommandText = "bradley.sp_OrderInital"
.CommandType = adCmdStoredProc
'sdfsdfsdff;sdfsd;
.Parameters.Append .CreateParameter ("RetVal", adVarWChar,
adParamReturnValue, 40)
.Parameters.Append .CreateParameter ("@iCustID", adVarWChar,
adParamInput, 40)
.Parameters.Append .CreateParameter ("@iPayID", adVarWChar,
adParamInput, 40)
.Parameters.Append .CreateParameter ("@iAddrID", adVarWChar,
adParamInput, 40)
.Parameters.Append .CreateParameter ("@orderAmount", adVarWChar,
adParamInput, 25)
.Parameters.Append .CreateParameter ("@orderComments",
adLongVarWChar, adParamInput, 4000)
.Parameters.Append .CreateParameter ("@orderSTax", adVarWChar,
adParamInput, 25)
.Parameters.Append .CreateParameter ("@orderShipMethod", adVarWChar,
adParamInput, 200)
.Parameters.Append .CreateParameter ("@orderShippingAmount",
adVarWChar, adParamInput, 25)
.Parameters.Append .CreateParameter ("@orderBrowser", adVarWChar,
adParamInput, 250)
.Parameters.Append .CreateParameter ("@orderGrandTotal", adVarChar,
adParamInput, 25)
.Parameters.Append .CreateParameter ("@orderPaymentMethod",
adVarWChar, adParamInput, 100)
.Parameters.Append .CreateParameter ("@orderCheckAcctNumber",
adVarWChar, adParamInput, 100)
.Parameters.Append .CreateParameter ("@orderCheckNumber",
adVarWChar, adParamInput, 100)
.Parameters.Append .CreateParameter ("@orderBankName", adVarWChar,
adParamInput, 250)
.Parameters.Append .CreateParameter ("@orderRoutingNumber",
adVarWChar, adParamInput, 100)
.Parameters.Append .CreateParameter ("@orderPurchaseOrderName",
adVarWChar, adParamInput, 250)
.Parameters.Append .CreateParameter ("@orderPurchaseOrderNumber",
adVarWChar, adParamInput, 100)
.Parameters.Append .CreateParameter ("@orderRemoteAddress",
adVarWChar, adParamInput, 30)
.Parameters.Append .CreateParameter ("@orderTradingPartner",
adVarWChar, adParamInput, 250)
.Parameters.Append .CreateParameter ("@orderHttpReferrer",
adVarWChar, adParamInput, 250)


IF IsNumeric(iCustID)Then
.Parameters ("@iCustId") = trim(iCustID)
ELSE
.Parameters ("@iCustId") = "0"
END IF
IF IsNumeric(iPayID)Then
.Parameters("@iPayId") = trim(iPayID)
ELSE
.Parameters("@iPayId") = "0"
END IF
If IsNumeric(iAddrID)Then
.Parameters("@iAddrId") = trim(iAddrID)
ELSE
.Parameters("@iAddrId") = "0"
End If
.Parameters("@orderAmount") = trim(sTotalPrice)
IF trim(sShipInstructions) <> "" THEN
.Parameters("@orderComments") = trim(sShipInstructions)
ELSE
.Parameters("@orderComments") = ""
END IF
.Parameters("@orderSTax") = trim(sTotalSTax)
'rsOrder.Fields("orderCTax") = trim(sTotalCTax)
'rsOrder.Fields("orderHandling") = trim(sTotalHandling)

.Parameters("@orderShipMethod") = trim(sShipMethodName)
.Parameters("@orderShippingAmount") = trim(sShipping)


.Parameters("@orderBrowser") =
trim(Request.ServerVariables("HTTP_USER_AGENT"))
.Parameters("@orderGrandTotal") = trim(sGrandTotal)
.Parameters("@orderPaymentMethod") = trim(sPaymentMethod)
IF trim(iCheckingAccountNumber) <> "" THEN
.Parameters("@orderCheckAcctNumber") = trim(iCheckingAccountNumber)
ELSE
.Parameters("@orderCheckAcctNumber") = ""
END IF
IF trim(iCheckNumber) <> "" THEN
.Parameters("@orderCheckNumber") = trim(iCheckNumber)
ELSE
.Parameters("@orderCheckNumber") = ""
END IF
IF trim(sBankName) <> "" THEN
.Parameters("@orderBankName") = trim(sBankName)
ELSE
.Parameters("@orderBankName") = ""
END IF
IF trim(iRoutingNumber) <> "" THEN
.Parameters("@orderRoutingNumber") = trim(iRoutingNumber)
ELSE
.Parameters("@orderRoutingNumber") = ""
END IF
IF trim(iPOName) <> "" THEN
.Parameters("@orderPurchaseOrderName") = trim(iPOName)
ELSE
.Parameters("@orderPurchaseOrderName") = ""
END IF
IF trim(iPONumber) <> "" THEN
.Parameters("@orderPurchaseOrderNumber") = trim(iPONumber)
ELSE
.Parameters("@orderPurchaseOrderNumber") = ""
END IF


if isArray(aReferer) then
on error resume next
IF aReferer(2) <> "" THEN
.Parameters("@orderRemoteAddress") = aReferer(2)
ELSE
.Parameters("@orderRemoteAddress") = ""
END IF
IF aReferer(0) <> "" THEN
.Parameters("@orderTradingPartner") = aReferer(0)
ELSE
.Parameters("@orderTradingPartner") = ""
END IF
IF aReferer(1) <> "" THEN
.Parameters("@orderHttpReferrer") = aReferer(1)
ELSE
.Parameters("@orderHttpReferrer") = ""
END IF
else
.Parameters("@orderRemoteAddress") = ""
.Parameters("@orderTradingPartner") = ""
.Parameters("@orderHttpReferrer") = ""
end if


Dim lngRecs
.Execute

iOrderID = .Parameters("RetVal") 'rsOrder.Fields("orderID")
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #2 (permalink)  
Old 02-28-2008, 07:34 PM
Dan Guzman
 
Posts: n/a
Default Re: stored procedure and asp problems
Try including SET NOCOUNT ON at the beginning of the proc. This will
suppress DONE_IN_PROC messages that can cause issues with ADO apps.

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index....partmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"bigDWK" <daveGoogle@davewking.com> wrote in message
news:9901b9fa.0310291028.6aa4141f@posting.google.c om...
> Hi-
> I'm having problems with the following stored procedure. Below
> first I have the stored proc, then I have the asp code that calls it.
> Randomly it won't insert the order into the database and a way out of
> sequence order id is returned. My connection string looks like this:
>
> Provider=SQLOLEDB;Data Source=192.168.1.2;Initial
> Catalog=myDatabase;Network Library=DBMSSOCN;Persist Security
> Info=True;User Id=myUser;Password=myPassword
>
> The web server and the sql server are directly connected using a
> crossover cable. Now for the sproc:
>
> CREATE PROCEDURE myUser.sp_orderInital
> @iCustID VarChar(40),
> @iPayID VarChar(40),
> @iAddrID VarChar(40),
> @orderAmount VarChar(25),
> @orderComments NText,
> @orderSTax VarChar(25),
> @orderShipMethod VarChar(200),
> @orderShippingAmount VarChar(25),
> @orderBrowser VarChar(250),
> @orderGrandTotal VarChar(25),
> @orderPaymentMethod VarChar(100),
> @orderCheckAcctNumber VarChar(100),
> @orderCheckNumber VarChar(100),
> @orderBankName VarChar(250),
> @orderRoutingNumber VarChar(100),
> @orderPurchaseOrderName VarChar(250),
> @orderPurchaseOrderNumber VarChar(100),
> @orderRemoteAddress VarChar(30),
> @orderTradingPartner VarChar(250),
> @orderHttpReferrer VarChar(250)
> AS
> DECLARE @orderID INT
>
> INSERT INTO sfOrders (orderCustId, orderPayId, orderAddrId,
> orderAmount, orderComments, orderSTax, orderShipMethod,
> orderShippingAmount, orderGrandTotal, orderPaymentMethod,
> orderCheckAcctNumber, orderCheckNumber, orderBankName,
> orderRoutingNumber, orderPurchaseOrderName,
> orderPurchaseOrderNumber,
> orderRemoteAddress, orderHttpReferrer, orderTradingPartner,
> orderBrowser)
> VALUES (@iCustID, @iPayID, @iAddrID,
> @orderAmount, @orderComments, @orderSTax, @orderShipMethod,
> @orderShippingAmount, @orderGrandTotal, @orderPaymentMethod,
> @orderCheckAcctNumber, @orderCheckNumber, @orderBankName,
> @orderRoutingNumber, @orderPurchaseOrderName,
> @orderPurchaseOrderNumber,
> @orderRemoteAddress, @orderHttpReferrer, @orderTradingPartner,
> @orderBrowser)
>
> SELECT @orderID = @@IDENTITY
> RETURN @orderID
> GO
>
> ************************************************** ******
> Now the asp code starts
> ************************************************** ******
>
> Dim iOrderID
>
> Dim cmdOrders
> Set cmdOrders = Server.CreateObject("ADODB.Command")
>
> With cmdOrders
> .ActiveConnection = cnn
> .CommandText = "bradley.sp_OrderInital"
> .CommandType = adCmdStoredProc
> 'sdfsdfsdff;sdfsd;
> .Parameters.Append .CreateParameter ("RetVal", adVarWChar,
> adParamReturnValue, 40)
> .Parameters.Append .CreateParameter ("@iCustID", adVarWChar,
> adParamInput, 40)
> .Parameters.Append .CreateParameter ("@iPayID", adVarWChar,
> adParamInput, 40)
> .Parameters.Append .CreateParameter ("@iAddrID", adVarWChar,
> adParamInput, 40)
> .Parameters.Append .CreateParameter ("@orderAmount", adVarWChar,
> adParamInput, 25)
> .Parameters.Append .CreateParameter ("@orderComments",
> adLongVarWChar, adParamInput, 4000)
> .Parameters.Append .CreateParameter ("@orderSTax", adVarWChar,
> adParamInput, 25)
> .Parameters.Append .CreateParameter ("@orderShipMethod", adVarWChar,
> adParamInput, 200)
> .Parameters.Append .CreateParameter ("@orderShippingAmount",
> adVarWChar, adParamInput, 25)
> .Parameters.Append .CreateParameter ("@orderBrowser", adVarWChar,
> adParamInput, 250)
> .Parameters.Append .CreateParameter ("@orderGrandTotal", adVarChar,
> adParamInput, 25)
> .Parameters.Append .CreateParameter ("@orderPaymentMethod",
> adVarWChar, adParamInput, 100)
> .Parameters.Append .CreateParameter ("@orderCheckAcctNumber",
> adVarWChar, adParamInput, 100)
> .Parameters.Append .CreateParameter ("@orderCheckNumber",
> adVarWChar, adParamInput, 100)
> .Parameters.Append .CreateParameter ("@orderBankName", adVarWChar,
> adParamInput, 250)
> .Parameters.Append .CreateParameter ("@orderRoutingNumber",
> adVarWChar, adParamInput, 100)
> .Parameters.Append .CreateParameter ("@orderPurchaseOrderName",
> adVarWChar, adParamInput, 250)
> .Parameters.Append .CreateParameter ("@orderPurchaseOrderNumber",
> adVarWChar, adParamInput, 100)
> .Parameters.Append .CreateParameter ("@orderRemoteAddress",
> adVarWChar, adParamInput, 30)
> .Parameters.Append .CreateParameter ("@orderTradingPartner",
> adVarWChar, adParamInput, 250)
> .Parameters.Append .CreateParameter ("@orderHttpReferrer",
> adVarWChar, adParamInput, 250)
>
>
> IF IsNumeric(iCustID)Then
> .Parameters ("@iCustId") = trim(iCustID)
> ELSE
> .Parameters ("@iCustId") = "0"
> END IF
> IF IsNumeric(iPayID)Then
> .Parameters("@iPayId") = trim(iPayID)
> ELSE
> .Parameters("@iPayId") = "0"
> END IF
> If IsNumeric(iAddrID)Then
> .Parameters("@iAddrId") = trim(iAddrID)
> ELSE
> .Parameters("@iAddrId") = "0"
> End If
> .Parameters("@orderAmount") = trim(sTotalPrice)
> IF trim(sShipInstructions) <> "" THEN
> .Parameters("@orderComments") = trim(sShipInstructions)
> ELSE
> .Parameters("@orderComments") = ""
> END IF
> .Parameters("@orderSTax") = trim(sTotalSTax)
> 'rsOrder.Fields("orderCTax") = trim(sTotalCTax)
> 'rsOrder.Fields("orderHandling") = trim(sTotalHandling)
>
> .Parameters("@orderShipMethod") = trim(sShipMethodName)
> .Parameters("@orderShippingAmount") = trim(sShipping)
>
>
> .Parameters("@orderBrowser") =
> trim(Request.ServerVariables("HTTP_USER_AGENT"))
> .Parameters("@orderGrandTotal") = trim(sGrandTotal)
> .Parameters("@orderPaymentMethod") = trim(sPaymentMethod)
> IF trim(iCheckingAccountNumber) <> "" THEN
> .Parameters("@orderCheckAcctNumber") = trim(iCheckingAccountNumber)
> ELSE
> .Parameters("@orderCheckAcctNumber") = ""
> END IF
> IF trim(iCheckNumber) <> "" THEN
> .Parameters("@orderCheckNumber") = trim(iCheckNumber)
> ELSE
> .Parameters("@orderCheckNumber") = ""
> END IF
> IF trim(sBankName) <> "" THEN
> .Parameters("@orderBankName") = trim(sBankName)
> ELSE
> .Parameters("@orderBankName") = ""
> END IF
> IF trim(iRoutingNumber) <> "" THEN
> .Parameters("@orderRoutingNumber") = trim(iRoutingNumber)
> ELSE
> .Parameters("@orderRoutingNumber") = ""
> END IF
> IF trim(iPOName) <> "" THEN
> .Parameters("@orderPurchaseOrderName") = trim(iPOName)
> ELSE
> .Parameters("@orderPurchaseOrderName") = ""
> END IF
> IF trim(iPONumber) <> "" THEN
> .Parameters("@orderPurchaseOrderNumber") = trim(iPONumber)
> ELSE
> .Parameters("@orderPurchaseOrderNumber") = ""
> END IF
>
>
> if isArray(aReferer) then
> on error resume next
> IF aReferer(2) <> "" THEN
> .Parameters("@orderRemoteAddress") = aReferer(2)
> ELSE
> .Parameters("@orderRemoteAddress") = ""
> END IF
> IF aReferer(0) <> "" THEN
> .Parameters("@orderTradingPartner") = aReferer(0)
> ELSE
> .Parameters("@orderTradingPartner") = ""
> END IF
> IF aReferer(1) <> "" THEN
> .Parameters("@orderHttpReferrer") = aReferer(1)
> ELSE
> .Parameters("@orderHttpReferrer") = ""
> END IF
> else
> .Parameters("@orderRemoteAddress") = ""
> .Parameters("@orderTradingPartner") = ""
> .Parameters("@orderHttpReferrer") = ""
> end if
>
>
> Dim lngRecs
> .Execute
>
> iOrderID = .Parameters("RetVal") 'rsOrder.Fields("orderID")


Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #3 (permalink)  
Old 02-28-2008, 07:34 PM
anamika
 
Posts: n/a
Default Re: stored procedure and asp problems
Try catching the error inside the stored procedure itself by

if @@error <> 0 goto error_handler
in that way u can catch the error in sp itself. I think your sp needs
to supply one output parameter as u r creatinbg a retval in create
parameter , don't u get any error while calling this??

daveGoogle@davewking.com (bigDWK) wrote in message news:<9901b9fa.0310291028.6aa4141f@posting.google. com>...
> Hi-
> I'm having problems with the following stored procedure. Below
> first I have the stored proc, then I have the asp code that calls it.
> Randomly it won't insert the order into the database and a way out of
> sequence order id is returned. My connection string looks like this:
>
> Provider=SQLOLEDB;Data Source=192.168.1.2;Initial
> Catalog=myDatabase;Network Library=DBMSSOCN;Persist Security
> Info=True;User Id=myUser;Password=myPassword
>
> The web server and the sql server are directly connected using a
> crossover cable. Now for the sproc:
>
> CREATE PROCEDURE myUser.sp_orderInital
> @iCustID VarChar(40),
> @iPayID VarChar(40),
> @iAddrID VarChar(40),
> @orderAmount VarChar(25),
> @orderComments NText,
> @orderSTax VarChar(25),
> @orderShipMethod VarChar(200),
> @orderShippingAmount VarChar(25),
> @orderBrowser VarChar(250),
> @orderGrandTotal VarChar(25),
> @orderPaymentMethod VarChar(100),
> @orderCheckAcctNumber VarChar(100),
> @orderCheckNumber VarChar(100),
> @orderBankName VarChar(250),
> @orderRoutingNumber VarChar(100),
> @orderPurchaseOrderName VarChar(250),
> @orderPurchaseOrderNumber VarChar(100),
> @orderRemoteAddress VarChar(30),
> @orderTradingPartner VarChar(250),
> @orderHttpReferrer VarChar(250)
> AS
> DECLARE @orderID INT
>
> INSERT INTO sfOrders (orderCustId, orderPayId, orderAddrId,
> orderAmount, orderComments, orderSTax, orderShipMethod,
> orderShippingAmount, orderGrandTotal, orderPaymentMethod,
> orderCheckAcctNumber, orderCheckNumber, orderBankName,
> orderRoutingNumber, orderPurchaseOrderName,
> orderPurchaseOrderNumber,
> orderRemoteAddress, orderHttpReferrer, orderTradingPartner,
> orderBrowser)
> VALUES (@iCustID, @iPayID, @iAddrID,
> @orderAmount, @orderComments, @orderSTax, @orderShipMethod,
> @orderShippingAmount, @orderGrandTotal, @orderPaymentMethod,
> @orderCheckAcctNumber, @orderCheckNumber, @orderBankName,
> @orderRoutingNumber, @orderPurchaseOrderName,
> @orderPurchaseOrderNumber,
> @orderRemoteAddress, @orderHttpReferrer, @orderTradingPartner,
> @orderBrowser)
>
> SELECT @orderID = @@IDENTITY
> RETURN @orderID
> GO
>
> ************************************************** ******
> Now the asp code starts
> ************************************************** ******
>
> Dim iOrderID
>
> Dim cmdOrders
> Set cmdOrders = Server.CreateObject("ADODB.Command")
>
> With cmdOrders
> .ActiveConnection = cnn
> .CommandText = "bradley.sp_OrderInital"
> .CommandType = adCmdStoredProc
> 'sdfsdfsdff;sdfsd;
> .Parameters.Append .CreateParameter ("RetVal", adVarWChar,
> adParamReturnValue, 40)
> .Parameters.Append .CreateParameter ("@iCustID", adVarWChar,
> adParamInput, 40)
> .Parameters.Append .CreateParameter ("@iPayID", adVarWChar,
> adParamInput, 40)
> .Parameters.Append .CreateParameter ("@iAddrID", adVarWChar,
> adParamInput, 40)
> .Parameters.Append .CreateParameter ("@orderAmount", adVarWChar,
> adParamInput, 25)
> .Parameters.Append .CreateParameter ("@orderComments",
> adLongVarWChar, adParamInput, 4000)
> .Parameters.Append .CreateParameter ("@orderSTax", adVarWChar,
> adParamInput, 25)
> .Parameters.Append .CreateParameter ("@orderShipMethod", adVarWChar,
> adParamInput, 200)
> .Parameters.Append .CreateParameter ("@orderShippingAmount",
> adVarWChar, adParamInput, 25)
> .Parameters.Append .CreateParameter ("@orderBrowser", adVarWChar,
> adParamInput, 250)
> .Parameters.Append .CreateParameter ("@orderGrandTotal", adVarChar,
> adParamInput, 25)
> .Parameters.Append .CreateParameter ("@orderPaymentMethod",
> adVarWChar, adParamInput, 100)
> .Parameters.Append .CreateParameter ("@orderCheckAcctNumber",
> adVarWChar, adParamInput, 100)
> .Parameters.Append .CreateParameter ("@orderCheckNumber",
> adVarWChar, adParamInput, 100)
> .Parameters.Append .CreateParameter ("@orderBankName", adVarWChar,
> adParamInput, 250)
> .Parameters.Append .CreateParameter ("@orderRoutingNumber",
> adVarWChar, adParamInput, 100)
> .Parameters.Append .CreateParameter ("@orderPurchaseOrderName",
> adVarWChar, adParamInput, 250)
> .Parameters.Append .CreateParameter ("@orderPurchaseOrderNumber",
> adVarWChar, adParamInput, 100)
> .Parameters.Append .CreateParameter ("@orderRemoteAddress",
> adVarWChar, adParamInput, 30)
> .Parameters.Append .CreateParameter ("@orderTradingPartner",
> adVarWChar, adParamInput, 250)
> .Parameters.Append .CreateParameter ("@orderHttpReferrer",
> adVarWChar, adParamInput, 250)
>
>
> IF IsNumeric(iCustID)Then
> .Parameters ("@iCustId") = trim(iCustID)
> ELSE
> .Parameters ("@iCustId") = "0"
> END IF
> IF IsNumeric(iPayID)Then
> .Parameters("@iPayId") = trim(iPayID)
> ELSE
> .Parameters("@iPayId") = "0"
> END IF
> If IsNumeric(iAddrID)Then
> .Parameters("@iAddrId") = trim(iAddrID)
> ELSE
> .Parameters("@iAddrId") = "0"
> End If
> .Parameters("@orderAmount") = trim(sTotalPrice)
> IF trim(sShipInstructions) <> "" THEN
> .Parameters("@orderComments") = trim(sShipInstructions)
> ELSE
> .Parameters("@orderComments") = ""
> END IF
> .Parameters("@orderSTax") = trim(sTotalSTax)
> 'rsOrder.Fields("orderCTax") = trim(sTotalCTax)
> 'rsOrder.Fields("orderHandling") = trim(sTotalHandling)
>
> .Parameters("@orderShipMethod") = trim(sShipMethodName)
> .Parameters("@orderShippingAmount") = trim(sShipping)
>
>
> .Parameters("@orderBrowser") =
> trim(Request.ServerVariables("HTTP_USER_AGENT"))
> .Parameters("@orderGrandTotal") = trim(sGrandTotal)
> .Parameters("@orderPaymentMethod") = trim(sPaymentMethod)
> IF trim(iCheckingAccountNumber) <> "" THEN
> .Parameters("@orderCheckAcctNumber") = trim(iCheckingAccountNumber)
> ELSE
> .Parameters("@orderCheckAcctNumber") = ""
> END IF
> IF trim(iCheckNumber) <> "" THEN
> .Parameters("@orderCheckNumber") = trim(iCheckNumber)
> ELSE
> .Parameters("@orderCheckNumber") = ""
> END IF
> IF trim(sBankName) <> "" THEN
> .Parameters("@orderBankName") = trim(sBankName)
> ELSE
> .Parameters("@orderBankName") = ""
> END IF
> IF trim(iRoutingNumber) <> "" THEN
> .Parameters("@orderRoutingNumber") = trim(iRoutingNumber)
> ELSE
> .Parameters("@orderRoutingNumber") = ""
> END IF
> IF trim(iPOName) <> "" THEN
> .Parameters("@orderPurchaseOrderName") = trim(iPOName)
> ELSE
> .Parameters("@orderPurchaseOrderName") = ""
> END IF
> IF trim(iPONumber) <> "" THEN
> .Parameters("@orderPurchaseOrderNumber") = trim(iPONumber)
> ELSE
> .Parameters("@orderPurchaseOrderNumber") = ""
> END IF
>
>
> if isArray(aReferer) then
> on error resume next
> IF aReferer(2) <> "" THEN
> .Parameters("@orderRemoteAddress") = aReferer(2)
> ELSE
> .Parameters("@orderRemoteAddress") = ""
> END IF
> IF aReferer(0) <> "" THEN
> .Parameters("@orderTradingPartner") = aReferer(0)
> ELSE
> .Parameters("@orderTradingPartner") = ""
> END IF
> IF aReferer(1) <> "" THEN
> .Parameters("@orderHttpReferrer") = aReferer(1)
> ELSE
> .Parameters("@orderHttpReferrer") = ""
> END IF
> else
> .Parameters("@orderRemoteAddress") = ""
> .Parameters("@orderTradingPartner") = ""
> .Parameters("@orderHttpReferrer") = ""
> end if
>
>
> Dim lngRecs
> .Execute
>
> iOrderID = .Parameters("RetVal") 'rsOrder.Fields("orderID")
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #4 (permalink)  
Old 02-28-2008, 07:34 PM
Simon Hayes
 
Posts: n/a
Default Re: stored procedure and asp problems
daveGoogle@davewking.com (bigDWK) wrote in message news:<9901b9fa.0310291028.6aa4141f@posting.google. com>...
> Hi-
> I'm having problems with the following stored procedure. Below
> first I have the stored proc, then I have the asp code that calls it.
> Randomly it won't insert the order into the database and a way out of
> sequence order id is returned. My connection string looks like this:
>
> Provider=SQLOLEDB;Data Source=192.168.1.2;Initial
> Catalog=myDatabase;Network Library=DBMSSOCN;Persist Security
> Info=True;User Id=myUser;Password=myPassword
>
> The web server and the sql server are directly connected using a
> crossover cable. Now for the sproc:
>

<snip>

Assuming that you have MSSQL 2000 (you didn't mention the version), I
guess you should be using scope_identity(), not @@identity - check
Books Online for the difference. That may explain the "out of order"
identity value (although identity values are not sequential), although
it wouldn't explain the "won't insert the order" issue. Can you
clarify what you mean by that - is there an error message?

By the way, you shouldn't use sp_ for stored procedure names - that's
for system stored proc only.

Simon
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Reply

« Connection Time Out Error | How to edit long text data in SQL Server »

Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On
Forum Jump


All times are GMT. The time now is 09:42 AM.

Contact Us - Unix Technical Forums - Top

Powered by vBulletin® Version 3.6.5
Copyright ©2000 - 2009, Jelsoft Enterprises Ltd.
SEO by vBSEO 3.2.0
www.UnixAdminTalk.com