MS SQL Server execute nonquery error

Larry Brister
Larry Brister used Ask the Experts™
on
On my Exe3cuteNonQuery I am getting an error message in the StackTrace

The process is consuming a MS SQL Server 2012 Stored Procedure
I am passing in @25 parameters

This is the error message in StackTrace...
"Error converting nvarchar to int"

Which end of the process is which?

And is there a way tyo capture which line of the SQL the error is on?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Can u attach the entire query pls.
Larry Bristersr. Developer

Author

Commented:
I am away from my pc from r lunch and will post when it return
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Ok. This looks like an conversion issue.
Kyle AbrahamsSenior .Net Developer

Commented:
I would try to run the stored proc manually from SQL, passing in the stored procs.  That should return more details of the error message.

If it doesn't then you have to copy out the stored procedure, declare the parameters, and run it until it fails.

eg:
create procedure MySp
@Var1 varchar(100)
as 
select @var1

Open in new window


instead of exec mySp 'Test'  I would have

declare @Var1 varchar(100) = 'Test'

select @Var1

Open in new window

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Pls send the stored procedure code and how you can calling it. Thnx
Larry Bristersr. Developer

Author

Commented:
Pawan... a lot of code here...

But here you go.
Beat I mind... I inherited the VB and will change to "Using"

VB CODE
Dim objConn2 As SqlConnection
 objConn2 = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("strConn1"))
 Dim oCom2 As SqlCommand
 oCom2 = New SqlCommand
 oCom2.Connection = objConn2
 oCom2.CommandText = "ewInsertPaymentsV4"
 oCom2.CommandType = CommandType.StoredProcedure
 oCom2.Parameters.AddWithValue("@SalesSiteID", CInt(hdnSalesSiteID.Value))
 oCom2.Parameters.AddWithValue("@BusinsessID", CInt(hdnBusinessID.Value))
 oCom2.Parameters.AddWithValue("@Amount", hdnAmount.Value.Replace("$", ""))
 oCom2.Parameters.AddWithValue("@Currency", hdnCurrency.Value)

 If EverywareFee <> "0" Then ' Charge a Everyware Fee if there is one instead of the normal fee
     oCom2.Parameters.AddWithValue("@ApplicationFee", FindEverywareFee)
 Else
     oCom2.Parameters.AddWithValue("@ApplicationFee", FindFee)
 End If
 oCom2.Parameters.AddWithValue("@PhoneNumber", custcellphone)
 oCom2.Parameters.AddWithValue("@CardNumber", txtCardNumber.Value.ToString())
 oCom2.Parameters.AddWithValue("@CardName", inpcardname.Value.ToString())
 oCom2.Parameters.AddWithValue("@ExpirationYear", txtCardExpireDate.Value.ToString().Substring(2, 2))
 oCom2.Parameters.AddWithValue("@ExpirationMonth", txtCardExpireDate.Value.ToString().Substring(0, 2))
 oCom2.Parameters.AddWithValue("@AddressCountry", hdnCountryCode.Value.ToString())
 oCom2.Parameters.AddWithValue("@AddressLine1", hdnStreet1.Value.ToString())
 oCom2.Parameters.AddWithValue("@AddressLine2", hdnStreet2.Value.ToString())
 oCom2.Parameters.AddWithValue("@AddressCity", hdnCity.Value.ToString())
 oCom2.Parameters.AddWithValue("@AddressState", hdnStateCode.Value.ToString())
 oCom2.Parameters.AddWithValue("@AddressZip", hdnZipCode.Value.ToString())
 oCom2.Parameters.AddWithValue("@CountryID", hdnCountryCode.Value.ToString())
 oCom2.Parameters.AddWithValue("@CVC", txtCardCVS.Value.ToString())
 If ErrorMessage = "" Then
     oCom2.Parameters.AddWithValue("@Capture", "1")
 Else
     oCom2.Parameters.AddWithValue("@Capture", "0")
 End If

 oCom2.Parameters.AddWithValue("@Description", hdnDescription.Value.ToString())
 If ErrorMessage = "" Then
     oCom2.Parameters.AddWithValue("@TransactionStatus", "succeeded")
 Else
     oCom2.Parameters.AddWithValue("@TransactionStatus", "Failed")
 End If

 oCom2.Parameters.AddWithValue("@FailureCode", "")
 oCom2.Parameters.AddWithValue("@FailureMessage", ErrorMessage)
 oCom2.Parameters.AddWithValue("@ReceiptEmail", hdnEmail.Value.ToString())
 oCom2.Parameters.AddWithValue("@Refunded", "0")
 oCom2.Parameters.AddWithValue("@AmountRefunded", "0")
 oCom2.Parameters.AddWithValue("@InvoiceID", TransactionID)
 oCom2.Parameters.AddWithValue("@ReceiptNumber", hdnOrderNumber.Value.ToString())
 oCom2.Parameters.AddWithValue("@StatementDescription", BusinessBankDesc)
 oCom2.Parameters.AddWithValue("@Live", LiveorTest)
 oCom2.Parameters.AddWithValue("@UserID", Session("UserID"))
 oCom2.Parameters.AddWithValue("@OrderNumber", hdnOrderNumber.Value.ToString())
 oCom2.Parameters.AddWithValue("@SalesRep", hdnSalesRep.Value.ToString())
 oCom2.Parameters.AddWithValue("@Merchant", MerchantGateway)
 oCom2.Parameters.AddWithValue("@PaymentGatewayID", PaymentGatewayID)
 oCom2.Parameters.AddWithValue("@CardType", CardType)
 oCom2.Parameters.AddWithValue("@IsAuthorized", "0")
 oCom2.Parameters.AddWithValue("@PaymentSMSTextID", CInt(hdnPaymentSMSTextID.Value))
 oCom2.Parameters.Add("@IndividualID", SqlDbType.Int)
 oCom2.Parameters("@IndividualID").Direction = ParameterDirection.Output
 objConn2.Open()
 oCom2.ExecuteNonQuery()

 Dim IndividualID As String
 IndividualID = oCom2.Parameters("@IndividualID").Value.ToString()


 objConn2.Close()
 objConn2.Dispose()
 oCom2.Dispose()

