Data type mismatch in criteria expression.

Question

1.00/5 (2 votes)

See more:

//I have used Parameterized query but still get error
//Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

C#

foreach (GridViewRow g1 in GridView1.Rows)
{
OleDbCommand oleDbCmd4 = con.CreateCommand();
con.Open();
oleDbCmd4 = new OleDbCommand("INSERT INTO [Article of clients (step)] (CodArt, NumFas, CodPie,Operac, FasExt, CodMaq,CodUti, TiePre, TieFab, CodPro, Control) VALUES (@one,@two,@three,@four,@five,@six,@seven,@eight,@nine,@ten,@eleven)", con);
oleDbCmd4.Parameters.AddWithValue("@one", g1.Cells[0].Text);
oleDbCmd4.Parameters.AddWithValue("@two", g1.Cells[1].Text);
oleDbCmd4.Parameters.AddWithValue("@three", g1.Cells[2].Text);
oleDbCmd4.Parameters.AddWithValue("@four", g1.Cells[3].Text);
oleDbCmd4.Parameters.AddWithValue("@five", g1.Cells[4].Text);
oleDbCmd4.Parameters.AddWithValue("@six", g1.Cells[5].Text);
oleDbCmd4.Parameters.AddWithValue("@seven", g1.Cells[6].Text);
oleDbCmd4.Parameters.AddWithValue("@eight", g1.Cells[7].Text);
oleDbCmd4.Parameters.AddWithValue("@nine", g1.Cells[8].Text);
oleDbCmd4.Parameters.AddWithValue("@ten", g1.Cells[9].Text);
oleDbCmd4.Parameters.AddWithValue("@eleven", g1.Cells[10].Text);
oleDbCmd4.ExecuteNonQuery();
con.Close();
}

What I have tried:

Plz help. I have tried alot but it throw me this error Data type mismatch in criteria expression.

Posted 17-Jan-17 7:39am

Member 12857358

Updated 18-Jan-17 9:27am

Patrice T

v6

Add a Solution

Comments

[no name] 17-Jan-17 13:41pm

Use a proper parameterized query.

Member 12857358 17-Jan-17 13:46pm

plz Explain. how

[no name] 17-Jan-17 13:59pm

You take that SQL injection attack query that you have written and write a parameterized query instead. If you do not know what a parameterized query is, google would have shown you hundreds or thousands of examples.

Richard Deeming 17-Jan-17 15:27pm

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ZurdoDev · Answer 1 · 2017-01-17T08:01:00

The problem is a syntax error. However, the bigger problem is your code is vulnerable to Sql injections which is a huge security risk.

To fix both problems, use parameters. Example follows:

C#

cmd.CommandText = "INSERT INTO table1(field1, field2) VALUES (@someValue, @someValue2)";
cmd.Parameters.AddWithValue("@someValue", g1.Cells[2].Text);
cmd.Parameters.AddWithValue("@someValue2", g1.Cells[3].Text);
// etc.

Bryian Tan · Answer 2 · 2017-01-17T08:39:00

Hello,

I agreed with other respected members of CodeProject that you need to look at the SQL Injection vulnerabilities. Here is an example on how the Cross-Site Scripting and SQL Injection vulnerabilities come into a nightmare.

SQL Injection and Cross-Site Scripting[^]

Regarding the error, if you run the application under debug mode with a break point next to cmd.CommandText, mouse over CommandText. You should see how the string being constructed. Better yet, copy and run it on SQL server. You will notice there syntax error on the query. Some of the field are expecting a string value and need single quote around it. Example: 'CUT STEEL', your code will look like [",'" + g1.Cells[4].Text + "',"]

But I would suggest you follow the recommendation to use parameterized query.

Patrice T · Answer 3 · 2017-01-17T09:27:00

Solution 3

Quote:
Syntax error (missing operator) in query expression 'CUT STEEL'

We can't know where is the error because we don't know the real query.
Guess: the way you build the query is for numeric values, but it seems 1 of them is alphabetic and need to have single quotes around.

Your query allow SQL injection attack
SQL injection - Wikipedia[^]
SQL Injection[^]

[Update]

Quote:
//Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

Look at your query, what ,is the SQL command ?

[Update]
All your parameters are text, every time you have a field which is not text, you will get an error message.
Parameters need to be of type of field.

Posted 17-Jan-17 9:27am

Patrice T

Updated 18-Jan-17 19:41pm

v3

Comments

Member 12857358 18-Jan-17 0:34am

//I have used Parameterized query but still get error
//Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
foreach (GridViewRow g1 in GridView1.Rows)
{
OleDbCommand oleDbCmd4 = con.CreateCommand();
con.Open();
oleDbCmd4 = new OleDbCommand("[Article of clients (step)] (CodArt, NumFas, CodPie,Operac, FasExt, CodMaq,CodUti, TiePre, TieFab, CodPro, Control) VALUES (@one,@two,@three,@four,@five,@six,@seven,@eight,@nine,@ten,@eleven)", con);
oleDbCmd4.Parameters.AddWithValue("@one", g1.Cells[0].Text);
oleDbCmd4.Parameters.AddWithValue("@two", g1.Cells[1].Text);
oleDbCmd4.Parameters.AddWithValue("@three", g1.Cells[2].Text);
oleDbCmd4.Parameters.AddWithValue("@four", g1.Cells[3].Text);
oleDbCmd4.Parameters.AddWithValue("@five", g1.Cells[4].Text);
oleDbCmd4.Parameters.AddWithValue("@six", g1.Cells[5].Text);
oleDbCmd4.Parameters.AddWithValue("@seven", g1.Cells[6].Text);
oleDbCmd4.Parameters.AddWithValue("@eight", g1.Cells[7].Text);
oleDbCmd4.Parameters.AddWithValue("@nine", g1.Cells[8].Text);
oleDbCmd4.Parameters.AddWithValue("@ten", g1.Cells[9].Text);
oleDbCmd4.Parameters.AddWithValue("@eleven", g1.Cells[10].Text);
oleDbCmd4.ExecuteNonQuery();
con.Close();
}

Patrice T 18-Jan-17 0:47am

Use Improve question to update your question.
So that everyone can pay attention to this information.

[no name] 18-Jan-17 8:24am

Good heavens. READ the error message. Do you see any of those keywords in your query anywhere?

DON'T change your question to be something completely different than the original. Now, all the answers you got for your original question don't make any sense.

Member 12857358 18-Jan-17 11:39am

Sorry for this, i am new here so i dont know how to do all these things.now i will keep in mind.

Member 12857358 18-Jan-17 11:40am

plz solve my problem. i am stuck here from two days

Data type mismatch in criteria expression.

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0