Cannot insert null into database vb.net

Question

I have been trying to insert null into database but every time I am getting failed. I tried several codes but nothing is working. When I tried this code:

   If String.IsNullOrEmpty(TextBox2.Text) Then
        TextBox2.Text = DBNull.Value
    Else
        TextBox2.Text = TextBox2.Text

    End If

It gives me an error :

Value of type 'System.DBNull' cannot be converted to 'String'.

And when I tried this:

    If Not String.IsNullOrEmpty(TextBox2.Text) = True Then
        TextBox2 = System.DBNull.Value
    Else
        TextBox2.Text = TextBox2.Text
    End If

It gives me an error:

  Value of type 'System.DBNull' cannot be converted to 'System.Windows.Forms.TextBox'.

Before these code I have added some validations in order to make the text box compulsory to filled by the user.

This is the place where I am getting this error:

    dsNewRow.Item("emp_name") = TextBox1.Text
    dsNewRow.Item("emp_age") = TextBox2.Text  'This is the place where I am getting this error'
    dsNewRow.Item("emp_gend") = ComboBox1.Text
    dsNewRow.Item("emp_dob") = DateTimePicker1.Text
    dsNewRow.Item("emp_mstatus") = TextBox4.Text
    dsNewRow.Item("emp_qual") = RichTextBox1.Text
    dsNewRow.Item("emp_exp") = RichTextBox2.Text

It also shows:

 Input string was not in a correct format.Couldn't store <> in emp_age Column.  Expected type is Int32.

You are not inserting values to a database, you are assigning the `Text` property of a control. At which point the error you get is entirely reasonable. — GSerg, Jan 27 '16 at 14:41
Please show the code where you are attempting to write your values to the database. It is there that you should put the DbNull.Value. — Steve, Jan 27 '16 at 14:42
A string is not convertible to `DbNull.Value`, you could use `String.Empty` instead. But what are you trying to achieve at all? Your code makes no sense at all. — Tim Schmelter, Jan 27 '16 at 14:42
@Steve I have edited my question again and inserted the error location — Ali Muhammad, Jan 27 '16 at 14:52
@TimSchmelter Actually I have created a form which includes several textboxes. When a user leaves a textbox empty and clicks save so at that point all I want is null should be inserted into DB. So to add null I wrote that piece of code. — Ali Muhammad, Jan 27 '16 at 14:59
@Steve Its a column in database having datatype number. I think it does accept. — Ali Muhammad, Jan 27 '16 at 15:06

score 1 · Accepted Answer · edited May 23 '17 at 11:52

1

The DbNull.Value could be assigned to a DataRow column using a syntax like this (assuming, of course, that the emp_age column is nullable)

dsNewRow.Item("emp_age") = 
    If(string.IsNullOrWhiteSpace(TextBox2.Text), DbNull.Value, TextBox2.Text)

Here, you use the If conditional operator to set the value of the column to DbNull.Value or to the TextBox2.Text

However, there is another problem not immediately visible. This emp_age column requires an integer value and you shouldn't write in it a string. It is better to check that input value to be sure that you have a valid integer value

Dim age As Integer
If Int32.TryParse(TextBox2.Text, age) Then
    dsNewRow.Item("emp_age") = age
Else
    dsNewRow.Item("emp_age") = DBNull.Value
End If

edited May 23 '17 at 11:52

Community

1
1

answered Jan 27 '16 at 14:55

Steve

213,761
22
232
286

Thanks a lot @steve for your help man. First code worked perfect. You are right if string is being inserted it will give an error but when I tried this code(2nd one) It give me an error: `Object reference not set to an instance of an object.` on this line `If Int32.TryParse(TextBox2.Text, age) Then` – Ali Muhammad Jan 27 '16 at 15:51
Sorry It was my error, it worked now. Thanks @steve – Ali Muhammad Jan 27 '16 at 16:31

score 0 · Answer 2 · answered Jan 27 '16 at 14:56

No matter what you try, TextBox2.Text will never be DbNull.Value. So you have to find a way to define the empty values. If you are allowed to treat empty strings as DbNulls, you could write:

dsNewRow.Item("emp_age") = If(String.IsNullOrEmpty(TextBox2.Text), DbNull.Value, TextBox2.Text)

However this still requires the column emp_age to support DbNull values.

Cannot insert null into database vb.net

2 Answers2