none
Unhandled Exception: System.Data.SqlClient.SqlException: Incorrect syntax near '3'. RRS feed

  • Question

  • Hi I am trying to pass a datetime value 9/25/2009 3:19:54 PM to MS SQL 2008 table , but it gives the following error
    Unhandled Exception: System.Data.SqlClient.SqlException: Incorrect syntax near '3'.

    You know how to pass such a string.

    Thanks
    Ankit
    Wednesday, October 14, 2009 8:30 PM

Answers

  • You need to put single quotes around all string and datetime values in your sql query.

    Change the following code from:
    nonqueryCommand.CommandText = @"INSERT INTO BlockFloat (Identifier, DEA_ID, Block_ID, Value, MeasurTime) VALUES(" + arg1 + "," + arg2 + "," + arg3 + "," + arg4 + "," + arg5  + ")";
    To:
    nonqueryCommand.CommandText = @"INSERT INTO BlockFloat (Identifier, DEA_ID, Block_ID, Value, MeasurTime) VALUES('" + arg1 + "','" + arg2 + "','" + arg3 + "','" + arg4 + "','" + arg5  + "')";

    Note that the exception message says:
    Message type: TrendDataBlockFloat
    9/25/2009 3:19:54 PMINSERT INTO BlockFloat (Identifier, DEA_ID, Block_ID, Value, MeasurTime) VALUES(5,1,122,22,9/25/2009 3:19:54 PM)

    The expected thing is to have the following string sent back to SQL Server:
    INSERT INTO BlockFloat (Identifier, DEA_ID, Block_ID, Value, MeasurTime) VALUES('5','1','122','22','9/25/2009 3:19:54 PM')

    This requiement to have a quote around the value is only true for string and datetime based variables. You don't have to do this for other data types.

    • Marked as answer by Yichun_Feng Wednesday, October 21, 2009 1:08 AM
    Thursday, October 15, 2009 5:05 PM
    Moderator
  • Use command parameters instead of variables in your SQL statement (which is inherently risky). Below is an example:

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Yichun_Feng Wednesday, October 21, 2009 1:08 AM
    Thursday, October 15, 2009 5:49 PM
  • One more addition. I believe VALUE is a reserved word and you need to wrap it into square brackets inside of SQL statement. If you do not do this, you might receive an error as well. You actually can wrap all the names, something like

    nonqueryCommand.CommandText = @"INSERT INTO [BlockFloat] ([Identifier], [DEA_ID], [Block_ID], [Value], [MeasurTime]) VALUES(........
    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by Yichun_Feng Wednesday, October 21, 2009 1:08 AM
    Friday, October 16, 2009 10:41 AM
    Moderator

