Warm tip: This article is reproduced from serverfault.com, please click

Error sending data to the Access Database

发布于 2020-11-30 03:26:48

I have this exception when I try to send my code to the Access Database:

System.Data.OleDb.OleDbException (0x80040E07): data type mismatch in a selection condition expression

at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at KPIZ.Form2.btn_save_Click(Object sender, EventArgs e) in C:\Users\booku\source\repos\KPIZ\KPIZ\Form2.cs:line 110

My code:

private void btn_save_Click(object sender, EventArgs e)
{
        try
        {
            byte[] imageBt = null;
            FileStream fstream = new FileStream(this.textBox2.Text, FileMode.Open, FileAccess.Read);
            BinaryReader br = new BinaryReader(fstream);
            imageBt = br.ReadBytes((int)fstream.Length);

            connection.Open();

            OleDbCommand command = new OleDbCommand();
            command.Connection = connection;
            command.CommandText = @"INSERT INTO [SportInfo]([ID],[FirstName],[LastName],[DOB],[Sport],[DIW],[Image])" + "VALUES(@id, @Fn, @Ln, @Date, @Sports, @days ,@IMG)";
            
            command.Parameters.Add(new OleDbParameter("@Fn", Convert.ToString(txt_fname.Text)));
            command.Parameters.Add(new OleDbParameter("@Ln", Convert.ToString(txt_lname.Text)));
            command.Parameters.Add(new OleDbParameter("@Date", Convert.ToString(mtxt_dob.Text)));
            command.Parameters.Add(new OleDbParameter("@Sports", Convert.ToString(txt_sport.Text)));
            command.Parameters.Add(new OleDbParameter("@days", Convert.ToInt32(nmc_diw.Text)));
            command.Parameters.Add(new OleDbParameter("@id", Convert.ToInt32(txt_id.Text)));
            command.Parameters.Add(new OleDbParameter("@IMG", imageBt));

            command.ExecuteNonQuery();

            MessageBox.Show("Data Saved");
            connection.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show("Unlucky  " + ex);
            connection.Close();
        }

        try
        {
            connection.Open();
            OleDbCommand command = new OleDbCommand();
            command.Connection = connection;
            string query = "select ID,LastName,FirstName,DOB,Sport,DIW, from SportInfo ";
            command.CommandText = query;

            OleDbDataAdapter da = new OleDbDataAdapter(command);
            dt = new DataTable();
            da.Fill(dt);
            dataGridView1.DataSource = dt;

            connection.Close();
        }
}
Questioner
booker
Viewed
0
Erik A 2020-11-30 14:41:22

OLEDB parameters are always positional. This means the order of parameters in your query needs to match the order of parameters as they're added in your code.

Also, you need to add your date parameter to be a date, not a string.

Adjust your parameter ordering to match:

        command.Parameters.Add(new OleDbParameter("@id", Convert.ToInt32(txt_id.Text)));
        command.Parameters.Add(new OleDbParameter("@Fn", Convert.ToString(txt_fname.Text)));
        command.Parameters.Add(new OleDbParameter("@Ln", Convert.ToString(txt_lname.Text)));
        command.Parameters.Add(new OleDbParameter("@Date", DateTime.Parse(mtxt_dob.Text)));
        command.Parameters.Add(new OleDbParameter("@Sports", Convert.ToString(txt_sport.Text)));
        command.Parameters.Add(new OleDbParameter("@days", Convert.ToInt32(nmc_diw.Text)));
        command.Parameters.Add(new OleDbParameter("@IMG", imageBt));

If the order doesn't match, you get lots of errors, because now you're trying to insert a text field in your date column or integer column.