How to fix error converting data type varchar to numeric

Question

I'm customizing a legacy ASP.NET MVC application that uses both raw SQL and models. I have some data to be committed that has two decimal places for example 4,615.38, 11.51. When I attempt to commit data im getting an error

Error converting datatype varchar to numeric

I need help on how to properly define my table and stored procedure. Should I use any casting in table definition or LEFT function?

In TaxTableController.cs i have :

                Models.TaxTable.Zimra zimra = new Models.TaxTable.Zimra();

            zimra.TableName = Helpers.SanitiseInput(Convert.ToString(formcollection["TableName"]));
            zimra.TierName = Helpers.SanitiseInput(Convert.ToString(formcollection["TierName"]));
            zimra.MinSalary = Convert.ToDouble(formcollection["MinSalary"]);
            zimra.MaxSalary = Convert.ToDouble(formcollection["MaxSalary"]);

            zimra.CreatedOn = DateTime.Now;
            zimra.CreatedBy = Convert.ToString(Session["UserId"]);

            if (ModelState.IsValid)
            {

                using (SqlConnection conn = new SqlConnection(Helpers.DatabaseConnect))
                {

                    SqlCommand cmd = new SqlCommand("SaveZimraTable", conn);
                    cmd.CommandType = CommandType.StoredProcedure;


                    cmd.Parameters.AddWithValue("@TableName", zimra.TableName);
                    cmd.Parameters.AddWithValue("@TierName", zimra.TierName);
                    cmd.Parameters.AddWithValue("@MaxSalary", zimra.MaxSalary);
                    cmd.Parameters.AddWithValue("@MinSalary", zimra.MinSalary);

                    cmd.Parameters.AddWithValue("@CreatedBy", zimra.CreatedBy);
                    cmd.Parameters.AddWithValue("@CreatedOn", zimra.CreatedOn);

My table definition is as below (using Script Table As--Create) :

    [Id] [int] IDENTITY(1,1) NOT NULL,
    [MinSalary] [decimal](18, 2) NOT NULL,
    [MaxSalary] [decimal](18, 2) NOT NULL,
    [CreatedBy] [varchar](50) NULL,
    [TableName] [varchar](50) NULL,
    [TierName] [varchar](50) NOT NULL,
    [CreatedOn] [datetime] NULL,
 CONSTRAINT [PK__Zimra__3214EC07397C51AA] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

And the stored procedure is as below:

CREATE PROCEDURE [dbo].[SaveZimraTable] 
    @TableName varchar(50),
    @TierName varchar(50),
    @MinSalary decimal(18,2),
    @MaxSalary decimal(18,2),
    @CreatedBy varchar(50),
    @CreatedOn datetime

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    INSERT INTO Zimra VALUES (@TableName,@TierName,@MinSalary,@MaxSalary,@CreatedBy,@CreatedOn)
END
GO

You need to set SQL parameter like this cmd.Parameters.Add(new SqlParameter { ParameterName = "", Value = "", DbType = DbType.String }) — Adnan Ahmed Ansari
– Adnan Ahmed Ansari, Commented Apr 30, 2019 at 10:45
See that first comment in your stored procedure? That is something you should do - go add that code. Comments in your code should reflect reality. — SMor
– SMor, Commented Apr 30, 2019 at 11:45

Martin · Accepted Answer · 2019-04-30 15:28:46Z

2

Always specify the columns list in an insert statement. Otherwise, you must specify values for all the columns (except identity columns) and you must do it in the correct order - which is clearly not the case in your statement. Also, you run the risk of breaking the statement if you change the table structure - either by adding a column or switching columns order.

INSERT INTO Zimra (TableName, TierName, MinSalary, MaxSalary, CreatedBy, CreatedOn)
VALUES (@TableName,@TierName,@MinSalary,@MaxSalary,@CreatedBy,@CreatedOn)

Also, as noted in the comments, do not use AddWithValues to add parameters to your command object - instead, use Add:

cmd.Parameters.Add("@TableName", SqlDbType.VarChar).Value = zimra.TableName;
// do the same for all parameters.

edited Apr 30, 2019 at 15:28

Martin

16.5k1 gold badge33 silver badges51 bronze badges

answered Apr 30, 2019 at 11:02

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Golide Over a year ago

i have amended to using Add as : ```cmd.Parameters.Add(new SqlParameter { ParameterName = "@MaxSalary", Value = "zimra.MaxSalary", DbType = DbType.Double });

Zohar Peled Over a year ago

You still need to fix the insert statement. No need to explicitly create the parameter, the Add method does that for you with a shorter code.

Golide Over a year ago

i have already refactored the table definition to reflect the column ordering in the Stored Procedure if thats what you mean.I am bit stuck , What do you mean by "No need to explicitly create the parameter"

Zohar Peled Over a year ago

You don't need to refactor the table, you need to fix the insert statement. I've noticed a small copy-paste typo in my answer and fixed it. Just use cmd.Parameters.Add(name, type).Value = value

Collectives™ on Stack Overflow

How to fix error converting data type varchar to numeric

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related