MS SQL Server execute nonquery error
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?
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?
Last Comment
Pawan Kumar
Can u attach the entire query pls.
ASKER
I am away from my pc from r lunch and will post when it return
Ok. This looks like an conversion issue.
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:
instead of exec mySp 'Test' I would have
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
instead of exec mySp 'Test' I would have
declare @Var1 varchar(100) = 'Test'
select @Var1
Pls send the stored procedure code and how you can calling it. Thnx
ASKER
Pawan... a lot of code here...
But here you go.
Beat I mind... I inherited the VB and will change to "Using"
VB CODE
SQL Server Procedure
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()
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;
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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
In addition to your fix I was passing in Country and state CODE
Instead of Integer ID's
Thanks
great.
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
