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)

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....

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



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.







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.







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.

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.

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

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!

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. 

??????

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!

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 ).

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

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

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!

No problem, glad you got it working