none
SubmitChanges() Errors with "Incorrect syntax near the keyword 'WHERE'." RRS feed

  • Question

  • from my db log I have:
    "

    A first chance exception of type 'System.FormatException' occurred in mscorlib.dll

    A first chance exception of type 'System.FormatException' occurred in mscorlib.dll

    A first chance exception of type 'System.FormatException' occurred in mscorlib.dll

    A first chance exception of type 'System.FormatException' occurred in mscorlib.dll

    A first chance exception of type 'System.FormatException' occurred in mscorlib.dll

    A first chance exception of type 'System.FormatException' occurred in mscorlib.dll

    A first chance exception of type 'System.FormatException' occurred in mscorlib.dll

    A first chance exception of type 'System.FormatException' occurred in mscorlib.dll

    SELECT * FROM dbo.PODPrintSpecification WITH (NOLOCK) WHERE PODPrintSpecificationID=@p0 AND PODPrintSpecificationGUID=@p1

    -- @p0: Input NVarChar (Size = 2; Prec = 0; Scale = 0) [12]

    -- @p1: Input NVarChar (Size = 36; Prec = 0; Scale = 0) [2f914d37-d7ac-49a3-8b52-2cc7bd840593]

    -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

    A first chance exception of type 'System.FormatException' occurred in mscorlib.dll

    SELECT TOP (1) [t0].[PODProjectTypeID], [t0].[PODProjectTypeGUID], [t0].[Value], [t0].[RecordStatus], [t0].[CreatedBy], [t0].[CreatedDate], [t0].[LastModifiedBy], [t0].[LastModifiedDate], [t0].[Version]

    FROM [dbo].[PODProjectType] AS [t0]

    WHERE @p0 = [t0].[PODProjectTypeID]

    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [2]

    -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

    SELECT TOP (1) [t0].[PODProjectTypeID], [t0].[PODProjectTypeGUID], [t0].[Value], [t0].[RecordStatus], [t0].[CreatedBy], [t0].[CreatedDate], [t0].[LastModifiedBy], [t0].[LastModifiedDate], [t0].[Version]

    FROM [dbo].[PODProjectType] AS [t0]

    WHERE @p0 = [t0].[PODProjectTypeID]

    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [2]

    -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

    SELECT [t0].[TableName], [t0].[ColumnName], [t0].[Description]

    FROM [dbo].[TableColumnDescriptions] AS [t0]

    WHERE [t0].[TableName] = @p0

    -- @p0: Input NVarChar (Size = 21; Prec = 0; Scale = 0) [PODPrintSpecification]

    -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

    SELECT [t0].[PODPrintSpecificationID], [t0].[PODPrintSpecificationGUID], [t0].[FinishingComments], [t0].[NumberUp], [t0].[Pages], [t0].[PrintingComments], [t0].[PODDieID], [t0].[PODProjectTypeID], [t0].[TrimLength], [t0].[TrimWidth], [t0].[RecordStatus], [t0].[CreatedBy], [t0].[CreatedDate], [t0].[LastModifiedBy], [t0].[LastModifiedDate], [t0].[Version]

    FROM [dbo].[PODPrintSpecification] AS [t0]

    WHERE [t0].[PODPrintSpecificationID] = @p0

    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [12]

    -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

    SELECT [t0].[PODPrintSpecificationPODSpecificationID], [t0].[PODPrintSpecificationPODSpecificationGUID], [t0].[PODPrintSpecificationID], [t0].[PODSpecificationID], [t0].[RecordStatus], [t0].[CreatedBy], [t0].[CreatedDate], [t0].[LastModifiedBy], [t0].[LastModifiedDate], [t0].[Version]

    FROM [dbo].[PODPrintSpecificationPODSpecification] AS [t0]

    WHERE [t0].[PODPrintSpecificationID] = @p0

    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [12]

    -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

    UPDATE [dbo].[PODPrintSpecificationPODSpecification]

    SET [RecordStatus] = @p2, [LastModifiedBy] = @p3, [LastModifiedDate] = @p4

    WHERE ([PODPrintSpecificationPODSpecificationID] = @p0) AND ([Version] = @p1)

    SELECT [t1].[PODPrintSpecificationPODSpecificationGUID], [t1].[CreatedDate], [t1].[Version], [t1].[PODPrintSpecificationPODSpecificationID]

    FROM [dbo].[PODPrintSpecificationPODSpecification] AS [t1]

    WHERE ((@@ROWCOUNT) > 0) AND ([t1].[PODPrintSpecificationPODSpecificationID] = @p5)

    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [65]

    -- @p1: Input VarBinary (Size = 8; Prec = 0; Scale = 0) [SqlBinary(8)]

    -- @p2: Input Char (Size = 1; Prec = 0; Scale = 0) [D]

    -- @p3: Input Int (Size = 0; Prec = 0; Scale = 0) [14]

    -- @p4: Input DateTime (Size = 0; Prec = 0; Scale = 0) [7/1/2009 8:27:38 AM]

    -- @p5: Input Int (Size = 0; Prec = 0; Scale = 0) [65]

    -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

    A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.Linq.dll

    SELECT [t0].[PODPrintSpecificationID], [t0].[PODPrintSpecificationGUID], [t0].[FinishingComments], [t0].[NumberUp], [t0].[Pages], [t0].[PrintingComments], [t0].[PODDieID], [t0].[PODProjectTypeID], [t0].[TrimLength], [t0].[TrimWidth], [t0].[RecordStatus], [t0].[CreatedBy], [t0].[CreatedDate], [t0].[LastModifiedBy], [t0].[LastModifiedDate], [t0].[Version]

    FROM [dbo].[PODPrintSpecification] AS [t0]

    WHERE [t0].[PODPrintSpecificationID] = @p0

    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [12]

    -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1
    "

    Before it Errors with:

    "
    System.Data.SqlClient.SqlException was unhandled by user code
      Message="Incorrect syntax near the keyword 'WHERE'."
      Source=".Net SqlClient Data Provider"
      ErrorCode=-2146232060
      Class=15
      LineNumber=36
      Number=156
      Procedure=""
      Server="ELITE-DEV2"
      State=1
      StackTrace:
           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.SqlDataReader.ConsumeMetaData()
           at System.Data.SqlClient.SqlDataReader.get_MetaData()
           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.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
           at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
           at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
           at System.Data.Common.DbCommand.ExecuteReader()
           at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)
           at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)
           at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
           at System.Data.Linq.ChangeDirector.StandardChangeDirector.DynamicUpdate(TrackedObject item)
           at System.Data.Linq.ChangeDirector.StandardChangeDirector.Update(TrackedObject item)
           at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode)
           at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)
           at System.Data.Linq.DataContext.SubmitChanges()
           at UserControls_PODSpecs.Save() in c:\Documents and Settings\jgold\My Documents\IMT\WebApplication\UserControls\PODSpecs.ascx.cs:line 121
           at ItemManage.Save_Click(Object sender, EventArgs e) in c:\Documents and Settings\jgold\My Documents\IMT\WebApplication\ItemManage.aspx.cs:line 665
           at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
           at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
           at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
           at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
           at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
           at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
      InnerException:

    "

    With the C# Code:

    "

     

    public void Save()

    {

     

    DA.db.Log = new DebuggerWriter();

    System.Collections.Specialized.

    NameValueCollection Errors = this.Save<PODPrintSpecification>();

     

    if (PODPrintSpecificationID > 0)

    {

    System.Collections.Generic.

    List<int> PODSpecificationIDs = new System.Collections.Generic.List<int>();

     

    var DropDowns = Request.Params.AllKeys.Where(p => p.EndsWith("PODSpecificationIDDropDown"));

     

    foreach (string dd in DropDowns)

     

    if (!Request.Params[dd].IsNullOrEmpty() && Request.Params[dd].IsNumeric())

    PODSpecificationIDs.Add(Request.Params[dd].ToInt());

     

    var PODSpecifications = Record.PODPrintSpecificationPODSpecifications.Where(s => s.RecordStatus != 'D');

     

    foreach (PODPrintSpecificationPODSpecification s in PODSpecifications.Where(s => !PODSpecificationIDs.Contains(s.PODSpecificationID)))

    {

    s.RecordStatus =

    'D';

    s.LastModifiedBy = (

    int)Session["LogOnUserID"];

    s.LastModifiedDate =

    DateTime.Now;

    }

     

    foreach (var PODSpecificationID in PODSpecificationIDs.Where(s => !PODSpecifications.Select(t => t.PODSpecificationID).Contains(s)))

    {

     

    PODPrintSpecificationPODSpecification s = new PODPrintSpecificationPODSpecification();

     

    //s.PODPrintSpecificationID = PODPrintSpecificationID;

    s.PODSpecificationID = PODSpecificationID;

    s.RecordStatus =

    'A';

    s.CreatedBy = (

    int)HttpContext.Current.Session["LogOnUserID"];

    s.CreatedDate =

    DateTime.Now;

    Record.PODPrintSpecificationPODSpecifications.Add(s);

    }

     

     

    DA.db.SubmitChanges();

    }

     

    }
    "

    Any Ideas? I don't see the problem in any of the SQL ... or my code.

    Wednesday, July 1, 2009 12:46 PM

