Insert null vale to Access database

Question

If a form has four input values: textBoxForField1, comboBoxForField2, comboBoxForField3, DateTimeForField4.

Let us say, textBoxForField1 and comboBoxForField2 are required, they have already be checked before writing below SQL statement; comboBoxForField3 and DateTimeForField4 are optional. So I only need to check if comboBoxForField3 and DateTimeForField4 are null.

So I will write something like below. Not sure if I write syntax correctly, it is just my logic.

Now here are questions:

1. For this question, there are only 2 optional input value (Field3 and Field4 are optional), there are already combination of 4 SQL statement. If three optional fields, then combination of 8 SQL statements; if four optional fields, then combination of 16 SQL statements. Apparently, the way I write is not efficient, there must be correct way to write it, how to write it?
2. How to enter DateTime picker value into database? Make sure it is Date data type (Access table field data type is date too), not string data type.

Thanks.

if (comboBoxForField3.Text == null && DateTimeForField4.SelectedDate == null)

{
string sqlQuery = "Insert into TableRecord (Field1, Field2, Field3, Field4) Value (textBoxForField1.Text, comboBoxForField2.Text, null, null)
}

elseif (comboBoxForField3.Text == null && !(DateTimeForField4.SelectedDate == null))

{
string sqlQuery = "Insert into TableRecord (Field1, Field2, Field3, Field4) Value (textBoxForField1.Text, comboBoxForField2.Text, null, DateTimeForField4.Value)
}

elseif (!(comboBoxForField3.Text == null) && DateTimeForField4.SelectedDate == null)

{
string sqlQuery = "Insert into TableRecord (Field1, Field2, Field3, Field4) Value (textBoxForField1.Text, comboBoxForField2.Text, comboBoxForField3.Text, null)
}

else
{
string sqlQuery = "Insert into TableRecord (Field1, Field2, Field3, Field4) Value (textBoxForField1.Text, comboBoxForField2.Text, comboBoxForField3.Text, DateTimeForField4.Value)
}

Answer 1

Hi VAer-4038,
First, you can use String.IsNullOrEmpty(String) method to check if Field3 or Field4 is null.
Then you can use the conditional operator ?: instead of if else statement.
If it is null you can pass string.Empty into datatable. Otherwise, you can pass Field3 and Field4's value.
You can enter DateTime picker value into database via dateTimePicker1.Value.Date.
Sql statemet looks like:(Id is PRIMARY KEY in my test)

 string sqlQuery =("insert into TableRecord (Id,Field1,Filed2,Filed3,Filed4) values ('" + textBox1.Text + "','" + textBox2.Text + "','" + comboBox1.SelectedItem.ToString() + "','"+(string.IsNullOrEmpty(comboBox2.SelectedItem.ToString()) ? string.Empty : comboBox2.SelectedItem.ToString())+"', '"+ (string.IsNullOrEmpty(dateTimePicker1.Value.Date.ToShortDateString()) ? string.Empty : dateTimePicker1.Value.Date.ToShortDateString()) +"') ");  

Best Regards,
Daniel Zhang

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.