StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Updating BO causes Business layer exception

Updating BO causes Business layer exception

Doug Birtell — Mon, 27 Aug 2007 08:29:40 GMT

I have created a windows form that maintains a simple table in a SQL Server database. The first column of this table is also the primary key.

I can add new records to the business object (and underlying table) but when I try to update one of the existing records I get the following error. The problem table only was one column as the primary key and it is not an identity column (auto generated). Looking at the partial BO class, it appears that the correct column is being used as the primary key.

BusinessLayerException

An error occurred while saving an the data to the server.

DataLayerSavingException

Cannot create UPDATE command because the updating DataTable does not contain columns for all PrimaryKeyFields.

DataLayerException

Cannot create UPDATE command because the updating DataTable does not contain columns for all PrimaryKeyFields.

Source : MicroFour StrataFrame Business

Stack Trace:

at MicroFour.StrataFrame.Data.DataLayer.BuildUpdateInfo(DataTable UpdatingTable, Boolean Transactional, String TransactionKey)

at MicroFour.StrataFrame.Data.DataLayer.UpdateDataTableThread(Object ThreadParams)

at MicroFour.StrataFrame.Data.DataLayer.SaveByForm(DataTable TableToSave, Boolean Transactional, String TransactionKey)

at MicroFour.StrataFrame.Business.BusinessLayer.SaveByForm(Boolean Transactional, String TransactionKey)

at MicroFour.StrataFrame.UI.Windows.Forms.BaseForm.Save(Boolean Transactional, String TransactionKey)

at MicroFour.StrataFrame.UI.Windows.Forms.BaseForm.Save()

at MicroFour.StrataFrame.UI.Windows.Forms.MaintenanceFormToolStrip.cmdSave_Click(Object sender, EventArgs e)

at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)

at System.Windows.Forms.ToolStripButton.OnClick(EventArgs e)

at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)

at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)

at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)

at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)

at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)

at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)

at System.Windows.Forms.Control.WndProc(Message& m)

at System.Windows.Forms.ScrollableControl.WndProc(Message& m)

at System.Windows.Forms.ToolStrip.WndProc(Message& m)

at System.Windows.Forms.Control.ControlNativewindow.OnMessage(Message& m)

at System.Windows.Forms.Control.ControlNativewindow.WndProc(Message& m)

at System.Windows.Forms.Nativewindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

RE: Updating BO causes Business layer exception

StrataFrame Team — Mon, 27 Aug 2007 08:29:40 GMT

No problem, glad you got it working :)

RE: Updating BO causes Business layer exception

Greg McGuffey — Fri, 24 Aug 2007 11:18:01 GMT

Glad you got it working. I've had a few problems like that and they are frustrating...and like you, I learned a lot from the posts too! :D

RE: Updating BO causes Business layer exception

Doug Birtell — Fri, 24 Aug 2007 10:43:08 GMT

Trent, Greg, Ben,

I recreated the form, BO, etc from scratch, and all is well with the world. I have no idea why the first app had the problem with edit/save. Must have been some glitch someplace. Regardless, I learned a lot and thank each of you for your valued input.

Cheers,

Doug

RE: Updating BO causes Business layer exception

Doug Birtell — Fri, 24 Aug 2007 09:55:19 GMT

Ben,

Thanks for the explaination. That all makes sense now. I'll let you know how my re-write from scratch goes with the other problem child (edit/save exception).

Regards,

Doug

RE: Updating BO causes Business layer exception

StrataFrame Team — Fri, 24 Aug 2007 09:42:02 GMT

The reason you're getting the InvalidCastException is because SF does not initialize PK fields when you add a new row. When you add a new row, all of the other fields are initialized to default values so they won't be DBNull.Value, however, the PK field(s) is(are) left alone. So, you can either set the ReturnAlternateOnNull option for the pk and return String.Empty (just like you've been doing :)), or you can manually set the pk field to String.Empty in the SetDefaultValues() method (in the main code file of the business object). Either way would work... and then, like you mentioned before, just add either a required field or a custom business rule for the PK, and the user won't be able to enter a blank one into the database (which they would only be able to do once, since the PK has to be unique ;)).

