data type mismatch using Access DB

Question

I keep getting a 'data type mismatch in criteria expression' when trying to insert into my Access 2010 table. The table has field names of: GradeKey - AutoNumber, Grade - Text, Comments - Text, TellerNum - Number, TestName - Text. The TestName is also a foreign key from another table. Any ideas on what I need to use to fix the data type mismatch?

using (OleDbConnection con = new OleDbConnection(constring)) { try { string cmdstring = "INSERT INTO GRADE (Grade, Comments, TellerNum, TestName) VALUES (@grade, @comments, @teller, @test)"; using (OleDbCommand cmd = new OleDbCommand(cmdstring, con)) { cmd.Parameters.AddWithValue("@teller", OleDbType.Integer).Value = comboBox5.Text; cmd.Parameters.AddWithValue("@grade", OleDbType.Char).Value = textBox7.Text; cmd.Parameters.AddWithValue("@comments", OleDbType.Char).Value = textBox10.Text; cmd.Parameters.AddWithValue("@test", OleDbType.Char).Value = comboBox16.Text; con.Open(); cmd.ExecuteNonQuery(); con.Close(); MessageBox.Show("Submitted Successfully"); } } catch (Exception ex) { MessageBox.Show("Failed due to " + ex.Message); } }

By the way, I am pretty positive that it is the TellerNum or @teller that is causing the problem because I was able to get it to work when I didn't include it. — JoeMarvel
– JoeMarvel, Commented May 28, 2014 at 16:06

Selman Genç · Accepted Answer · 2014-05-28 16:03:41Z

2

OleDbType.Integer should be an integer.Try parsing comboBox5.Text to int:

cmd.Parameters.AddWithValue("@teller", OleDbType.Integer).Value = int.Parse(comboBox5.Text);

answered May 28, 2014 at 16:03

Selman Genç

102k15 gold badges126 silver badges193 bronze badges

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

2 Comments

Selman Genç Over a year ago

@user3680618 try changing OleDbType.Char's to OleDbType.VarChar, if it doesn't work, remove all types and just pass the values like: cmd.Parameters.AddWithValue("@grade", textBox7.Text) ;

JoeMarvel Over a year ago

changing it to the cmd.Parameters.AddWithValue("@grade", textBox7.Text) ; worked thank you so much!

Chris Dunaway · Accepted Answer · 2014-05-28 17:56:36Z

OleDb does not support named parameters. It only supports positional parameters. You need to add your parameters in the same order as they appear in the query:

cmd.Parameters.AddWithValue("@grade", OleDbType.Char).Value = textBox7.Text; cmd.Parameters.AddWithValue("@comments", OleDbType.Char).Value = textBox10.Text; cmd.Parameters.AddWithValue("@teller", OleDbType.Integer).Value = comboBox5.Text; cmd.Parameters.AddWithValue("@test", OleDbType.Char).Value = comboBox16.Text;

Thanks, but this didn't help. It continues to give me the same error.
@user3680618 - Then as Selman22 indicates, converting to Integer may be necessary, but if your parameters are in the wrong order, you may be getting data in the wrong fields, so you should check that.

Collectives™ on Stack Overflow

data type mismatch using Access DB

2 Answers 2

2 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Related