insert into error, using access db in c#

Question

ive got problem with my program, i got error in cmd.ExecuteNonQuery();, telling me "Data type mismatch in criteria expression."

using (OleDbConnection myCons = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=QuizDatabase.accdb"))
              {
                OleDbCommand cmd = new OleDbCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "insert into HighScores ([ID],[Name],[Score]) values (?,?,?)";
                cmd.Parameters.AddWithValue("@ID", id.Text);
                cmd.Parameters.AddWithValue("@Name", name.Text);
                cmd.Parameters.AddWithValue("@Score", score.Text);
                cmd.Connection = myCons;
                myCons.Open();
                cmd.ExecuteNonQuery();

                myCons.Close();

            }

thanks in advance! i really appreaciate fast response :)

thanks!! Steve for the help!

Steve · Accepted Answer · 2013-06-28 15:42:40Z

2

AddWithValue is really handy, but could lead to errors. If any of your fields is of numeric type you need to convert to the appropriate type the value that you pass to AddWithValue. As it stands now your code passes all strings. Probably you need

cmd.Parameters.AddWithValue("@ID", Convert.ToInt32(id.Text));
cmd.Parameters.AddWithValue("@Name", name.Text);
cmd.Parameters.AddWithValue("@Score", Convert.ToInt32(score.Text));

By the way. If the ID column is an Autonumber column you should avoid to pass that value.

I concur with Jon Skeet about that it is better to avoid AddWithValue (and you should avoid it with Sql Server and other optimizing database engine). AddWithValue cannot accurately convert your input values to the underlying database type.

This is a very interesting article

How Data Access Code Affects Database Performance

edited Jun 28, 2013 at 15:42

answered Jun 28, 2013 at 15:37

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mary Grace Isanan Viaje Over a year ago

ohH! BTW!, i cant insert another one, it says it will replace the existing one, is there a method to insert data per line?

Jon Skeet Over a year ago

@MaryGraceIsananViaje: Well presumably you should be using a different ID for each record... were you trying to use the same ID twice?

Mary Grace Isanan Viaje Over a year ago

0w! i got it wrong, i just put wrong variable, thanks! problem solved

Jon Skeet · Accepted Answer · 2013-06-28 15:37:11Z

0

I suspect this is the problem:

cmd.Parameters.AddWithValue("@Score", score.Text);

I would expect a score to be a numeric field, so something like:

cmd.Parameters.Add("@Score", OleDbType.Integer).Value = numericScore;

... where numericScore is a variable of type int (or whatever's appropriate).

The same might be true for Id as well - we don't know what the field type is. Basically, you should try to make the parameter types match up with the field types as closely as possible. If you need to do any string conversion (e.g. parsing the value in a text box) I'd strongly recommend doing that in the .NET code rather than leaving it to the database.

(While you could still use AddWithValue, I believe it's a better idea to explicitly specify the type in the Add call and then set the value.)

answered Jun 28, 2013 at 15:37

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

2 Comments

Mary Grace Isanan Viaje Over a year ago

i tried this cmd.Parameters.Add("@Score", OleDbType.Integer).Value = Convert.ToInt32(this.score.Text); but still got the problem, but thanks for fast response!

Jon Skeet Over a year ago

@MaryGraceIsananViaje: Well what's the type of the ID column in the database? (Given that you've accepted the other answer, presumably ID is an integer. In future, when you ask a question please try to include all the relevant information to start with, so we don't have to guess.)

Collectives™ on Stack Overflow

insert into error, using access db in c#

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related