How Would I Add a Date To Microsoft Access?

Question

I am having issues adding dates/times to Microsoft Access, this is my code:

private void submit_Click(object sender, EventArgs e)
        {
            try
            {
                connection.Open();
                OleDbCommand command = new OleDbCommand();
                command.Connection = connection;
                command.CommandText = "insert into DailyLog (EmployeeID,BusNumber,RouteID,DestinationID,ActivityID,Date,MilesDriven,GasInGallons,Comments) values('"+ employee.SelectedValue + "','" + bus.SelectedValue + "','" + route.SelectedValue + "','" + dest.SelectedValue + "','" + activity.SelectedValue + "','" + theDate.Value.ToString("MM/dd/yyyy") + "','" + miles.Value + "','" + gas.Value + "','" + comments.Text + "')";

                command.ExecuteNonQuery();
                MessageBox.Show("Your log has been submitted.");
                connection.Close();
            }
            catch(Exception ex)
            {
                MessageBox.Show("Err: " + ex);
                connection.Close();
            }
        }

It is giving me a syntax error for the "Date" only. What should I do? I've tried fixing up the properties, making it a short date, general date, etc. Nothing seems to be working for me.

Exact Error:

Answer 1

Try parameterizing your command. This will take care of any potential SQL injection problems as well as correctly formatting the values for the DBMS.

string commandText = "insert into DailyLog (EmployeeID,BusNumber,RouteID,DestinationID,ActivityID,Date,MilesDriven,GasInGallons,Comments) values(@employee, @bus, @route, @dest, @activity, @theDate, @miles, @gas, @comments)";

using (OleDbCommand command = new OleDbCommand(commandText, connection)) {

    // add parameters
    command.Parameters.Add(new OleDbParameter("@employee", OleDbType.Integer));
    command.Parameters.Add(new OleDbParameter("@theDate", OleDbType.DBDate));

    // set parameter valuess
    command.Parameters["@employee"] = employee.SelectedValue;
    command.Parameters["@theDate"] = theDate.Value;

    // execute command
    command.ExecuteNonQuery();
}

Updated to remove AddWithValue.