Solved

Error Converting data type varchar to numeric. .NET application

Posted on 2016-09-26
19
151 Views
Last Modified: 2016-09-26
Hi,

I am getting the above error when calling a stored procedure.

The issue is, how do I find out which one is causing the problem?

Or is it happening in the Stored procedure?


Error
0
Comment
Question by:SmashAndGrab
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
19 Comments
 
LVL 12

Expert Comment

by:Máté Farkas
ID: 41815803
Yes, this message comes from stored procedure (from SQL Server side).
You can test it with calling the UpdateChanges procedure manually in Management Studio by giving parameters as .net code would done.
0
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 41815824
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).
0
 

Author Comment

by:SmashAndGrab
ID: 41815829
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;
            }

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:SmashAndGrab
ID: 41815831
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

Open in new window

0
 

Author Comment

by:SmashAndGrab
ID: 41815850
Here's my watching varibles (these are the INTs)

codeImage.PNG
0
 

Author Comment

by:SmashAndGrab
ID: 41815852
Table Definition:

tableDef.PNG
0
 

Author Comment

by:SmashAndGrab
ID: 41815854
The error is happeing when the ID is:  '75568' so here is the current data for that record:

query.PNG
It all looks ok to me!  So I am a little stuck here!
0
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 41815860
If ID is INT why are you using LIKE and keeping the number between single quotes?
It should be ID = 75568.
0
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 41815862
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.
0
 

Author Comment

by:SmashAndGrab
ID: 41815884
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 :)
0
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 41815891
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?
0
 

Author Comment

by:SmashAndGrab
ID: 41815913
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?
0
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 41815928
Debug the .net code and watch the variables values. Hopefully you'll see the bad data.
0
 

Author Comment

by:SmashAndGrab
ID: 41815940
ok.  1 step closer I think!

I found the data in the bad record (1 of them anyway).

I took this data and ran the Stored Procedure with this data..

SQL
However - I am still not exactly sure where the error lies!  ha!  It references line 34 but that doesn't even exist!
0
 
LVL 49

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 41815953
It's line 34 of your stored procedure:
INSERT INTO FF_tTrailers (TrailerID, Vendor_Code, Description)
VALUES (@NewTrailer, @Vendor, @Description)

Open in new window

What's the data type for FF_tTrailers..TrailerID?
0
 
LVL 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 250 total points
ID: 41816065
Yes Vitor is correct. Either TrailerID or Vendor_Code is of type numeric.

And you are passing Nvarchar value to that thats why the conversion error while executing the sp.
0
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 41816071
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)

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41816072
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:
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

Open in new window

0
 

Author Closing Comment

by:SmashAndGrab
ID: 41816166
Perfect!  thank you guys!
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question