Accessing Stored Procedure from IIS

S

stjulian

I have a stored procedure that is supposed to
1. Increment a counter in Table A via a transaction
2. Use this value as the primary key to add in an address to customers
Table B
(Referenced as a "DECLARE @CustomerID INT" just after the AS
clause)
3. Return the primary key.

This works perfectly when being called from Query Analyzer supplying values
in an EXEC line, however, accessing it from .ASP (IIS 5.0 on Win2K), the
execution falls right through without adding the customer or incrementing
the counter or giving an error. All conditional routines are executed, but
no work is being done.

Is there anything I can do to raise some sort of error to let me know what
is or isn't happening?

adovbs.inc is linked and the "conditional code" I refer to swaps the stored
procedure name (for add/edit) to add in one more parameter needed for
editing records. The parameters are referenced in exactly the same order as
they are in the procedures, with the return value being mentioned first.

The append parameters lines have been rewritten in short form, long form,
and in a "with" block as shown.

for example:

(Blocked within conditional code)

adocmd.CommandType = adCmdStoredProc
adocmd.CommandText = "spr_AddCustomer"
adocmd.ActiveConnection = conn.ConnectionObject

set param = adocmd.createparameter("@RETURN_VALUE", adInteger,
adParamReturnValue, 0)
adocmd.parameters.append param

(Conditional code end)

With adocmd

set param = .createparameter("@Company", adVarChar, adParamInput, 40,
company)
.parameters.append param
set param = .createparameter("@FirstName", adVarChar, adParamInput, 15,
firstname)
.parameters.append param
set param = .createparameter("@MiddleInitial", adVarChar, adParamInput,
1, middleinitial)
.parameters.append param
set param = .createparameter("@LastName", adVarChar, adParamInput, 20,
lastname)
.parameters.append param

.... (continuing to add parameters in the same order as SP)

.execute lngRecs,,adexecutenorecords
CustomerId = .Parameters("@RETURN_VALUE").Value

End With
 
S

stjulian

Thank you both for your attention....
DDL follows


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.spr_WriteCustomers Created: 2/24/05
JS ******/
CREATE PROCEDURE spr_WriteCustomers

@Company varchar(40) = NULL,
@FirstName varchar(15) = NULL,
@MiddleInitial varchar(1) = NULL,
@LastName varchar(20) = NULL,
@Title varchar(30) = NULL,
@BillingAttnLine varchar(40) = NULL,
@BillingAddress1 varchar(40) = NULL,
@BillingAddress2 varchar(40) = NULL,
@BillingCity varchar(20) = NULL,
@BillingState varchar(3) = NULL,
@BillingZip varchar(10) = NULL,
@FK_CountryCode varchar(3) = NULL,
@BillingCountry varchar(25) = NULL,
@BillingPhone varchar(25) = NULL,
@BillingFax varchar(15) = NULL,

@ShippingFirstName varchar(15) = NULL,
@ShippingLastName varchar(20) = NULL,
@ShippingCompany varchar(40) = NULL,
@ShippingTitle varchar(40) = NULL,
@ShippingAttnLine varchar(40) = NULL,
@ShippingAddress1 varchar(40) = NULL,
@ShippingAddress2 varchar(40) = NULL,
@ShippingCity varchar(20) = NULL,
@ShippingState varchar(3) = NULL,
@ShippingZip varchar(10) = NULL,
@FK_SCountryCode varchar(3) = NULL,
@ShippingCountry varchar(25) = NULL,
@ShippingPhone varchar(25) = NULL,
@ShippingFax varchar(15) = NULL,

@FK_CustomerTierID int = 0,
@UserName varchar(45) = NULL,
@Password varchar(20) = NULL,
@EMail varchar(45) = NULL,

@TaxExempt bit = 0,
@NoEmail bit= 0,
@GREETING1 varchar(35) = NULL,
@GREETING2 varchar(35) = NULL,
@BelongsTo int = 0

AS
BEGIN
SET NOCOUNT ON
DECLARE @custid INT
DECLARE @CREATEDATE DATETIME

--- Begin process
--Get New CustomerID
BEGIN TRAN
SELECT @custid = nextid
FROM tblAutoNumber
WHERE TableName = 'tblCustomers'

UPDATE tblAutoNumber
SET nextid = @custid + 1
WHERE TableName = 'tblCustomers'
COMMIT TRAN

SELECT @CREATEDATE = getdate()

BEGIN
INSERT INTO tblCustomers
(PK_ID,
Company,
FirstName,
MiddleInitial,
LastName,
Title,
BillingAttnLine,
BillingAddress1,
BillingAddress2,
BillingCity,
BillingState,
BillingZip,
FK_CountryCode,
BillingCountry,
ShippingFirstName,
ShippingLastName,
ShippingCompany,
ShippingTitle,
ShippingAttnLine,
ShippingAddress1,
ShippingAddress2,
ShippingCity,
ShippingState,
ShippingZip,
FK_SCountryCode,
ShippingCountry,
FK_CustomerTierID,
UserName,
Password,
Email,
BillingPhone,
ShippingPhone,
BillingFax,
ShippingFax,
LeaseStatus,
LeaseCreditLimit,
FK_CurrencyId,
DisableLogin,
LastModified,
Created,
TaxExempt,
NoEmail,
GREETING1,
GREETING2,
TaxExemptVerified,
AutoCancel,
LastLogin,
BelongsTo)

VALUES(
@custid,
@Company,
@FirstName,
@MiddleInitial,
@LastName,
@Title,
@BillingAttnLine,
@BillingAddress1,
@BillingAddress2,
@BillingCity,
@BillingState,
@BillingZip,
@FK_CountryCode,
@BillingCountry,
@ShippingFirstName,
@ShippingLastName,
@ShippingCompany,
@ShippingTitle,
@ShippingAttnLine,
@ShippingAddress1,
@ShippingAddress2,
@ShippingCity,
@ShippingState,
@ShippingZip,
@FK_SCountryCode,
@ShippingCountry,
@FK_CustomerTierID,
@UserName,
@Password,
@Email,
@BillingPhone,
@ShippingPhone,
@BillingFax,
@ShippingFax,
'',
0,
0,
0,
@CREATEDATE,
@CREATEDATE,
@TaxExempt,
@NoEmail,
@GREETING1,
@GREETING2,
0,
0,
@CREATEDATE,
@BelongsTo)

END
RETURN @custid

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
 
S

stjulian

Wait, I think I got it .... The On Error was in an include file.

Thank you all for your help.

Julian
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,898
Latest member
BlairH7607

Latest Threads

Top