RE: Updating BO causes Business layer exception

Doug Birtell — Fri, 24 Aug 2007 09:24:43 GMT

Ben,

At this point, I think I will throw away the "problem child" and try to create the form again from scratch and see if I get the same problem regarding the edit/save issue. I would still like to understand the problem I have when trying to add a new record and getting the casting exception.

Thanks!

RE: Updating BO causes Business layer exception

Doug Birtell — Fri, 24 Aug 2007 09:21:15 GMT

Ok, I've created a small app that has the following characteristics

1. Maintains a table that has 3 columns, the first of which is a varchar(10) and is the primary key.

2. The BO for this table has the PK set as required and does not allow null.

3. With this new app, I can edit and save records (of course...#@$%$#@)

4. I am unable to add new records via the form. The instant I click "New" I get the following exception thrown...

"Unable to cast object of type 'System.DBNull' to type 'System.String'."

Again, it acts like it is trying to add the record to the BO before the user can fill in any data via the form. The only way I've found around this exception is to set the BO to return alternate of String.Empty.

??????

RE: Updating BO causes Business layer exception

Doug Birtell — Fri, 24 Aug 2007 08:51:09 GMT

I am in the process of creating a small sample app of the problem. I'll keep you posted on how it goes, etc.

Thanks!

RE: Updating BO causes Business layer exception

StrataFrame Team — Fri, 24 Aug 2007 08:43:18 GMT

OK, do you have a small sample app that reproduces the problem? Say, one form, with your one business object? And just a screen shot of the table in the database so that I can see the structure. I'm just not seeing anything else that you've done wrong, so I'll probably have to step through it. Thanks :)

RE: Updating BO causes Business layer exception

Doug Birtell — Fri, 24 Aug 2007 08:27:37 GMT

Greg,

I'm tracking 100% with what you posted. The PK is entered by the user. I have the PK in the BO set as "required" and "does not allow null". I am using the strataframe maintenance form. When I go to add a new record, the exception "Unable to cast object of type 'System.DBNull' to type 'System.String'." is thrown on the PK immediately when I click the "new" button on the form. This is before I can add any data (PK) via the form. The only way to get around this exception (that I've found) is to set the BO to "return alternate on null" with a String.Empty. Doing this allows me to add new records via the form. But... as previously discussed, this is not an ideal way to handle a PK value.

It acts as though it is adding the record to the BO before the user is allowed to enter any data via the form.

Now... when I have an existing record with a PK populated in the form (and the BO obviously), and I click "Edit" and then immediately click "Save" without ever changing any data, I get the following...

"BusinessLayerException
An error occurred while saving an the data to the server.
DataLayerSavingException
Cannot create UPDATE command because the updating DataTable does not contain columns for all PrimaryKeyFields.
DataLayerException
Cannot create UPDATE command because the updating DataTable does not contain columns for all PrimaryKeyFields.

Source : MicroFour StrataFrame Business

I get this regardless of how I have the PK setup.

RE: Updating BO causes Business layer exception

Greg McGuffey — Thu, 23 Aug 2007 17:33:48 GMT

I wouldn't say terribly wrong...there's just lots of moving pieces and looks like you haven't figure out a couple of them :hehe:

OK, I'm guessing that error is returned at runtime, not compile time (i.e. it's an exception being thrown, not a compile error). The problem is likely that you aren't creating your PK and setting the value before you save the BO (or something like that). In the Event Handlers region of your BO (main file), there is the SetDefaultValues handler. This is an excellent place to set the value of the PK, if it can be set by your app. E.g.

