Syntax error using INSERT INTO

Question

3.00/5 (3 votes)

See more:

<->Hi,

Getting crazy with a syntax error that I´m really not understanding.

I've checked table and field names and it's all good, but for some reason doesn't accept the "Insert". On the other hand Selects and Updates are working.
I'm confused.

Thanks in advance

heres the code:

C#

public void ExportToDB(FastOrder_T3.RequestDetails[] Incoming)
{

    OleDbConnection dbConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @Properties.Settings.Default.DBPath);

    try
    {
        dbConnection.Open();
        OleDbCommand dbCommand;

        foreach (RequestDetails item in Incoming)
        {
            string QueryDB = "INSERT INTO Details (Order, ProductCode, ProductName, Tray, Index, Depth, DeviceID, IO) "
            +"VALUES ('" + item.Order + "', '" + item.ProductCode + "', '" + item.ProductName + "', '" + item.Tray + "', '" + item.Index + "', '" + item.Depth + "', '" + item.DeviceID + "', '" + item.IO + "');";
            dbCommand = new OleDbCommand(QueryDB, dbConnection);
            dbCommand.ExecuteNonQuery();
            dbCommand.Dispose();
        }



        dbConnection.Close();
    }


    catch(OleDbException e)
    {

        dbConnection.Close();
    }

}

base {System.Data.Common.DbException} = {"Erro de sintaxe na instrução INSERT INTO."}

ErrorCode = -2147217900

Errors = {System.Data.OleDb.OleDbErrorCollection}

Final result of QueryDB = "INSERT INTO Details(Order, ProductCode, ProductName, Tray, Index, Depth, DeviceID, IO) VALUES('0001','1234566','Teste 4','3','1','1','1','IN');"

I copy/pasted the field names in case i was missing something. They are all set to text.

Posted 21-Dec-10 5:36am

jbravofaria

Updated 21-Dec-10 6:04am

v4

Add a Solution

Comments

Henry Minute 21-Dec-10 11:43am

When you get errors like this, it is very difficult for us to help you if you do not tell us what the error is, preferably by pasting the entire thing into your question.

Please, Please PLEASE do not add it as an Answer. Use the green 'Improve question' widget.

Kschuler 21-Dec-10 11:44am

What is the exact text of the error?

dasblinkenlight 21-Dec-10 11:55am

Could you please verify that none of the item attributes (ProductName, ProductCode, etc.) contain single quotes? A single quote will result in an command that is impossible to parse. In general, forming SQL dynamically is error-prone, because this technique is sensitive to the content of your data. Generally accepted fix involves using SQL parameters.

2 solutions

Solution 1

I would suggest using parameterised queries - it would look a lot neater, and may get rid of the problem:

C#

string QueryDB = "INSERT INTO Details (Order, ProductCode, ProductName, Tray, Index, Depth, DeviceID, IO) VALUES (@OR, @PC, @PN, @TR, @IN, @DP, @DID, @IO)";
dbCommand = new OleDbCommand(QueryDB, dbConnection);
dbCommand.Parameters.AddWithValue("@OR", item.Order);
dbCommand.Parameters.AddWithValue("@PC", item.ProductCode);
dbCommand.Parameters.AddWithValue("@PN", item.ProductName);
dbCommand.Parameters.AddWithValue("@TR", item.Tray);
dbCommand.Parameters.AddWithValue("@IN", item.Index);
dbCommand.Parameters.AddWithValue("@DP", item.Depth);
dbCommand.Parameters.AddWithValue("@DID", item.DeviceID);
dbCommand.Parameters.AddWithValue("@IO", item.IO)l
dbCommand.ExecuteNonQuery();

If it doesn't cure the problem, it may make it easier to read without the single quotes!

[edit]Oops! Should have been AddWithValue rather than Add: the later is depreciated.[/edit]

Posted 21-Dec-10 5:50am

OriginalGriff

Updated 21-Dec-10 5:52am

v2

Comments

Sandeep Mewara 21-Dec-10 13:44pm

Comment from OP:
thank you OriginalGriff,

but i get the same error.

I used the QueryDB string on access and worked. unfortunately, not in my app.

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)

Marc A. Brown · Accepted Answer · 2010-12-21T06:22:00

Solution 3

Try surrounding the name of the Order and Index fields with brackets like this: INSERT INTO Details ([Order], ProductCode, ProductName, Tray, [Index], Depth, DeviceID, IO)

You can do this with the code as you originally presented it or with the changes OriginalGriff proposed.

Posted 21-Dec-10 6:22am

Marc A. Brown

Updated 21-Dec-10 6:26am

v2

Comments

jbravofaria 22-Dec-10 4:18am

Thank you very much Marc.
So i guess i was using reserved words, or something similar...
I'll take also OriginalGriff tip, code looks very clean that way.
Regards

Marc A. Brown 22-Dec-10 6:02am

You're quite welcome. Glad I could help.