I am getting syntax error on executenonquery() in c#.

Question

0.00/5 (No votes)

See more:

.NET4.5

I am entering some values into the database. I know nothing wrong in the "insert into syntax", but it is showing syntax error on the ExecutenonQuery() function.

What I have tried:

Here is my code.

C#

connection.Open();
pictureBox12.Visible = true;
string sql = "insert into Pass (Sno,pdate, Relay Off Coil Voltage, Regulator Input Voltage, Relay 1 ON, Relay 1 OFF, Relay 2 ON, Relay 2 OFF, Regulator Output Voltage, MCLR Voltage, Input Voltage Sense, Current Sense, Trimpot Value, Fail Case No) values (" + int.Parse(label2.Text) + ",'"+ DateTime.Now.Date.ToShortDateString()+"'," + float.Parse(label6.Text) + "," + float.Parse(label7.Text) + "," + int.Parse(label19.Text) + "," + int.Parse(label20.Text) + "," + int.Parse(label21.Text) + "," + int.Parse(label22.Text) + "," + float.Parse(label23.Text) + "," + float.Parse(label24.Text) + "," + float.Parse(label25.Text) + "," + float.Parse(label26.Text) + "," + float.Parse(label27.Text) + ",1)";
OleDbCommand cmd = new OleDbCommand(sql, connection);

cmd.ExecuteNonQuery();
connection.Close();

Posted 9-May-16 20:34pm

S.Soundar

Updated 9-May-16 21:29pm

Karthik_Mahalingam

v2

Add a Solution

Comments

Tomas Takac 10-May-16 2:49am

First of all use parameters[^]!
Now what's the content of the sql variable? Please add the information to your question via "Improve question".

S.Soundar 10-May-16 6:42am

Thank you Tom. I solved it.

Richard MacCutchan 10-May-16 3:19am

What a great example of how not to write a program.

S.Soundar 10-May-16 6:42am

If it is wrong help me correct it, don't comment like this. I am not an expert like you.

Richard MacCutchan 10-May-16 11:44am

OK.

Your SQL command is using string concatenation rather than proper parameters. This exposes you to SQL injection which can destroy your entire database.

You use int.Parse and float.Parse, both of which will crash your code if the strings contain illegal characters. Use the TryParse methods to catch illegal values.

You use int.Parse and float.Parse to convert values from strings to numbers, in a stringizing operation, so they will get converted straight back to strings.

All your form field names use the defaults set by Visual Studio, so have no meaning in the context of your program. In 3 months will you remember what label22 is supposed to represent? And chances are, you actually meant to use TextBox values rather than Labels. In either case use meaningful variable names.

You do not catch the return value from ExecuteNonQuery so you have no idea whether your command succeeds or not.

Enough to be going on with?

S.Soundar 11-May-16 5:00am

For label22 i am getting value from another form and going to store into the database Richard.
For that Int.parse and float.parse I am going to receive data from the external device through serial port and displaying it to the textbox or in the label. I am going to filter the necessary data by using and start and end character. So i think the datatype will be string and need to change it to int and float format, so that i can generate charts easily i think. If there is any other better way to do this please guide me Sir.

Karthik_Mahalingam 10-May-16 3:31am

take care of SQL Injection[^]

S.Soundar 10-May-16 6:41am

Thanks Karthik.

Karthik_Mahalingam 10-May-16 6:42am

welcome soundar :)

Nagarjuna99 10-May-16 6:08am

use try catch method for bettor results.

S.Soundar 10-May-16 6:41am

Thank you Nagarjuna

1 solution

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)

Suvendu Shekhar Giri · Accepted Answer · 2016-05-09T20:48:00

There should not white spaces in between the Column names.
If you really have white spaces in the column names then try something like-

C#

string sql = "insert into Pass (Sno,pdate, [Relay Off Coil Voltage], [Regulator Input Voltage], [Relay 1 ON], [Relay 1 OFF], [Relay 2 ON], [Relay 2 OFF], [Regulator Output Voltage], [MCLR Voltage], [Input Voltage Sense], [Current Sense], [Trimpot Value],[Fail Case No])....

You need to wrap such column name with a pair of square braces.

Please let me know, if you have more questions.

Thanks :)