All replies

  • Ankit,

    Are you passing this as a string. i.e wrapping the whole stuff inside single quotes? It would be helpful if you paste the whole code snippet. From your error I am assuming you are trying to insert it from a .NET front end. In that case, try and use the parameter object.
    Thursday, October 15, 2009 3:26 AM
  • You need to pass value as a datetime parameter, not as a string. In this case you will avoid this error and any other potential issues with formatting or regional settings. Folowing link contains sample how to use parameters to pass values to SQL statements (check "How to use parameters" section)

    http://support.microsoft.com/kb/301075



    Val Mazur (MVP) http://www.xporttools.net
    Thursday, October 15, 2009 10:34 AM
    Moderator
  • THE CALLING SNIPPET
     else if (message.Item is TrendDataBlockFloat)
                {
                    TrendDataBlockFloat peaks = (TrendDataBlockFloat)message.Item;
                    ptb.InsertMyXMLData2(Convert.ToString(peaks.Identifier), Convert.ToString(peaks.DEA_ID), Convert.ToString(peaks.Block_ID), Convert.ToString(peaks.Value), Convert.ToDateTime(peaks.MeasurTime));
                }



    THE CALLED SNIPPET

    public override void InsertMyXMLData2(string str1, string str2, string str3, string str4, DateTime str5)
            {


                SqlConnection thisConnection = new SqlConnection("Server=KNL056ZC\\SQLExpress;Database=TrendData;Trusted_Connection=True; Asynchronous Processing=true");
                SqlCommand nonqueryCommand = thisConnection.CreateCommand();
                thisConnection.Open();


                string arg1 = str1;
                string arg2 = str2;
                string arg3 = str3;
                string arg4 = str4;
                string arg5 = str5;
               
                nonqueryCommand.CommandText = @"INSERT INTO BlockFloat (Identifier, DEA_ID, Block_ID, Value, MeasurTime) VALUES(" + arg1 + "," + arg2 + "," + arg3 + "," + arg4 + "," + arg5  + ")";
                Console.Write(nonqueryCommand.CommandText);
                nonqueryCommand.ExecuteNonQuery();
                thisConnection.Close();


            }


    EXCEPTION THROWN
    :
    Message type: TrendDataBlockFloat
    9/25/2009 3:19:54 PMINSERT INTO BlockFloat (Identifier, DEA_ID, Block_ID, Value, MeasurTime) VALUES(5,1,122,22,9/25/2009 3:19:54 PM)
    Unhandled Exception: System.Data.SqlClient.SqlException: Incorrect syntax near '3'.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at PETTrendDB.PETTrendDBConcrete.InsertMyXMLData2(String str1, String str2, String str3, String str4,  DateTime str5) in C:\Documents and Settings\singan10\Desktop\MS SQL\PETTrendDB\PETTrendDBConcrete.cs:line 83
       at XmlClassTest.Program.deserialize() in C:\Documents and Settings\singan10\Desktop\MS SQL\PETTrendRemObj\PETTrendRemObj\Program.cs:line 64
       at PETTrendRemObj.PETTrendRemObject.Work() in C:\Documents and Settings\singan10\Desktop\MS SQL\PETTrendRemObj\PETTrendRemObj\PETTrendRemObject.cs:line 204
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.runTryCode(Object userData)
       at System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode code, CleanupCode backoutCode, Object userData)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()

    C:\Documents and Settings\singan10\Desktop\MS SQL\PETTrendRemObj\PETTrendServer\bin\Debug> else if (message.Item is TrendDataBlockFloat)
    Thursday, October 15, 2009 2:42 PM
  • THE CALLING SNIPPET
     else if (message.Item is TrendDataBlockFloat)
                {
                    TrendDataBlockFloat peaks = (TrendDataBlockFloat)message.Item;
                    ptb.InsertMyXMLData2(Convert.ToString(peaks.Identifier), Convert.ToString(peaks.DEA_ID), Convert.ToString(peaks.Block_ID), Convert.ToString(peaks.Value), Convert.ToDateTime(peaks.MeasurTime));
                }



    THE CALLED SNIPPET

    public override void InsertMyXMLData2(string str1, string str2, string str3, string str4, DateTime str5)
            {


                SqlConnection thisConnection = new SqlConnection("Server=KNL056ZC\\SQLExpress;Database=TrendData;Trusted_Connection=True; Asynchronous Processing=true");
                SqlCommand nonqueryCommand = thisConnection.CreateCommand();
                thisConnection.Open();


                string arg1 = str1;
                string arg2 = str2;
                string arg3 = str3;
                string arg4 = str4;
                string arg5 = str5;
               
                nonqueryCommand.CommandText = @"INSERT INTO BlockFloat (Identifier, DEA_ID, Block_ID, Value, MeasurTime) VALUES(" + arg1 + "," + arg2 + "," + arg3 + "," + arg4 + "," + arg5  + ")";
                Console.Write(nonqueryCommand.CommandText);
                nonqueryCommand.ExecuteNonQuery();
                thisConnection.Close();


            }


    EXCEPTION THROWN
    :
    Message type: TrendDataBlockFloat
    9/25/2009 3:19:54 PMINSERT INTO BlockFloat (Identifier, DEA_ID, Block_ID, Value, MeasurTime) VALUES(5,1,122,22,9/25/2009 3:19:54 PM)
    Unhandled Exception: System.Data.SqlClient.SqlException: Incorrect syntax near '3'.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at PETTrendDB.PETTrendDBConcrete.InsertMyXMLData2(String str1, String str2, String str3, String str4,  DateTime str5) in C:\Documents and Settings\singan10\Desktop\MS SQL\PETTrendDB\PETTrendDBConcrete.cs:line 83
       at XmlClassTest.Program.deserialize() in C:\Documents and Settings\singan10\Desktop\MS SQL\PETTrendRemObj\PETTrendRemObj\Program.cs:line 64
       at PETTrendRemObj.PETTrendRemObject.Work() in C:\Documents and Settings\singan10\Desktop\MS SQL\PETTrendRemObj\PETTrendRemObj\PETTrendRemObject.cs:line 204
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.runTryCode(Object userData)
       at System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode code, CleanupCode backoutCode, Object userData)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()

    C:\Documents and Settings\singan10\Desktop\MS SQL\PETTrendRemObj\PETTrendServer\bin\Debug> else if (message.Item is TrendDataBlockFloat)
    Thursday, October 15, 2009 2:42 PM
  • You need to put single quotes around all string and datetime values in your sql query.

    Change the following code from:
    nonqueryCommand.CommandText = @"INSERT INTO BlockFloat (Identifier, DEA_ID, Block_ID, Value, MeasurTime) VALUES(" + arg1 + "," + arg2 + "," + arg3 + "," + arg4 + "," + arg5  + ")";
    To:
    nonqueryCommand.CommandText = @"INSERT INTO BlockFloat (Identifier, DEA_ID, Block_ID, Value, MeasurTime) VALUES('" + arg1 + "','" + arg2 + "','" + arg3 + "','" + arg4 + "','" + arg5  + "')";

    Note that the exception message says:
    Message type: TrendDataBlockFloat
    9/25/2009 3:19:54 PMINSERT INTO BlockFloat (Identifier, DEA_ID, Block_ID, Value, MeasurTime) VALUES(5,1,122,22,9/25/2009 3:19:54 PM)

    The expected thing is to have the following string sent back to SQL Server:
    INSERT INTO BlockFloat (Identifier, DEA_ID, Block_ID, Value, MeasurTime) VALUES('5','1','122','22','9/25/2009 3:19:54 PM')

    This requiement to have a quote around the value is only true for string and datetime based variables. You don't have to do this for other data types.

    • Marked as answer by Yichun_Feng Wednesday, October 21, 2009 1:08 AM
    Thursday, October 15, 2009 5:05 PM
    Moderator
  • Use command parameters instead of variables in your SQL statement (which is inherently risky). Below is an example:

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Yichun_Feng Wednesday, October 21, 2009 1:08 AM
    Thursday, October 15, 2009 5:49 PM
  • One more addition. I believe VALUE is a reserved word and you need to wrap it into square brackets inside of SQL statement. If you do not do this, you might receive an error as well. You actually can wrap all the names, something like

    nonqueryCommand.CommandText = @"INSERT INTO [BlockFloat] ([Identifier], [DEA_ID], [Block_ID], [Value], [MeasurTime]) VALUES(........
    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by Yichun_Feng Wednesday, October 21, 2009 1:08 AM
    Friday, October 16, 2009 10:41 AM
    Moderator