CODEWITHSUNDEEP
c#sqlms-access-2013
Jacob Lambert
Jacob Lambert

Reputation: 7679

SQL Insert Into Syntax Error using C#

I'm transferring information from one Access DB to another using C#. Using the following code generates an error but by putting the query in Access using the same values, I get no error. Can anyone see what is going on?

    string sqlInfaction = "INSERT INTO AquiredInfractions" +
                                "(AgentID, DateID, InfractionID, STime, Durration, Exception) " +
                            "SELECT " +
                                "@ID AS AgentID, (SELECT D.DateID FROM DateCodes AS D WHERE D.DateValue = @DT) AS DateID, " +
                                "I.InfractionID, @ST AS STime, @DR AS Durration, @E AS Exception " +
                            "FROM " +
                                "InfractionTypes AS I " +
                                    "INNER JOIN " +
                                "Groupings AS G " +
                                    "ON I.GroupingID = G.GroupingID " +
                            "WHERE " +
                                "G.GroupingTitle = @NAME " +
                                "AND " +
                                "I.MinDur <= @DR1 AND I.MaxDur >= @DR2;";

    OleDbCommand InfCmd = null;

Then the setup:

    string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\New.Employee\Documents\UserMan2.accdb";
        conn = new OleDbConnection(conString);

    InfCmd = new OleDbCommand(sqlInfaction, conn);

        InfCmd.Parameters.Add("@ID", OleDbType.Integer);
        InfCmd.Parameters.Add("@DT", OleDbType.Date);
        InfCmd.Parameters.Add("@ST", OleDbType.SmallInt);
        InfCmd.Parameters.Add("@DR", OleDbType.SmallInt);
        InfCmd.Parameters.Add("@E", OleDbType.Boolean);
        InfCmd.Parameters.Add("@NAME", OleDbType.VarWChar);
        InfCmd.Parameters.Add("@DR1", OleDbType.SmallInt);
        InfCmd.Parameters.Add("@DR2", OleDbType.SmallInt);

And in the transfer function:

            InfCmd.Parameters["@ID"].Value = inf.AgentID;
            InfCmd.Parameters["@DT"].Value = inf.date;
            InfCmd.Parameters["@ST"].Value = inf.startTime;
            InfCmd.Parameters["@DR"].Value = inf.durration;
            InfCmd.Parameters["@E"].Value = inf.exception;
            InfCmd.Parameters["@NAME"].Value = inf.infract;
            InfCmd.Parameters["@DR1"].Value = inf.durration;
            InfCmd.Parameters["@DR2"].Value = inf.durration;

            InfCmd.ExecuteNonQuery();

In testing, I stepped through the running code, using the values that are put into the above parameters to test the query in Access.

The Value of sqlInfaction via quickwatch:

        sqlInfaction    "INSERT INTO AquiredInfractions(AgentID, DateID, InfractionID, STime, Durration, Exception) 
                  SELECT @ID AS AgentID, (SELECT D.DateID FROM DateCodes AS D WHERE D.DateValue = @DT) AS DateID, 
                  I.InfractionID, @ST AS STime, @DR AS Durration, @E AS Exception FROM InfractionTypes AS I 
                  INNER JOIN Groupings AS G ON I.GroupingID = G.GroupingID WHERE G.GroupingTitle = @NAME 
                  AND I.MinDur <= @DR1 AND I.MaxDur >= @DR2;"   string

The exception gives the following:

Message: Syntax error in INSERT INTO statement.
Error Code: -2147217900

If there is something specific from the exception thrown, let me know but that is the entire message.

Upvotes: 0

Views: 199

Answers (1)

Jacob Lambert
Jacob Lambert

Reputation: 7679

After playing around, I found the answer to be missing [] around Exception. The correct query reads:

    string sqlInfaction = "INSERT INTO AquiredInfractions" +
                            "(AgentID, DateID, InfractionID, STime, Durration, [Exception]) " +
                        "SELECT " +
                            "@ID AS AgentID, (SELECT D.DateID FROM DateCodes AS D WHERE D.DateValue = @DT) AS DateID, " +
                            "I.InfractionID, @ST AS STime, @DR AS Durration, @E AS [Exception] " +
                        "FROM " +
                            "InfractionTypes AS I " +
                                "INNER JOIN " +
                            "Groupings AS G " +
                                "ON I.GroupingID = G.GroupingID " +
                        "WHERE " +
                            "G.GroupingTitle = @NAME " +
                            "AND " +
                            "I.MinDur <= @DR1 AND I.MaxDur >= @DR2;";

Upvotes: 1

Related Questions