Accessing Stored Procedure from IIS

[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

[Aaron [SQL Server MVP]]

Make sure you disable "on error resume next" in your ASP page.
Make sure the stored procedure has SET NOCOUNT ON at the beginning.
Have a look at http://www.aspfaq.com/2201

--
http://www.aspfaq.com/
(Reverse address to reply.)


"stjulian" <an*******@discussions.microsoft.com> wrote in message
news:#l**************@TK2MSFTNGP09.phx.gbl...

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

[John Bell]

Hi

You don't provide DDL for the procedure.
http://www.aspfaq.com/etiquette.asp?id=5006

You may want to check what is happening using profiler, and make sure that
NOCOUNT is ON.

John
"stjulian" wrote:

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

[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

"John Bell" <Jo******@discussions.microsoft.com> wrote in message
news:A7**********************************@microsof t.com...

Hi

You don't provide DDL for the procedure.
http://www.aspfaq.com/etiquette.asp?id=5006

You may want to check what is happening using profiler, and make sure that
NOCOUNT is ON.

John
"stjulian" wrote:

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

[stjulian]

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

Thank you all for your help.

Julian
"John Bell" <Jo******@discussions.microsoft.com> wrote in message
news:A7**********************************@microsof t.com...

Hi

You don't provide DDL for the procedure.
http://www.aspfaq.com/etiquette.asp?id=5006

You may want to check what is happening using profiler, and make sure that
NOCOUNT is ON.

John
"stjulian" wrote:

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