Open in new window


SQL Server Procedure
ALTER PROCEDURE [dbo].[ewInsertPaymentsV4]
    @SalesSiteID BIGINT ,
    @BusinsessID BIGINT ,
    @Amount NVARCHAR(500) ,
    @Currency NVARCHAR(200) ,
    @ApplicationFee NVARCHAR(500) ,
    @CardNumber NVARCHAR(500) ,
    @CardName NVARCHAR(500) ,
    @ExpirationYear NVARCHAR(100) ,
    @ExpirationMonth NVARCHAR(100) ,
    @AddressCountry NVARCHAR(100) ,
    @AddressLine1 NVARCHAR(MAX) ,
    @AddressLine2 NVARCHAR(MAX) ,
    @AddressCity NVARCHAR(200) ,
    @AddressState NVARCHAR(200) ,
    @AddressZip NVARCHAR(200) ,
    @CVC NVARCHAR(300) ,
    @Capture BIT ,
    @Description NVARCHAR(500) ,
    @TransactionStatus NVARCHAR(300) ,
    @FailureCode NVARCHAR(300) = NULL ,
    @FailureMessage NVARCHAR(MAX) = NULL ,
    @ReceiptEmail NVARCHAR(300) = NULL ,
    @Refunded BIT = NULL ,
    @AmountRefunded NVARCHAR(500) = NULL ,
    @InvoiceID NVARCHAR(500) = NULL ,
    @ReceiptNumber NVARCHAR(500) = NULL ,
    @StatementDescription NVARCHAR(500) = NULL ,
    @Live BIT = 0 ,
    @OrderNumber NVARCHAR(500) = NULL ,
    @UserID INT = 0 ,
    @SalesRep NVARCHAR(100) = NULL ,
    @SalesRepID NVARCHAR(100) = '0' ,
    @CommissionValue FLOAT = NULL ,
    @PhoneNumber NVARCHAR(100) = NULL ,
    @ExternalID NVARCHAR(100) = NULL ,
    @CountryID INT = 224 ,
    @Merchant NVARCHAR(500) = 'Authorize' ,
    @IndividualID NVARCHAR(100) = '0' OUTPUT ,
    @Extras NVARCHAR(MAX) = NULL ,
    @PaymentGatewayID NVARCHAR(100) = '0' ,
    @IsAuthorized BIT = 0 ,
    @CardType VARCHAR(200) = NULL ,
    @PaymentSMSTextID INT = NULL
