CODEWITHSUNDEEP
c#asp.netsql-server-2008
Girish
Girish

Reputation: 35

NULL value not getting inserted

I have a table with few NULL columns.. and I have created a stored procedure.

When I enter values in the controls in the web form, I need to type in values in all the fields or else, I get an error saying:

System.FormatException: Input string was not in a correct format.

on the line:

cmd.Parameters.Add(new SqlParameter("@Mob2", Convert.ToInt32(TextBox5.Text)));

I tried even doing 

cmd.Parameters.Add(new SqlParameter("@Mob2", int.parse(TextBox5.Text)));

But I'm unable to leave it as NULL in the TextBox control..

In the table, the datatype is numeric(10,0).. in the stored procedure I have mentioned as nvarchar(10) and while inserting I have converted like this,

Convert(numeric(10,0),@Mob2)

But it seems to not getting converted..

Upvotes: 1

Views: 1345

Answers (4)

heads5150
heads5150

Reputation: 7443

You're trying to parse a null string to an integer which you can't to you should use either int.TryParse or if you a null value inserted into the database field something along the lines of:

cmd.Parameters.Add(new SqlParameter("@Mob2", 
!string.IsNullOrEmpty(TextBox5.Text.Trim()) ?int.parse(TextBox5.Text) 
: SqlInt32.Null));

which inserts the Sql Int32 null value into the database if TextBox5.Text == null or empty

UPDATE TO ADDRESS USER COMMENTS If you're using numeric(10,0), convert the TextBox5 value to decimal and change your stored procedure from varchar(10) to numeric(10,0) to match the table column data type, then you can use code similar to:

SqlParameter numericParameter = new SqlParameter("NumericValue",SqlDbType.Decimal);
numericParameter.Precision = 10;
numericParameter.Scale = 0;
numericParameter.Value = !string.IsNullOrEmpty(TextBox5.Text.Trim()) ?decimal.parse(TextBox5.Text) 
: SqlInt32.Null);

Upvotes: 0

user1542538
user1542538

Reputation:

make sure mobile number is required field

If not than make sure in your database table mobile number field is unchecked for NULL in your database table. That's way you get this kind of error.

Upvotes: 1

Marc Gravell
Marc Gravell

Reputation: 1062550

Convert.ToInt32(TextBox5.Text) will always fail for empty strings. So check that:

object val = string.IsNullOrEmpty(theString) ? (object)DBNull.Value
                                             : int.Parse(theString);

Upvotes: 3

Khurshid
Khurshid

Reputation: 944

Check value of TextBox5.Text, problem related with textbox value

Upvotes: 1

Related Questions