Update keeps erroring

I'm just trying to update a table and keep getting a "WHERE" error when doing the updage.

MyTrackingLogItem.Update(12, "Bill", "Sue", true);

 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=1
  Number=156
  Procedure=""
  Server="S0013"
  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.ExecuteScalar()
       at SubSonic.SqlDataProvider.ExecuteScalar(QueryCommand qry) in C:\svn\subsonicproject\trunk\SubSonic\DataProviders\SqlDataProvider.cs:line 451
       at SubSonic.DataService.ExecuteScalar(QueryCommand cmd) in C:\svn\subsonicproject\trunk\SubSonic\DataProviders\DataService.cs:line 533
       at SubSonic.ActiveRecord`1.Save(String userName) in C:\svn\subsonicproject\trunk\SubSonic\ActiveRecord\ActiveRecord.cs:line 185
       at WebApp.MyTrackingLogItem.Update(Int32 varUniqueID, String varItemType, String varItemName, Nullable`1 varRemoved) in c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\the_insidertesting\302f7287\9ce3ecf\App_Code.t2hiylsu.1.cs:line 32007
       at WebApp.MyTrackingLogClass.RemoveListItem(Object sender, ImageClickEventArgs e) in c:\WebProjects\MyTesting\operations\MyTrackingLog.aspx.cs:line 549
       at System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e)
       at System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.WebControls.ImageButton.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:

Any suggestions?

-Allen

 I had this same problem - traced the execution through and found that, as generated, the Update() code had an error in the sql command generator - it generates a command that only updates the columns marked as dirty, but depending on how you create your 'MyTrackingLogItem', there might be no columns marked as dirty..  If you trace right up to the error and then inspect the sql command string you'll see that it has a 'WHERE' with nothing after it.

I overrode BeforeUpdate in ActiveRecord.cs with the following:

    protected virtual void BeforeUpdate() // this was added by JSB
    {
      TableSchema.Table schema = GetSchema();
      TableSchema.TableColumnSettingCollection tcsc = GetColumnSettings();
      foreach (TableSchema.TableColumnSetting tcs in tcsc)
      {
        if (tcs.IsDirty)
        {
          TableSchema.TableColumn dirtyCol = schema.GetColumn(tcs.ColumnName);
          this.DirtyColumns.Add(dirtyCol);
        }
      }
    }

and that seemed to make it work correctly.

Thanks jkimbu,

it worked for me.

Regards,

Rui Jarimba

It works perfectly!

Before I had to change generated files automatically, according to http://forums.subsonicproject.com/forums/t/2642.aspx post which was inconvenient.

By the way - I'm very surprised why Update issue is still not fixed...

In official release I got "Incorrect syntax near the keyword 'WHERE'". There were no fields to update ,ie.:

exec sp_executesql N'UPDATE [dbo].[Companies] SET  WHERE [CompanyId] = @CompanyId; SELECT @CompanyId AS id',N'@CompanyId int',@CompanyId=43

In current Update didn't work at all - no command at all in query analyzer.

Regards

Bartek

I'm getting the same error message and making the above changes doesn't seem to work 100%. I don't get the "Incorrect syntax near 'WHERE'" and it update the "code" field but not the "IsDefault" field.

        Dim myTest As New DB.TestCode

        With myTest
            .Code= txtCode.Text.Trim
            .IsDefault = chkDefault.Checked

            If btnSaveCode.CommandName = "Update" Then
                .TestKey = btnSaveCode.CommandArgument
                .IsNew = False

            Else
                .IsNew = True
            End If

            .Save()
       End With

I guess I should also mention that TestKey is an identity (int) field in the table.

This is such a basic feature - I'm surprised that not more people have run into this issue.  Is there an official fix for this yet?

 Just a quick note to anyone with this problem. I ran into this problem after updating a project to SS 2.1.  The above override of BeforeUpdate worked great.  However, in cases when the DirtyColumns collection had values the override would result in duplicate DirtyColumns and would mangle the resulting SQL command. 

The solution was to clear the dirtycolumns collection at the beginning of the method, i.e. this.DirtyColumns.Clear();  Alternatively you can probably just check the count on DirtyColumns and only run the suggested override if it is empty.

Is this something that will be fixed in future release?

Thx, Lieven Cardoen

I'm seeing this same behavior when I do a subsonic.select....executesingle(of [classname]).

I solved the problem by adding the columns I wanted to update to the dirty columns collection manually.

myclass.dirtycolumns.add(myclass.mypropcolumn)