[codesnippet]Private Sub SampleBO_SetDefaultValues()
'-- Set the PK value, in this case using a private method called GetNewSampleNumber (which would
' return a new unique sample number).
Me.SampleNumber = Me.GetNewSampleNumber()
End Sub[/codesnippet]

If the PK is set by the user, then you'll need to check that it is actually set. The easiest way is to just indicate that the field is required. Open the BO's designer, hit F4 to view the properties and look for the ReqiredFields property. You can easily set the PK field as required and they won't be able to save without it.

There is one more step you likely will want to take if the user is providing the PK: check that it is actually unique. There are a couple of ways to do this. The first is to check for a sql duplicate exception when saving...not the best way, but it can work. The better way (according a recent post about this very thing) is to check if it's unique using the ExecutScalar method. You'd do this in the CheckRulesOnCurrentRow event. E.g.

[codesnippet]Private Sub MyBo_CheckRulesOnCurrentRow(ByVal e As Microfour.StrataFrame.Business.CheckRulesEventArgs)
'-- Check that the value provide is unique (if one was provided)
Dim sampleNumberExists As Boolean
Using cmd As New SqlCommand()
cmd.CommandText = "If Exists(Select SampleNumber" _
& " From SampleMaster" _
& " Where SampleNumber = @sampleNumber)" _
& " Begin Select 1 End" _
& " Else" _
& " Begin Select 0 End"
cmd.Parameters.Add("@sampleNumber",varchar,50)
cmd.Parameters("@sampleNumber").Value = Me.SampleNumber
sampleNumberExists = CBool(Me.ExecuteScalar(cmd))
End Using

'-- If the sample exists, add a broken rule
Me.AddBrokenRule(SampleBOFieldNames.SampleNumber, "The Sample Number indicated is already in use.")
End Sub[/codesnippet]

Note I just typed in the code, so there might be some syntax/typing errors. Hopefully this helps you understand how to manage the PK.

Of course, this might have nothing to do with the original problem.

RE: Updating BO causes Business layer exception

Doug Birtell — Thu, 23 Aug 2007 16:20:26 GMT

I understand and agree totally about the PK not being null. I had the BO setup that way originally, but when I do, I get the following compile error. (see attached document)

The only way I could get it to work was to change the PK within the BO to "return alternate on null" with String.Empty.

I must be doing something terribly wrong....

RE: Updating BO causes Business layer exception

Greg McGuffey — Thu, 23 Aug 2007 11:22:37 GMT

The thing that pops out to me is that you are allowing NULLs in your PK (you have set to return an alternate value of an empty string if the db value is NULL). This may not be causing your problems here, but it has caused me problems in the past. First, while you can allows NULLs in a PK column, this is a bad idea. What will happen is that one record will be allowed a NULL value in the PK, then when a second record with a NULL PK added, it will fail. The record with a Null value can also get "lost" as most search criterion and joins on the PK won't include it. These can be tough bugs to hunt down (yep, I've done this :( )