AS
    DECLARE @PaymentID BIGINT;
    BEGIN

        IF LEN(@PhoneNumber) < 6
            BEGIN
                SET @PhoneNumber = NULL;
            END;

        SET @AddressCountry = (   SELECT TOP 1 CountryCode
                                  FROM   dbo.Countries
                                  WHERE  CountryID = @CountryID );

    END;
    SET @AddressState = (   SELECT TOP 1 StateCode
                            FROM   States
                            WHERE  StateID = @AddressState
                                   AND CountryID = @CountryID );

    IF @ReceiptEmail IS NULL
        BEGIN
            SET @ReceiptEmail = '';
        END;

    --If Null see if we can find the IndividualID on a email address
    IF @IndividualID = '0'
       AND LEN(@ReceiptEmail) > 3
        BEGIN
            SET @IndividualID = (   SELECT MAX(a.IndividualID)
                                    FROM   IndividualEmails a
                                           JOIN ewSalesSitesToIndividuals b ON a.IndividualID = b.IndividualID
                                    WHERE  a.EmailAddress = @ReceiptEmail
                                           AND b.SalesSiteID = @SalesSiteID );
        END;

    IF @IndividualID IS NULL
        BEGIN
            SET @IndividualID = '0';
        END;





    DECLARE @CommissionAmount FLOAT = 0.00;
    IF ISNULL(@CommissionValue, 0.00) >= 1
        BEGIN
            SET @CommissionValue = @CommissionValue / 100;
        END;

    IF ISNULL(@CommissionValue, 0.00) > 0
        BEGIN
            SET @CommissionAmount = @Amount * @CommissionValue;
        END;

    INSERT INTO ewPayments ( SalesSiteID ,
                             BusinessID ,
                             Amount ,
                             Currency ,
                             ApplicationFee ,
                             CardNumber ,
                             CardName ,
                             ExpirationYear ,
                             ExpirationMonth ,
                             AddressCountry ,
                             AddressLine1 ,
                             AddressLine2 ,
                             AddressCity ,
                             AddressState ,
                             AddressZip ,
                             CVC ,
                             Capture ,
                             [Description] ,
                             StatementDescription ,
                             TransactionStatus ,
                             FailureCode ,
                             FailureMessage ,
                             ReceiptEmail ,
                             Refunded ,
                             AmountRefunded ,
                             InvoiceID ,
                             ReceiptNumber ,
                             DateAdded ,
                             Live ,
                             OrderNumber ,
                             UserID ,
                             SalesRep ,
                             ExternalID ,
                             Merchant ,
                             CommissionValue ,
                             CommissionAmount ,
                             SalesRepID ,
                             IndividualID ,
                             PaymentGatewayID ,
                             IsAuthorized ,
                             CardType )
    VALUES ( @SalesSiteID ,
             @BusinsessID ,
             @Amount ,
             @Currency ,
             @ApplicationFee ,
             RIGHT(@CardNumber, 4),
             @CardName ,
             @ExpirationYear ,
             @ExpirationMonth ,
             @AddressCountry ,
             @AddressLine1 ,
             @AddressLine2 ,
             @AddressCity ,
             @AddressState ,
             @AddressZip ,
             @CVC ,
             @Capture ,
             @Description ,
             @StatementDescription ,
             @TransactionStatus ,
             ISNULL(@FailureCode, ''),
             ISNULL(@FailureMessage, ''),
             ISNULL(@ReceiptEmail, ''),
             ISNULL(@Refunded, ''),
             ISNULL(@AmountRefunded, ''),
             ISNULL(@InvoiceID, ''),
             ISNULL(@ReceiptNumber, ''),
             GETDATE(),
             @Live ,
             ISNULL(@OrderNumber, ''),
             @UserID ,
             ISNULL(@SalesRep, ''),
             ISNULL(@ExternalID, ''),
             @Merchant ,
             @CommissionValue ,
             CASE WHEN ISNULL(@CommissionValue, 0.00) > 0 THEN @CommissionAmount ELSE NULL END,
             @SalesRepID ,
             @IndividualID ,
             @PaymentGatewayID ,
             @IsAuthorized ,
             @CardType );

    SET @PaymentID = @@IDENTITY;

	--Added next 4 lines to handle SLS Payment By Text
	IF @PaymentSMSTextID IS NOT NULL AND @PaymentID IS NOT NULL
	BEGIN
	    UPDATE dbo.ewPaymentsSMSTextPending
		SET PaymentID = @PaymentID
		WHERE PaymentSMSTextID = @PaymentSMSTextID
	END


    IF LEN(@Extras) > 2
        BEGIN
            -- Add any extras to the order and track it
            CREATE TABLE #TempExtras
                (
                    BusinessLocationServiceID BIGINT
                );

            INSERT INTO #TempExtras ( BusinessLocationServiceID )
                        SELECT *
                        FROM   ParseList(@Extras, ',');



            INSERT INTO ewPaymentsItems ( PaymentID ,
                                          BusinessLocationServiceID ,
                                          Cost ,
                                          DateAdded ,
                                          Taxable )
                        SELECT @PaymentID ,
                               a.BusinessLocationServiceID ,
                               Cost ,
                               GETDATE() ,
                               b.Taxable
                        FROM   #TempExtras a
                               JOIN ewBusinessLocationServices b ON a.BusinessLocationServiceID = b.BusinessLocationServiceID;

            DROP TABLE #TempExtras;
        END; -- Extras select * from ewBusinessLocationServices
    --Track the activity 


    DECLARE @TransactionStatusActivity VARCHAR(50);

    IF ISNULL(@TransactionStatus, '') = ''
        BEGIN
            SET @TransactionStatusActivity = 'Charge';
        END;

    IF @TransactionStatus = 'succeeded'
        BEGIN
            SET @TransactionStatusActivity = 'Charge';
        END;

    IF @TransactionStatus = 'Authorized'
        BEGIN
            SET @TransactionStatusActivity = 'Authorized';
        END;

    IF @TransactionStatus = 'Failed'
        BEGIN
            SET @TransactionStatusActivity = 'Failed';
        END;

    INSERT INTO ewPaymentsActivity ( PaymentID ,
                                     Amount ,
                                     DateAdded ,
                                     UserID ,
                                     PaymentType )
    VALUES ( @PaymentID, @Amount, GETDATE(), @UserID, @TransactionStatusActivity );

    IF @IndividualID = '0'
        BEGIN
            --Insert Customer
            DECLARE @OriginID BIGINT;
            DECLARE @FirstName VARCHAR(100);
            DECLARE @LastName VARCHAR(100);


            SET @FirstName = LEFT(@CardName, CHARINDEX(' ', @CardName));
            SET @LastName = SUBSTRING(@CardName, CHARINDEX(' ', @CardName) + 1, LEN(@CardName) - ( CHARINDEX(' ', @CardName) - 1 ));

            SET @OriginID = (   SELECT TOP 1 OriginID
                                FROM   Origins
                                WHERE  SalesSiteID = @SalesSiteID );

            EXEC ewInsertCustomerpayments @OriginID = @OriginID ,
                                          @FirstName = @FirstName ,
                                          @MiddleName = '' ,
                                          @LastName = @LastName ,
                                          @Address1 = @AddressLine1 ,
                                          @Address2 = @AddressLine2 ,
                                          @City = @AddressCity ,
                                          @StateCode = @AddressState ,
                                          @ZipCode = @AddressZip ,
                                          @CountryID = @CountryID ,
                                          @HomePhone = @PhoneNumber ,
                                          @CellPhone = @PhoneNumber ,
                                          @Email = @ReceiptEmail ,
                                          @UserID = @UserID ,
                                          @ExternalID = @ExternalID ,
                                          @CustomField1 = @SalesRep ,
                                          @CustomField2 = @OrderNumber ,
                                          @IndividualID = @IndividualID OUTPUT;

            UPDATE ewPayments
            SET    IndividualID = @IndividualID
            WHERE  PaymentID = @PaymentID;

        END; -- Inserting new customer



    ------------------------------------------------------------------------------        
    -- add a disposition record        
    ------------------------------------------------------------------------------        
    DECLARE @DispositionTypeID INT;
    DECLARE @CallBack DATETIME;
    DECLARE @ContactTypeID INT;
    DECLARE @ContactDate DATETIME;
    DECLARE @IndividualExportsMasterID INT;
    DECLARE @Comments VARCHAR(300) = 'Total for ' + @Description + ' - $' + CONVERT(VARCHAR(40), @Amount);
    SET @IndividualExportsMasterID = 0;

    SET @CallBack = NULL;
    --Set @Comments = ''        
    SET @ContactTypeID = 0;
    SET @ContactDate = GETDATE();
    --SET @DispositionTypeID = (SELECT TOP 1 DispositionTypeID FROM dbo.DispositionTypes WHERE Code = 'Charge');    


    IF @TransactionStatus = 'succeeded'
        BEGIN
            SET @DispositionTypeID = (   SELECT TOP 1 DispositionTypeID
                                         FROM   dbo.DispositionTypes
                                         WHERE  Code = 'Charge' );
        END;

    IF @TransactionStatus = 'Authorized'
        BEGIN
            SET @DispositionTypeID = (   SELECT TOP 1 DispositionTypeID
                                         FROM   dbo.DispositionTypes
                                         WHERE  Code = 'CAUTH' );
        END;

    IF @TransactionStatus = 'Failed'
        BEGIN
            SET @DispositionTypeID = (   SELECT TOP 1 DispositionTypeID
                                         FROM   dbo.DispositionTypes
                                         WHERE  Code = 'FAILED' );
        END;




    IF @IndividualID = '0'
        BEGIN
            SET @IndividualID = (   SELECT MAX(IndividualID)
                                    FROM   ewPayments
                                    WHERE  InvoiceID = @InvoiceID );
        END;
    --Disposition for Credit Card Charge 
    EXEC dbo.InsertIndividualDisposition @IndividualID = @IndividualID ,
                                         @IndividualExportsMasterID = @IndividualExportsMasterID ,
                                         @OriginID = @OriginID ,
                                         @DispositionTypeID = @DispositionTypeID ,
                                         @ContactDate = @ContactDate ,
                                         @ContactTypeID = @ContactTypeID ,
                                         @CallBack = @CallBack ,
                                         @Comments = @Comments ,
                                         @UserID = @UserID ,
                                         @AgentID = @UserID;

    SET @IndividualID = (   SELECT IndividualID
                            FROM   ewPayments WITH ( NOLOCK )
                            WHERE  PaymentID = @PaymentID );

    SELECT @IndividualID AS IndividualID ,
           @PaymentID AS PaymentID;
    RETURN;

