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

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

Show quoteHide quote

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


Show quoteHide quote

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





Show quoteHide quote

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

Thank you all for your help.

Julian


Show quoteHide quote

Help with IIS server Error (Provider error '80040154' Class not registered)
Problems with checkbox value stored in database
Procedure/Query Confusion
HTTP Redirect with POST
NEWBIE NEEDS DIRECTION