CODEWITHSUNDEEP
vb.net
Sammy
Sammy

Reputation: 15

How to insert null values to Database

My Scenario,

Im working asp.net vb application I have set of values to be inserted to Database.But their some fields are optional to be filled

I need to check the textbox empty.if empty i should not enter the value to Database. How to work on this

Upvotes: 1

Views: 13737

Answers (5)

Jsen Fruge
Jsen Fruge

Reputation: 579

Here's a sample of how I handled this in my code. It's pretty lengthily cause I want to check every field that is nullable in the database.

Dim query As String = String.Empty query &= "INSERT INTO CHANGELOG(ChangeLogId, LogType, ReleaseType, ReferenceType, ChangeType, Area, Description, Notes, Date, MajorVersion, MinorVersion, RevisionVersion, BuildVersion)" query &= "VALUES " & "(NEWID()," & "@LogType, @ReleaseType, @ReferenceType, @ChangeType, @Area, @Description, @Notes, @Date, @MajorVersion, @MinorVersion, @RevisionVersion, @BuildVersion);" Using conn As New SqlConnection("server=JSEN-PC\SQLEXPRESS; Database=myDB; Persist Security Info=False;Integrated Security=True;") Using comm As New SqlCommand() With comm .Connection = conn .CommandType = CommandType.Text .CommandText = query

        If LogTypeComboBox.SelectedIndex = -1 Then 'Log Type: Accept Convert dbNull 
            .Parameters.AddWithValue("@LogType", SqlDbType.NVarChar).Value = DBNull.Value
        Else
            .Parameters.AddWithValue("@LogType", LogTypeComboBox.SelectedItem)
        End If
        If ReleaseComboBox.SelectedIndex = -1 Then 'Release Type: Accept Convert dbNull 
            .Parameters.AddWithValue("@ReleaseType", SqlDbType.NVarChar).Value = DBNull.Value
        Else
            .Parameters.AddWithValue("@ReleaseType", ReleaseComboBox.SelectedItem)
        End If
        If ReferenceComboBox.SelectedIndex = -1 Then 'Reference Type: Accept Convert dbNull 
            .Parameters.AddWithValue("@ReferenceType", SqlDbType.NVarChar).Value = DBNull.Value
        Else
            .Parameters.AddWithValue("@ReferenceType", ReferenceComboBox.SelectedItem)
        End If
        If ChangeComboBox.SelectedIndex = -1 Then 'Change Type: Accept Convert dbNull 
            .Parameters.AddWithValue("@ChangeType", SqlDbType.NVarChar).Value = DBNull.Value
        Else
            .Parameters.AddWithValue("@ChangeType", ChangeComboBox.SelectedItem)
        End If
        If FunctionalAreaComboBox.SelectedIndex = -1 Then 'Functional Area: Accept Convert dbNull 
            .Parameters.AddWithValue("@Area", SqlDbType.NVarChar).Value = DBNull.Value
        Else
            .Parameters.AddWithValue("@Area", FunctionalAreaComboBox.SelectedItem)
        End If
        If DescriptionTextBox.Text.Length = 0 Then 'Description: Accept Convert dbNull 
            .Parameters.AddWithValue("@Description", SqlDbType.Text).Value = DBNull.Value
        Else
            .Parameters.AddWithValue("@Description", DescriptionTextBox.Text)
        End If
        If NotesTextBox.Text.Length = 0 Then 'Notes: Accept Convert dbNull 
            .Parameters.AddWithValue("@Notes", SqlDbType.Text).Value = DBNull.Value
        Else
            .Parameters.AddWithValue("@Notes", NotesTextBox.Text)
        End If
        .Parameters.AddWithValue("@Date", DateDateTimePicker.Value.Date.ToString("yyyy-MM-dd")) 'Date: No Null conversion
        If MajorVersionTextBox.Text.Length = 0 Then 'MajorVersion: Accept Convert dbNull 
            .Parameters.AddWithValue("@MajorVersion", SqlDbType.Text).Value = DBNull.Value
        Else
            .Parameters.AddWithValue("@MajorVersion", MajorVersionTextBox.Text)
        End If
        If MinorVersionTextBox.Text.Length = 0 Then 'MinorVersion: Accept Convert dbNull 
            .Parameters.AddWithValue("@MinorVersion", SqlDbType.Text).Value = DBNull.Value
        Else
            .Parameters.AddWithValue("@MinorVersion", MinorVersionTextBox.Text)
        End If
        If RevisionVersionTextBox.Text.Length = 0 Then 'RevisionVersion: Accept Convert dbNull 
            .Parameters.AddWithValue("@RevisionVersion", SqlDbType.Text).Value = DBNull.Value
        Else
            .Parameters.AddWithValue("@RevisionVersion", RevisionVersionTextBox.Text)
        End If
        If BuildVersionTextBox.Text.Length = 0 Then 'BuildVersion: Accept Convert dbNull 
            .Parameters.AddWithValue("@BuildVersion", SqlDbType.Text).Value = DBNull.Value
        Else
            .Parameters.AddWithValue("@BuildVersion", BuildVersionTextBox.Text)
        End If
    End With
    Try
        conn.Open()
        comm.ExecuteNonQuery()
    Catch ex As SqlException
        MessageBox.Show(ex.Message.ToString(), "Error Message")
    End Try
End Using

End Using

Upvotes: 0

Sai Kalyan Kumar Akshinthala
Sai Kalyan Kumar Akshinthala

Reputation: 11844

you can check it like this

If String.IsNullOrEmpty(TextBox1.Text.ToString().Trim) Then 
    Databasevaluetoinsert = DBNull.Value
Else
    Databasevaluetoinsert = TextBox1.Text           
End If

Upvotes: 1

KV Prajapati
KV Prajapati

Reputation: 94645

Use Parameter collection of Command. (I presume that you have a MsSql database).

cmd=new SqlCommand("insert into tableName values (@col1,@col2,@col3)",conn)
If TextBox1.Text.Trim().Length=0 Then
  cmd.Parameters.Add("@col1",SqlDbType.Varchar).Value=DBNull.Value
else
  cmd.Parameters.Add("@col1",SqlDbType.Varchar).Value=TextBox1.Text
End If
...

Upvotes: 3

DoStuffZ
DoStuffZ

Reputation: 800

I usually use the ConvertEmptyStringToNull for optional values.

http://www.google.com/search?q=convertemptystringtonull+asp.net

The first couple of links gives a few examples, the MSDN shows how its done in VB

Upvotes: 1

Joseph Le Brech
Joseph Le Brech

Reputation: 6653

If you use a stored procedure to add to your database, you can just pass null to that parameter. It has to be a nullable variable type, like a string.

Upvotes: 1

Related Questions