Input string was not in a correct format error using C#

Question

I am trying to insert some data into the database but getting the error mentioned above. The data type for this field in SQL Server is numeric(4,2). I have tried all kind of solution but cannot get it to work. here is my code:

 TextBox Hours = row.FindControl("Hrs") as TextBox; 

cmd.CommandText = "insert into myTable (ID, HOURS) values (@ID, @HOURS) ";
 cmd.Parameters.Add("@ID", SqlDbType.VarChar).Value = ID.Text;
 cmd.Parameters.Add("@HOURS", SqlDbType.VarChar).Value = Convert.ToInt32(Hours.ToString());

i have also tried to use decimal but not sure how to fix it. thanks

Calling ToString() on a TextBox will not give you a useful string — SLaks
– SLaks, Commented Feb 5, 2014 at 20:38

Grant Winney · Accepted Answer · 2014-02-05 20:44:00Z

2

You said the type is numeric, but you're trying to add varchar parameters.

Try changing how you're adding parameters:

cmd.Parameters.AddWithValue("@ID", Convert.ToInt32(ID.Text));
cmd.Parameters.AddWithValue("@HOURS", Convert.ToInt32(Hours.Text));

edited Feb 5, 2014 at 20:44

answered Feb 5, 2014 at 20:38

Grant Winney

66.6k13 gold badges120 silver badges167 bronze badges

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

Comments

Steve · Accepted Answer · 2014-02-05 21:39:19Z

2

If you want to pass an integer to your parameter, you should convert the Text property of the TextBox, not the instance variable. Calling ToString on the Hours TextBox return System.Windows.Forms.TextBox and of course this cannot be converted to an integer.

Of course the parameter type should be SqlDbType.Int not a VarChar

TextBox Hours = row.FindControl("Hrs") as TextBox; 
cmd.CommandText = "insert into myTable (ID, HOURS) values (@ID, @HOURS) ";
cmd.Parameters.Add("@ID", SqlDbType.VarChar).Value = ID.Text;
cmd.Parameters.Add("@HOURS", SqlDbType.Int).Value = Convert.ToInt32(Hours.Text);

If the field HOURS expects a decimal value (4,2) then you need to create a decimal type parameter. Also I suggest to add some check on the input box

decimal hourValue;
if(!decimal.TryParse(Hours.Text, out hourValue)    
   // Message to the user that he/she has typed an invalid number
else
{
    TextBox Hours = row.FindControl("Hrs") as TextBox; 
    cmd.CommandText = "insert into myTable (ID, HOURS) values (@ID, @HOURS) ";
    cmd.Parameters.Add("@ID", SqlDbType.VarChar).Value = ID.Text;
    cmd.Parameters.Add("@HOURS", SqlDbType.Decimal).Value = hourValue;
}

edited Feb 5, 2014 at 21:39

answered Feb 5, 2014 at 20:40

Steve

217k22 gold badges242 silver badges296 bronze badges

6 Comments

Steve Over a year ago

What it the value contained in the Hours TextBox?

moe Over a year ago

okay the data type in the back end is numeric(4,2) and i am typing like 25 or 45

moe Over a year ago

i am doing a for loop in the gridview so it does not like when the rest of the rows are null or blank. if i fill out all the rows in the gridview then it works. how can i overcome that?

Steve Over a year ago

It depends. Do you want null values or not? If not then inform the user, if yes (and the database table accepts null values) pass DBNull.Value as value for the parameter

moe Over a year ago

how do i pass the dbnull value for the parameter?

|

Marek · Accepted Answer · 2014-02-06 07:59:55Z

1

Does this work for you?

object Hours = DBNull.Value;
Hours = row.FindControl("Hrs") as TextBox;     
cmd.CommandText = "insert into myTable (ID, HOURS) values (@ID, @HOURS) "; 
cmd.Parameters.Add("@ID", SqlDbType.VarChar).Value = ID.Text;
cmd.Parameters.Add("@HOURS", SqlDbType.Decimal).Value = Decimal.Parse((Hours.Text));

edited Feb 6, 2014 at 7:59

answered Feb 5, 2014 at 20:39

Marek

3,60517 gold badges76 silver badges125 bronze badges

6 Comments

moe Over a year ago

i am still getting this error: Input string was not in a correct format.

Marek Over a year ago

@moe On which line do you get that?

moe Over a year ago

the last line where you say: Decimal.Parse((Hours.Text)

Marek Over a year ago

Debug that line and tell me what value is in Hours.Text

moe Over a year ago

i am doing a for loop in the gridview so it does not like when the rest of the rows are null. if i fill out all the rows in the gridview then it works. how can i overcome that?

|

moe · Accepted Answer · 2014-02-05 21:52:09Z

0

okay i found the solution.

  if (string.IsNullOrEmpty(Hours.Text))
                    cmd.Parameters.AddWithValue("@HOURS", DBNull.Value);
                else
                    cmd.Parameters.AddWithValue("@HOURS", decimal.Parse(Hours.Text));

answered Feb 5, 2014 at 21:52

moe

5,24941 gold badges136 silver badges203 bronze badges

1 Comment

Steve Over a year ago

decimal.Parse will crash your code if the user doesn't type a valid number

Collectives™ on Stack Overflow

Input string was not in a correct format error using C#

4 Answers 4

Comments

6 Comments

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

6 Comments

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related