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")