Data Mismatch In Criteria Expression

Question

I am trying to delete data from a Database AND the DataGridViewer using Winforms on Visual Studio. The way I am doing this is selecting a cell, and based on where that cell is, that row will be deleted. The selected row will read two strings and one date. I've tested the two strings and they work perfectly when deleting data. When it comes to the date, it doesn't seem to work for me, I keep getting an error. The error message will be attached as an image and the code will be below. I am fairly new to C# and SQL, just to put that out there.

private void delete_Click(object sender, EventArgs e)
        {
            foreach (DataGridViewCell theCell in daily_log.SelectedCells)
            {
                if (theCell.Selected)
                {
                    string eid = daily_log[0, theCell.RowIndex].Value.ToString();
                    string aid = daily_log[4, theCell.RowIndex].Value.ToString();
                    DateTime dt = Convert.ToDateTime(daily_log[5, theCell.RowIndex].Value);
                    try
                    {
                        connection.Open();
                        using (OleDbCommand cmd = new OleDbCommand("DELETE FROM DailyLog WHERE EmployeeID='" + eid + "' AND ActivityID = '" + aid + "' AND Date = '" + dt.Date + "'", connection))
                        {
                            cmd.ExecuteNonQuery();
                        }
                        connection.Close();
                        daily_log.Rows.RemoveAt(theCell.RowIndex);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Err: " + ex);
                    }
                }
            }
        }

Is this a conversion error? And if so, how would I fix this?

Answer 1

You could try to use OledbParameter to delete data from access database.

Here is a code example you can refer to.

        OleDbConnection conn = new OleDbConnection("connstr");
        private void button1_Click(object sender, EventArgs e)
        {
            foreach (DataGridViewCell theCell in dataGridView1.SelectedCells)
            {
                if (theCell.Selected)
                {
                    string id = dataGridView1[0, theCell.RowIndex].Value.ToString();
                    string aid = dataGridView1[1, theCell.RowIndex].Value.ToString();
                    DateTime dt = Convert.ToDateTime(dataGridView1[2, theCell.RowIndex].Value);
                    try
                    {
                        conn.Open();
                        string sql = "delete from DailyLog where ID=@ID AND AID=@AID AND Date=@Date";
                        using (OleDbCommand cmd = new OleDbCommand(sql, conn))
                        {
                            cmd.Parameters.AddWithValue("@ID", id);
                            cmd.Parameters.AddWithValue("@AID", aid);
                            cmd.Parameters.AddWithValue("@Date", dt);
                            cmd.ExecuteNonQuery();
                        }
               
                        dataGridView1.Rows.RemoveAt(theCell.RowIndex);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Err: " + ex);
                    }
                }
            }
            conn.Close();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            conn.Open();
            string query = "select * from DailyLog";
            OleDbDataAdapter da = new OleDbDataAdapter(query, conn);
            OleDbCommandBuilder builder = new OleDbCommandBuilder(da);
            var ds = new DataSet();
            da.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0];
            conn.Close();
        }

Result: