Null value in access with combobox (vb.net)

Question

I have a combobox that is connected to a column in a access database, I would like to put a null value in the field of the column's database if my combobox value is empty. I wrote this code and I know that is wrong but I can't figure it out.

myvalue is declared as integer

Dim myConnectionString As SqlConnection = New SqlConnection("Data Source=*****\****;Initial Catalog=****;user=***;password=****")

    Dim myCommand As String
    Dim cmd As SqlCommand
    Dim myvalue As Integer


If IsDBNull(Form1.CBEsp2.SelectedValue) Then
    myvalue = vbNull
Else
    myvalue = Form1.CBEsp2.SelectedValue
End If

MsgBox(Form1.CBEsp2.SelectedValue)

myCommand = "UPDATE DoctorEnterpriseDetails SET " & _
         "RankId = " & Form1.CBSelec.SelectedValue & ", " & _
         "GroupId = " & Form1.CBCateg.SelectedValue & ", " & _
         "PrescribingPotential = " & Form1.CBPP.SelectedValue & ", " & _
         "Observation = '" & Form1.TxtObs2.Text & "', " & _
         "Telephone = '" & Convert_Null(Form1.TxtTelefone.Text, "") & "', " & _
         "Mobile = '" & Convert_Null(Form1.TxtTelem.Text, "") & "', " & _
          "Speciality1 = " & Form1.CBEsp1.SelectedValue & ", " & _
         "Speciality2 = " & myvalue & " " & _
          "WHERE EnterpriseId = 26 AND DoctorId = " & Form1.labelvazia.Text

MsgBox(myCommand)
cmd = New SqlCommand(myCommand, myConnectionString)
cmd.Connection.Open()
cmd.ExecuteNonQuery()

cmd.Connection.Close()

Thanks.

Please show the code that tries to write this value to the database — Steve, Mar 25 '15 at 10:52
That's not the correct way to create a query. You should ALWAYS use a parameterized query and your task to pass a null value will be really simple — Steve, Mar 25 '15 at 10:58

score 0 · Accepted Answer · edited May 23 '17 at 12:12

I think you should rewrite your query and use a parameterized approach instead of a string concatenation.

If IsDBNull(Form1.CBEsp2.SelectedValue) Then
    meuvalor = DBNull.Value
Else
    meuvalor = Form1.CBEsp2.SelectedValue
End If
Dim myCommand = "UPDATE DoctorEnterpriseDetails SET " & _
     "RankId = @RankID, GroupId = @GroupID, " & _ 
     "PrescribingPotential = @PrescribingPotential, " 
     "Observation = @Observation, Telephone = @Telephone, " & _
     "Mobile = @Mobile, Speciality1 = @Speciality1, " 
     "Speciality2 = @Speciality2 " & _
     "WHERE EnterpriseId = 26 AND DoctorId = @DoctorID"

Using conn = New SqlConnection("....")
Using cmd  = New SqlCommand(myCommand, conn)
   conn.Open()
   cmd.Parameters.Add("@RankID", SqlDbType.Int).Value = Convert.ToInt32(Form1.CBSelec.SelectedValue)
   cmd.Parameters.Add("@GroupID", SqlDbType.Int).Value = Convert.ToInt32(Form1.CBCateg.SelectedValue)
   cmd.Parameters.Add("@PrescribingPotential", SqlDbType.Int).Value = Convert.ToInt32(Form1.CBPP.SelectedValue)
   cmd.Parameters.Add("@Observation", SqlDbType.NVarChar).Value = Form1.TxtObs2.Text 
   ... and so on for the other parameters.....
   ... the one with null value will be
   cmd.Parameters.Add("@Speciality2", SqlDbType.Int).Value = meuvalor 
   ....
   cmd.ExecuteNonQuery()
End Using
End Using

This is a sample for a parameterized query. The values are not concatenated in the query text (making it more readable in the process) but are added to the Parameters collection of the SqlCommand while the query text contains only a placeholder (@xxxx). Each Parameter should be defined with the correct DataType (SqlDbType enumeration) and converted from your UI representation to the VB.NET type before adding it to the collection.
This approach makes your code more safer because you don't have to worry about malicious users that types dangerous texts in your textboxes (Sql Injection) and when the parameters are specified with their type there is no space for parsing errors from the values (decimal separators, date formats, string quotes, etc....)

For a NULL value you should pass a DBNull.Value that will be correctly transalated in the null you require in the database field.

score 0 · Answer 2 · answered Mar 25 '15 at 16:31

I wrote this and it worked..maybe is not the best solution.

 If IsNothing(Form1.CBEsp2.SelectedValue) Then
            myCommand = "UPDATE DoctorEnterpriseDetails SET " & _
                "RankId = " & Form1.CBSelec.SelectedValue & ", " & _
                "GroupId = " & Form1.CBCateg.SelectedValue & ", " & _
                "PrescribingPotential = " & Form1.CBPP.SelectedValue & ", " & _
                "Observation = '" & Form1.TxtObs2.Text & "', " & _
                "Telephone = '" & Convert_Null(Form1.TxtTelefone.Text, "") & "', " & _
                "Mobile = '" & Convert_Null(Form1.TxtTelem.Text, "") & "', " & _
                 "Speciality1 = " & Form1.CBEsp1.SelectedValue & ", " & _
                "Speciality2 = NULL " & _
                 "WHERE EnterpriseId = 26 AND DoctorId = " & Form1.labelvazia.Text
        Else
            myCommand = "UPDATE DoctorEnterpriseDetails SET " & _
                 "RankId = " & Form1.CBSelec.SelectedValue & ", " & _
                 "GroupId = " & Form1.CBCateg.SelectedValue & ", " & _
                 "PrescribingPotential = " & Form1.CBPP.SelectedValue & ", " & _
                 "Observation = '" & Form1.TxtObs2.Text & "', " & _
                 "Telephone = '" & Convert_Null(Form1.TxtTelefone.Text, "") & "', " & _
                 "Mobile = '" & Convert_Null(Form1.TxtTelem.Text, "") & "', " & _
                  "Speciality1 = " & Form1.CBEsp1.SelectedValue & ", " & _
                 "Speciality2 =  " & Form1.CBEsp2.SelectedValue & " " & _
                  "WHERE EnterpriseId = 26 AND DoctorId = " & Form1.labelvazia.Text
        End If

Null value in access with combobox (vb.net)

2 Answers2