9 Replies - 18271 Views - Last Post: 23 May 2011 - 06:44 AM Rate Topic: -----

#1 alowais   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 55
  • Joined: 13-September 08

update table - Data type mismatch in criteria expression.

Posted 22 May 2011 - 10:44 AM

I'm trying to update a table with update statement but it doesn't update the table, I get this error message:

"Data type mismatch in criteria expression."

The code:

talConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\abc\\Documents\\Visual Studio 2010\\Projects\\TAL\\TAL\\App_Data\\db_indv.accdb;Persist Security Info=False");
                strUpdate = "UPDATE subject_tbl SET [email protected]_grade, [email protected]_description WHERE subject_id='"+DropDownList1.SelectedItem+"';";
                oleCmd = new OleDbCommand(strUpdate, talConnection);
                oleCmd.Parameters.Add("@LO_description", learningoutcome.InnerText);
                oleCmd.Parameters.Add("@LO_grade", TextBox1.Text);
                talConnection.Open();
            
                oleCmd.ExecuteNonQuery();
                talConnection.Close(); 



This is to insert data in two fields of the subject_tbl table through textbox. The two fields are the LO_description and LO_grade
I think the problem is from the dropdownlist, I don't know how to send the data from it to the DB.


So, any idea

Is This A Good Question/Topic? 0
  • +

Replies To: update table - Data type mismatch in criteria expression.

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15260
  • View blog
  • Posts: 61,140
  • Joined: 12-June 08

Re: update table - Data type mismatch in criteria expression.

Posted 22 May 2011 - 11:45 AM

First thing I noticed is you don't need the semicolon in a SQL statement. That's reserved for special uses and this isn't one of them.

What data types are _grade and _description?
Was This Post Helpful? 0
  • +
  • -

#3 alowais   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 55
  • Joined: 13-September 08

Re: update table - Data type mismatch in criteria expression.

Posted 22 May 2011 - 11:57 AM

Even if i remove it it still doesn't work

_grade is number
_description is text

the subjects on the list are text
Was This Post Helpful? 0
  • +
  • -

#4 JackOfAllTrades   User is offline

  • Saucy!
  • member icon

Reputation: 6258
  • View blog
  • Posts: 24,026
  • Joined: 23-August 08

Re: update table - Data type mismatch in criteria expression.

Posted 22 May 2011 - 01:15 PM

Datatype mismatch? Well perhaps you'd like to use this overload of the Add method?
Was This Post Helpful? 0
  • +
  • -

#5 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15260
  • View blog
  • Posts: 61,140
  • Joined: 12-June 08

Re: update table - Data type mismatch in criteria expression.

Posted 22 May 2011 - 02:27 PM

It would also be worth putting apostrophes in your sql text statement for your text variable.

This post has been edited by modi123_1: 22 May 2011 - 02:27 PM

Was This Post Helpful? 0
  • +
  • -

#6 alowais   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 55
  • Joined: 13-September 08

Re: update table - Data type mismatch in criteria expression.

Posted 22 May 2011 - 08:47 PM

I still have the same problem, it doesn't work
Was This Post Helpful? 0
  • +
  • -

#7 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15260
  • View blog
  • Posts: 61,140
  • Joined: 12-June 08

Re: update table - Data type mismatch in criteria expression.

Posted 22 May 2011 - 09:55 PM

subject_id='"+DropDownList1.SelectedItem+"';";


What is this code all about? What type is subject id? If it's a number why are the ticks around it to indicate text? Why is the dropdown list value not in a parameter when the rest are?

This post has been edited by modi123_1: 22 May 2011 - 09:55 PM

Was This Post Helpful? 0
  • +
  • -

#8 alowais   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 55
  • Joined: 13-September 08

Re: update table - Data type mismatch in criteria expression.

Posted 22 May 2011 - 10:01 PM

View Postmodi123_1, on 22 May 2011 - 09:55 PM, said:

subject_id='"+DropDownList1.SelectedItem+"';";


What is this code all about? What type is subject id? If it's a number why are the ticks around it to indicate text? Why is the dropdown list value not in a parameter when the rest are?


this is to select the value of the item in the dropdownlist and I've also tried selectedvalue.
the subject_id is a text.
i don't understand which parameter you mean?

thanks
Was This Post Helpful? 0
  • +
  • -

#9 Curtis Rutland   User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5106
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: update table - Data type mismatch in criteria expression.

Posted 23 May 2011 - 06:29 AM

Why are you mixing Parameters and string-building? Parameters are the correct way to do this. What I mean is, you have a parameter named @LO_description and [[email protected]_grade[/il], but instead of using @subject_id, you try to concatenate the value in. That's very, very bad practice. Get rid of the concatenation, and replace it with a third parameter, and add it the same way you did the first two.

Also, are you sure "subject_id" is Text? It seems to be a strange name for a text field. "id" would suggest a numeric field.
Was This Post Helpful? 0
  • +
  • -

#10 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15260
  • View blog
  • Posts: 61,140
  • Joined: 12-June 08

Re: update table - Data type mismatch in criteria expression.

Posted 23 May 2011 - 06:44 AM

Also - what does your code look like now?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1