You need to setup the PK field in the database to be the PK (unique values, clustered likely), then setup the BO such that it doesn't allow nulls at all. If you are generating the PK automatically, you can set its value in the SetDefaults event (I think that is the event name...it's one of the two events that are in a BO when you create it, in the main file). If the user is entering the PK, then you will likely need to check that it is unique in the other event in the BO, CheckBusinessRules (again, I think that is what it's called).

I doubt this is the causing the current problem, but thought I'd point it out.

RE: Updating BO causes Business layer exception

Doug Birtell — Thu, 23 Aug 2007 11:21:49 GMT

Ben,

I'm afraid I get true. see attached...

Doug

RE: Updating BO causes Business layer exception

StrataFrame Team — Thu, 23 Aug 2007 10:59:04 GMT

What do you get in the Watch window when you test

boVariable.IsPrimaryKeyField("SampleNumber")

? It should return true... if it's not, then that's the root of our problem.

RE: Updating BO causes Business layer exception

Doug Birtell — Thu, 23 Aug 2007 10:13:49 GMT

Ben,

I've attached a word document with screenshots that hopefully captures what you are wanting to see.

Most of what I see in the local varialbles of the BO looks ok. The exception being the DataSet value which is null. Hopefully this will make sense to you when you see the screenshots.

Thanks for all your help. I know it's something probably very simple that I've done, but I've not been able to find it.

Doug

RE: Updating BO causes Business layer exception

StrataFrame Team — Thu, 23 Aug 2007 09:06:55 GMT

When you're at that break point in the BeforeSave, check these values in the Watch window and let me know what you get:

boVariable["SampleNumber"]

and

boVariable.CurrentRow["SampleNumber"]

and

boVariable.CurrentDataTable.Columns["SampleNumber"]

RE: Updating BO causes Business layer exception

Doug Birtell — Thu, 23 Aug 2007 08:39:44 GMT

Ben, I understand what you are saying. Every where I look, I see the primary key field "SampleNumber" defined correctly, i.e. in the table, in the BO, etc.
I put a breakpoint in the BeforeSave() method of the business object and when I examine the primary key values, it is correct. I will attempt to attach screenshots of various pieces of this "puzzlement".

SampleBO.designer.cs

        private static string[] _PrimaryKeyFields = new string[] { "SampleNumber" };
        /// <summary>
        /// Gets the field or fields that comprise the primary key for the business object.
        /// </summary>
        /// <remarks></remarks>
        public override string[] PrimaryKeyFields
        {
            get
            {
                return _PrimaryKeyFields;
            }
        }


/// <summary>
/// SampleNumber
/// </summary>
/// <remarks></remarks>
[Browsable(false),
BusinessFieldDisplayInEditor(),
Description("SampleNumber"),
DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
public System.String SampleNumber
{
     get
     {
  object loValue;
  loValue = this.CurrentRow["SampleNumber"];
  if (loValue == DBNull.Value)
  {
      return String.Empty;
  }
  else
  {
      return (System.String)loValue;
  }
     }
     set
     {
  if (value != String.Empty)
  {
      this.CurrentRow["SampleNumber"] = value;
  }
  else
  {
      this.CurrentRow["SampleNumber"] = DBNull.Value;
  }
     }
}

        /// <summary>
        /// A PropertyDescriptor class used to Get and Set the value of the SampleNumber property.
        /// </summary>
        /// <remarks></remarks>
        private class Field_SampleNumber_Descriptor : MicroFour.StrataFrame.Business.FieldPropertyDescriptor
        {
            public Field_SampleNumber_Descriptor() : base("SampleNumber") { }
            private System.Type _PropertyType = typeof(System.String);
            public override object GetValue(Object component)
            {
                return ((SampleBO)component).SampleNumber;
            }
            public override void SetValue(Object component, object Value)
            {
                ((SampleBO)component).SampleNumber = (System.String)Value;
            }
            public override System.Type PropertyType
            {
                get
                {
                    return this._PropertyType;
                }
            }
            public override System.Type ComponentType
            {
                get
                {
                    return _ComponentType;
                }
            }
        }

RE: Updating BO causes Business layer exception

StrataFrame Team — Wed, 22 Aug 2007 14:50:58 GMT

Hehe, sorry for the delay, been digging at the pile on my desk :) OK, that error you're getting is because the business object's CurrentDataTable thinks that it does not the DataColumn for the field(s) that are part of the PrimaryKeyFields array. So, if the PrimaryKeyFields array contains "ID1" and "ID2", then the CurrentDataTable of the business object needs to have the columns for ID1 and ID2 (otherwise, the business object won't be able to send the primary key to the server to update the record, because the BO can't find the PK's column within its internal DataTable). What I would do it put a break point on the call to your BO.Save(), and in the Watch window, check the visualizer for the BO.CurrentDataTable property (by clicking on the little magnifying glass, you'll get a window with a grid that shows the contents of the table). Make sure that the table actually contains the column that is your primary key.

RE: Updating BO causes Business layer exception

Doug Birtell — Wed, 22 Aug 2007 14:41:00 GMT

Thanks Greg. It makes sense that it's some generated code someplace that is somehow confused about the primary key attributes of the table. I'm sure it's something simple that I've done, but after several days trying to find it, I decided to turn to the experts. Ben, any help you could provide would be greatly appreciated.

Regards,

Doug

RE: Updating BO causes Business layer exception

Greg McGuffey — Wed, 22 Aug 2007 13:44:37 GMT

Well, I think Ben will likely have to help you. It doesn't appear to be a SQL problem. I'm guessing the exception occurs when the data source item is attempting to create the SQL for the update, but I don't know enough about the internals to have any idea why it would fail (especially since it works when you add new fields).

RE: Updating BO causes Business layer exception

Doug Birtell — Wed, 22 Aug 2007 11:47:16 GMT

Greg,

Thanks for the explaination. I modified the code to log the sql commands as follows

MicroFour.StrataFrame.Data.DataBasics.DataSources[""].SetDebugOn("c:\\MyDebug.html", true);

And here is what the MyDebug.html file contains after getting the exception - BusinessLayerException
An error occurred while saving an the data to the server.
DataLayerSavingException
Cannot create UPDATE command because the updating DataTable does not contain columns for all PrimaryKeyFields.

Command #:	1
Timestamp:	2007-08-22 11:45:03.641
General Command Settings
Command Type:	Text
Object Type:	System.Data.SqlClient.SqlCommand
Connection String:	Data Source=IST022246\SQLEXPRESS;Initial Catalog=FitWearDatabase;Integrated Security=True;Persist Security Info=False;Asynchronous Processing=True
Transaction:	False
Command Settings
CommandText:	Select * from SampleMaster
Command Parameters
N/A	No parameters are defined

Command #:	2
Timestamp:	2007-08-22 11:45:03.922
General Command Settings
Command Type:	Text
Object Type:	System.Data.SqlClient.SqlCommand
Connection String:	Data Source=IST022246\SQLEXPRESS;Initial Catalog=FitWearDatabase;Integrated Security=True;Persist Security Info=False;Asynchronous Processing=True
Transaction:	False
Command Settings
CommandText:	select * from SampleMaster
Command Parameters
N/A	No parameters are defined

The table in question has only the first column defined as the primary key, and the Business Object partial code has the correct column as the primary key. (non auto-incremeting, and defined as such in the BO) I can add new records but when I try to edit/save a record (via a strataframe maintenance form) I get the above expection.

Thanks for the help!

RE: Updating BO causes Business layer exception

Greg McGuffey — Wed, 22 Aug 2007 10:16:54 GMT

Oh, and as to your confusion about SetDebugOn, this is a method of a DataSource, not the DataSources collection. The code is DataSources(""), which is using the default property of the DataSources class (the indexer in C#), which is referencing one of the DataSource items in the collection by it's key (in this case an empty string).

RE: Updating BO causes Business layer exception

Greg McGuffey — Wed, 22 Aug 2007 10:12:30 GMT

Doug,

This statement simply exports all the sql that is generated by the indicated datasource to the indicated file, in html format. I.e.

[codesnippet]MicroFour.StrataFrame.Data.Databasics.DataSources("").SetDebugOn("c:\MyDebug.html",True)[/codesnippet]

will write all the sql statements generated by the default data source, with the key of an empty string (DataSources("")) to the "c:\MyDebug.html" file. Typically you set this in the SetDataSources (pretty sure that is its name) method in AppMain. However, you can actually put it anywhere in code after the datasources are set.

When you open this file, you'll see every sql command sent (after debugging has been turned on). This way you can see if the commands being sent to the database are OK. It can really narrow down were to start looking for the problem.

Hope this helps!