Answers

All replies

  • What data type is PODPrintSpecification.PODPrintSpecificationGUID declared as? uniqueidentifier or nvarchar? If nvarchar, pass it as a guid/uniqueidentifier in the first query (which looks like a sql-in-a-string-query passed through datacontext.ExecuteQuery or similar).
    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    Wednesday, July 1, 2009 1:36 PM
    Answerer
  • its a uniqueidentifier  ... not the issue however gets past that code;  thats the line "

    System.Collections.Specialized.

    NameValueCollection Errors = this.Save<PODPrintSpecification>();"

    it has its own SubmitChanges() in the method and it does not error.

    Wednesday, July 1, 2009 1:53 PM
  • The error is in the sql .. But I am blind I am not seeing it:

    "

    DECLARE

     

    @p0 Int = 65

    DECLARE

     

    @p1 VarBinary (8) = (SELECT Version FROM PODPrintSpecificationPODSpecification WHERE ([PODPrintSpecificationPODSpecificationID] = @p0))

    DECLARE

     

    @p2 Char(1) ='D'

    DECLARE

     

    @p3 Int = 14

    DECLARE

     

    @p4 DateTime ='7/1/2009 10:59:52 AM'

    DECLARE

     

    @p5 Int = 65

    PRINT

     

    @p1

    UPDATE

     

    [dbo].[PODPrintSpecificationPODSpecification]

    SET

     

    [RecordStatus] = @p2, [LastModifiedBy] = @p3, [LastModifiedDate] = @p4

    WHERE

     

    ([PODPrintSpecificationPODSpecificationID] = @p0) AND ([Version] = @p1)

    PRINT

     

    'UPDATED'

    SELECT

     

    [t1].[PODPrintSpecificationPODSpecificationGUID], [t1].[CreatedDate], [t1].[Version], [t1].[PODPrintSpecificationPODSpecificationID]

    FROM

     

    [dbo].[PODPrintSpecificationPODSpecification] AS [t1]

    WHERE

     

    ((@@ROWCOUNT) > 0) AND ([t1].[PODPrintSpecificationPODSpecificationID] = @p5)
    "

    Returns
    "

    0x0000000000034DE7

    (1 row(s) affected)

    (1 row(s) affected)

    Msg 156, Level 15, State 1, Line 36

    Incorrect syntax near the keyword 'WHERE'.

    (1 row(s) affected)

    UPDATED

    (0 row(s) affected)


    "

    Wednesday, July 1, 2009 3:10 PM
  • Error is in our Audit trigger .. nothing to do with linq
    • Marked as answer by johntgold Wednesday, July 1, 2009 3:18 PM
    Wednesday, July 1, 2009 3:18 PM