user6648485 user6648485 - 4 months ago 14
C# Question

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

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?

Answer

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));