C# - Microsoft Access: Empty TextBox could not pass data to Access Database (Data Type: Number)

Question

I will like to pass the Empty Textbox to Access Database (Data Type: Number) but it show error (Data type mismatch in criteria expression.)

Code below was used:

oleDBCommand.CommandText =
 "INSERT INTO tblRoutineChemAnalData ([Sample1 Vol]) VALUES (@Sample1Vol)";


oleDBCommand.Parameters.Add(new OleDbParameter("@Sample1Vol", textBoxSample1.Text));

Note 1: If Access database field's Data Type set to Short Text / Long Text, code was running fine if the textboxSample1 was empty.

Note 2: If Access database field's Data Type set to Number (Double), code was running error if the textboxSample1 was empty. Error description,

Data type mismatch in criteria expression.

May I know what is the method to pass the empty textbox to Access Database that having Number (double) as Data Type?

naveen naveen · Accepted Answer · 2016-08-09T05:05:07

You could make use of the DBNull.Value like this.

double output = 0;
if (double.TryParse(textBoxSample1.Text, out output))
{
    oleDBCommand.Parameters.Add(new OleDbParameter("@Sample1Vol", output));
}
else
{
    oleDBCommand.Parameters.Add(new OleDbParameter("@Sample1Vol", DBNull.Value));
}

Make sure that the field Sample1Vol accepts null values.

Update: To use it for multiple fields, wrap it in a function like this.

private OleDbParameter CreateOleDbParameter(string parameterName, string parameterValue)
{
    double output = 0;
    if (double.TryParse(parameterValue, out output))
    {
        return new OleDbParameter(parameterName, output);
    }
    else
    {
        return new OleDbParameter(parameterName, DBNull.Value);
    }
}

Now use it like this

oleDBCommand.Parameters.Add(CreateOleDbParameter("@Sample1Vol", textBoxSample1.Text));

C# - Microsoft Access: Empty TextBox could not pass data to Access Database (Data Type: Number)

1 Answers