Accessing Stored Procedure from IIS

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 WithMake 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" <anonymous@.discussions.microsoft.com> wrote in message
news:#l$GjY2GFHA.2736@.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
>|||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 value
s
> 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 store
d
> procedure name (for add/edit) to add in one more parameter needed for
> editing records. The parameters are referenced in exactly the same order a
s
> 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
>
>|||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" <JohnBell@.discussions.microsoft.com> wrote in message
news:A7EC9405-C84D-4ABC-B9D7-34ED6A4A1BC6@.microsoft.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:
>|||Wait, I think I got it ... The On Error was in an include file.
Thank you all for your help.
Julian
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:A7EC9405-C84D-4ABC-B9D7-34ED6A4A1BC6@.microsoft.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:
>