Debug "Syntax error in INSERT INTO statement" in C# when submitting data to Access Database using OleDbConnection

Question

I'm diving head first into both C# and Access databases. This is all brand new to me, so I have a small test database set up to work with a small template. I'm trying to figure out why I keep getting a syntax error that is triggered by the ExecuteNonQuery() method. Any help and insight would be appreciated.

Edit: SOLVED: This is the working code for this situation. All help was greatly appreciated!

    public void addToDb()
    {
        String first = "John";
        String last = "Doe";
        String testPath = GVar.TEST_FILEPATH + GVar.TEST_DATABASE;
        String strCommand = "INSERT INTO ID ([First], [Last]) Values(@First, @Last)";
        OleDbConnection dbTest = null;
        OleDbCommand cmd = null;

        try
        {
            dbTest = new OleDbConnection(GVar.OLE_DB_WRITE + testPath);
            dbTest.Open();

            cmd = new OleDbCommand(strCommand, dbTest);
            cmd.Parameters.AddWithValue("@First", first);
            cmd.Parameters.AddWithValue("@Last", last);
            cmd.ExecuteNonQuery();

            Console.WriteLine("Data Added");
        }
        catch (Exception ex)
        {
            Console.WriteLine("Db Test: " + ex.Message);
        }
        dbTest.Close();
    }

Soner Gönül · Accepted Answer · 2014-05-01 19:04:06Z

1

From OleDbCommand.Parameters property

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used. For example:

SELECT * FROM Customers WHERE CustomerID = ?

Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text.

I don't see anything wrong in your INSERT statement other than this.

cmd.CommandText = "INSERT INTO Identity ([First],[Last]) VALUES(?, ?)";
cmd.Parameters.AddWithValue("@First", first);
cmd.Parameters.AddWithValue("@Last", last);
cmd.ExecuteNonQuery();

edited May 1, 2014 at 19:04

answered May 1, 2014 at 15:21

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

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

9 Comments

johndoe86x Over a year ago

This doesn't seem to make a difference. Is there something else I'm missing? cmd.CommandText = "INSERT INTO Identity ([First],[Last]) VALUES(?, ?)";

Soner Gönül Over a year ago

@johndoe86x Also you changed your parameters like new OleDbParameter(?, first) right? Actually, I didn't see any syntax error in your INSERT statement. What is your cmd looks like when you debug your code after you add parameter values?

johndoe86x Over a year ago

I updated the OP to reflect my current code. I also removed the try/catch statements so the program would break at the appropriate spot. The unhandled exception still occurs at cmd.ExecuteNonQuery();

Soner Gönül Over a year ago

@johndoe86x Debug your code step by step and watch your cmd before your cmd.ExecuteNonQuery(); line. And your First and Last columns are character type, right?

johndoe86x Over a year ago

Something I noticed was that rowArray didn't seem to pull the values properly by using new OleDbParameter("?", first). I changed this to: OleDbParameter[] rowArray = new OleDbParameter[2]; rowArray[0].Value = first; rowArray[1].Value = last; And now I'm getting an "Object reference not set to an instance of an object." Error on rowArray[0].Value = first;

|

Collectives™ on Stack Overflow

Debug "Syntax error in INSERT INTO statement" in C# when submitting data to Access Database using OleDbConnection

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related