SmashAndGrab
asked on
Error Converting data type varchar to numeric. .NET application
Review the parameters and see if they match with the giving .NET data types. If they match then must be one of the fields that are not totally numeric (depending on the regional settings a comma or a point can be used for decimal so check also the decimal formats).
ASKER
Code:
cmdUpdate.Parameters.Add("@Reg", SqlDbType.VarChar, 8).Value = tbReg.Text.ToUpper();
cmdUpdate.Parameters.Add("@Driver", SqlDbType.VarChar, 10).Value = ddlDriver.SelectedValue;
cmdUpdate.Parameters.Add("@DriverName", SqlDbType.VarChar, 50).Value = tbDriverName.Text;
cmdUpdate.Parameters.Add("@Remarks", SqlDbType.VarChar, 25).Value = tbRemarks.Text.ToUpper();
cmdUpdate.Parameters.Add("@Trailer", SqlDbType.VarChar, 8).Value = tbTrailer.Text;
cmdUpdate.Parameters.Add("@Bay", SqlDbType.Int).Value = Convert.ToInt32(ddlBay.SelectedValue);
cmdUpdate.Parameters.Add("@ID", SqlDbType.Int).Value = Convert.ToInt32(hidID.Value);
cmdUpdate.Parameters.Add("@RegOut", SqlDbType.VarChar, 8).Value = tbRegOut.Text;
cmdUpdate.Parameters.Add("@Inbound", SqlDbType.Bit).Value = (0);
if (tbTrailer.Text.Length > 0)
{
cmdUpdate.Parameters.Add("@Change", SqlDbType.Bit).Value = 1;
cmdUpdate.Parameters.Add("@NewTrailer", SqlDbType.VarChar, 8).Value = tbTrailer.Text;
cmdUpdate.Parameters.Add("@Vendor", SqlDbType.VarChar, 8).Value = ddlDriver.SelectedValue;
cmdUpdate.Parameters.Add("@Description", SqlDbType.VarChar, 50).Value = ddlDriver.SelectedItem.Text;
}
ASKER
Stored Proc:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[UpdateChanges](@ID int,
@Reg varchar(8),
@Driver varchar(10),
@DriverName varchar(50),
@Remarks varchar(25),
@Trailer varchar(8),
@Bay int,
@RegOut varchar(8),
@Inbound bit,
@Change bit = NULL,
@NewTrailer varchar(15) = NULL,
@Vendor varchar(8) = NULL,
@Description varchar(50) = NULL)
AS
UPDATE FF_tTransportBookingIn
SET RegistrationIn = @Reg, Contracter = @Driver, Remarks = @Remarks, Trailer = @Trailer, Bay = @Bay, RegistrationOut = @RegOut, InboundGoods = @Inbound, DriverName = @DriverName
WHERE (ID = @ID)
IF @Change = 1
BEGIN
DECLARE @Count int
SET @Count = (SELECT COUNT(*) AS Expr1 FROM FF_tTrailers WHERE (TrailerID = @NewTrailer))
IF @Count = 0
BEGIN
INSERT INTO FF_tTrailers (TrailerID, Vendor_Code, Description)
VALUES (@NewTrailer, @Vendor, @Description)
END
ELSE IF @Count > 0
BEGIN
UPDATE FF_tTrailers
SET TrailerID = @NewTrailer
WHERE (TrailerID = @Trailer)
END
UPDATE FF_tTransportBookingIn
SET Trailer = @NewTrailer
WHERE (ID = @ID)
END
SET NOCOUNT ON
ASKER
If ID is INT why are you using LIKE and keeping the number between single quotes?
It should be ID = 75568.
It should be ID = 75568.
btw, the maximum value for a short integer is 65536. 75568 > 65536 so you should definitely not using LIKE and single quotes. This will make implicit conversion from string to numeric.
ASKER
Try not to focus on that part as I only did that select query to show you the data of the ID that is causing the issue.
Point taken though :)
Point taken though :)
Ok but I can't see any error. The last screenshot shows that the record has been returned with no errors.
IMO the error is about the data in the form and not with the code. Did you check if all data are valid?
Can you print all parameters before running the ExecuteNonQuery command?
IMO the error is about the data in the form and not with the code. Did you check if all data are valid?
Can you print all parameters before running the ExecuteNonQuery command?
ASKER
Thank you Vitor - I think you are right.
I think the answer is in the debugging of the stored procedure.
How do I debug the SP? When I click DEBUG - it just says "The command completed successfully". I don't have a chance to add all the inputs?
I think the answer is in the debugging of the stored procedure.
How do I debug the SP? When I click DEBUG - it just says "The command completed successfully". I don't have a chance to add all the inputs?
Debug the .net code and watch the variables values. Hopefully you'll see the bad data.
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Other thing that I just saw now. You have @Vendor = '' and empty strings can't be converted to numeric. Better use NULL or '0':
INSERT INTO FF_tTrailers (TrailerID, Vendor_Code, Description)
VALUES (@NewTrailer, CASE
WHEN @Vendor='' THEN NULL
ELSE @Vendor END, @Description)
Do you need those double quotes inside the parameters value? That could be an issue alone!
You have to debug the stored procedure code to pinpoint the exact statement. You need to get the CREATE code for the sp and to comment the CREATE with parameters part until AS. Then use a declare statement with all the parameters and initialize them with the values from the procedure code. Run the script that way and then double click on the error message in the Massage tab where the results should show. That will jump you to the exact statement in the code that generated the error and you will see that you are trying to insert into a numeric column a non numeric value. If you have double quotes around the value that is your problem but even if you remove them the value could be non numeric.
Here, execute this script after you replace the columns with your real values:
You have to debug the stored procedure code to pinpoint the exact statement. You need to get the CREATE code for the sp and to comment the CREATE with parameters part until AS. Then use a declare statement with all the parameters and initialize them with the values from the procedure code. Run the script that way and then double click on the error message in the Massage tab where the results should show. That will jump you to the exact statement in the code that generated the error and you will see that you are trying to insert into a numeric column a non numeric value. If you have double quotes around the value that is your problem but even if you remove them the value could be non numeric.
Here, execute this script after you replace the columns with your real values:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* -- comment this line to uncomment the whole section
ALTER PROCEDURE [dbo].[UpdateChanges](
@ID int,
@Reg varchar(8),
@Driver varchar(10),
@DriverName varchar(50),
@Remarks varchar(25),
@Trailer varchar(8),
@Bay int,
@RegOut varchar(8),
@Inbound bit,
@Change bit = NULL,
@NewTrailer varchar(15) = NULL,
@Vendor varchar(8) = NULL,
@Description varchar(50) = NULL)
AS
--*/
--/* -- uncomment this line to comment the whole section
declare
@ID int=75557,
@Reg varchar(8)='value here',
@Driver varchar(10)='value here',
@DriverName varchar(50)='value here',
@Remarks varchar(25)='value here',
@Trailer varchar(8)='value here',
@Bay int=28,
@RegOut varchar(8)='value here',
@Inbound bit=0,
@Change bit = 1,
@NewTrailer varchar(15)='value here',
@Vendor varchar(8)='value here',
@Description varchar(50)='value here'
--*/
UPDATE FF_tTransportBookingIn
SET RegistrationIn = @Reg, Contracter = @Driver, Remarks = @Remarks, Trailer = @Trailer, Bay = @Bay, RegistrationOut = @RegOut, InboundGoods = @Inbound, DriverName = @DriverName
WHERE (ID = @ID)
IF @Change = 1
BEGIN
DECLARE @Count int
SET @Count = (SELECT COUNT(*) AS Expr1 FROM FF_tTrailers WHERE (TrailerID = @NewTrailer))
IF @Count = 0
BEGIN
INSERT INTO FF_tTrailers (TrailerID, Vendor_Code, Description)
VALUES (@NewTrailer, @Vendor, @Description)
END
ELSE IF @Count > 0
BEGIN
UPDATE FF_tTrailers
SET TrailerID = @NewTrailer
WHERE (TrailerID = @Trailer)
END
UPDATE FF_tTransportBookingIn
SET Trailer = @NewTrailer
WHERE (ID = @ID)
END
SET NOCOUNT ON
ASKER
Perfect! thank you guys!
You can test it with calling the UpdateChanges procedure manually in Management Studio by giving parameters as .net code would done.