Open in new window

Database Expert
Awarded 2016
Top Expert 2016
Commented:
Please try -- updated code for Int parameters.

Dim objConn2 As SqlConnection
 objConn2 = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("strConn1"))
 Dim oCom2 As SqlCommand
 oCom2 = New SqlCommand
 oCom2.Connection = objConn2
 oCom2.CommandText = "ewInsertPaymentsV4"
 oCom2.CommandType = CommandType.StoredProcedure
 oCom2.Parameters.AddWithValue("@SalesSiteID", CInt(hdnSalesSiteID.Value))
 oCom2.Parameters.AddWithValue("@BusinsessID", CInt(hdnBusinessID.Value))
 oCom2.Parameters.AddWithValue("@Amount", hdnAmount.Value.Replace("$", ""))
 oCom2.Parameters.AddWithValue("@Currency", hdnCurrency.Value)

 If EverywareFee <> "0" Then ' Charge a Everyware Fee if there is one instead of the normal fee
     oCom2.Parameters.AddWithValue("@ApplicationFee", FindEverywareFee)
 Else
     oCom2.Parameters.AddWithValue("@ApplicationFee", FindFee)
 End If
 oCom2.Parameters.AddWithValue("@PhoneNumber", custcellphone)
 oCom2.Parameters.AddWithValue("@CardNumber", txtCardNumber.Value.ToString())
 oCom2.Parameters.AddWithValue("@CardName", inpcardname.Value.ToString())
 oCom2.Parameters.AddWithValue("@ExpirationYear", txtCardExpireDate.Value.ToString().Substring(2, 2))
 oCom2.Parameters.AddWithValue("@ExpirationMonth", txtCardExpireDate.Value.ToString().Substring(0, 2))
 oCom2.Parameters.AddWithValue("@AddressCountry", hdnCountryCode.Value.ToString())
 oCom2.Parameters.AddWithValue("@AddressLine1", hdnStreet1.Value.ToString())
 oCom2.Parameters.AddWithValue("@AddressLine2", hdnStreet2.Value.ToString())
 oCom2.Parameters.AddWithValue("@AddressCity", hdnCity.Value.ToString())
 oCom2.Parameters.AddWithValue("@AddressState", hdnStateCode.Value.ToString())
 oCom2.Parameters.AddWithValue("@AddressZip", hdnZipCode.Value.ToString())
 oCom2.Parameters.AddWithValue("@CountryID", CInt(hdnCountryCode.Value.ToString()))
 oCom2.Parameters.AddWithValue("@CVC", txtCardCVS.Value.ToString())
 If ErrorMessage = "" Then
     oCom2.Parameters.AddWithValue("@Capture", "1")
 Else
     oCom2.Parameters.AddWithValue("@Capture", "0")
 End If

 oCom2.Parameters.AddWithValue("@Description", hdnDescription.Value.ToString())
 If ErrorMessage = "" Then
     oCom2.Parameters.AddWithValue("@TransactionStatus", "succeeded")
 Else
     oCom2.Parameters.AddWithValue("@TransactionStatus", "Failed")
 End If

 oCom2.Parameters.AddWithValue("@FailureCode", "")
 oCom2.Parameters.AddWithValue("@FailureMessage", ErrorMessage)
 oCom2.Parameters.AddWithValue("@ReceiptEmail", hdnEmail.Value.ToString())
 oCom2.Parameters.AddWithValue("@Refunded", "0")
 oCom2.Parameters.AddWithValue("@AmountRefunded", "0")
 oCom2.Parameters.AddWithValue("@InvoiceID", TransactionID)
 oCom2.Parameters.AddWithValue("@ReceiptNumber", hdnOrderNumber.Value.ToString())
 oCom2.Parameters.AddWithValue("@StatementDescription", BusinessBankDesc)
 oCom2.Parameters.AddWithValue("@Live", LiveorTest)
 oCom2.Parameters.AddWithValue("@UserID", CInt(Session("UserID")))
 oCom2.Parameters.AddWithValue("@OrderNumber", hdnOrderNumber.Value.ToString())
 oCom2.Parameters.AddWithValue("@SalesRep", hdnSalesRep.Value.ToString())
 oCom2.Parameters.AddWithValue("@Merchant", MerchantGateway)
 oCom2.Parameters.AddWithValue("@PaymentGatewayID", PaymentGatewayID)
 oCom2.Parameters.AddWithValue("@CardType", CardType)
 oCom2.Parameters.AddWithValue("@IsAuthorized", "0")
 oCom2.Parameters.AddWithValue("@PaymentSMSTextID", CInt(hdnPaymentSMSTextID.Value))
 oCom2.Parameters.Add("@IndividualID", SqlDbType.Int)
 oCom2.Parameters("@IndividualID").Direction = ParameterDirection.Output
 objConn2.Open()
 oCom2.ExecuteNonQuery()

 Dim IndividualID As String
 IndividualID = oCom2.Parameters("@IndividualID").Value.ToString()


 objConn2.Close()
 objConn2.Dispose()
 oCom2.Dispose()

Open in new window

Larry Bristersr. Developer

Author

Commented:
Pointed me in the right direction

In addition to your fix I was passing in Country and state CODE
Instead of Integer ID's

Thanks
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
great.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial