By Doug Birtell - 8/21/2007
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)
|
By Trent L. Taylor - 8/21/2007
If the PrimaryKeyField is not auto-incrementing, then set the PrimaryKeyIsAutoIncrementing property on the BO to False and your error should go away. 
|
By Doug Birtell - 8/21/2007
Trent,The property you mention is already set to false. The strange thing is that I have a similar windows form that does essentially the same thiing and it works fine. I've compared the two forms, BO's, etc and can not see any difference that would cause this error. Thanks!
|
By Trent L. Taylor - 8/21/2007
Turn on debugging and see what is causing the problem in your statement. In the AppMain.vb or program.cs file, after the data source connections have been established, turn on the debug.MicroFour.StrataFrame.Data.Databasics.DataSources("").SetDebugOn("c:\MyDebug.html",True)
|
By Doug Birtell - 8/22/2007
Ok, (an old Java programmer) being new to strataframe (using C#), I'm not sure how to get the debugger turned on via code. I am unable to find any method in any MicroFour tablespaces that resembles SetDebugOn(...i.e. there is no method such as SetDebugOn within the DataSources class, or any other class that I can find. I'm sure it's somewhere, but I can't find it. ??? I can set breakpoints in the code, but am unable to step into the spot that causes the problem. MicroFour.StrataFrame.Data.Databasics.DataSources("").SetDebugOn("c:\MyDebug.html",True)
|
By Greg McGuffey - 8/22/2007
Doug,
This statement simply exports all the sql that is generated by the indicated datasource to the indicated file, in html format. I.e.
MicroFour.StrataFrame.Data.Databasics.DataSources("").SetDebugOn("c:\MyDebug.html",True)
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!
|
By Greg McGuffey - 8/22/2007
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).
|
By Doug Birtell - 8/22/2007
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. | 1 | | 2007-08-22 11:45:03.641 | | General Command Settings | | Text | | System.Data.SqlClient.SqlCommand | | Data Source=IST022246\SQLEXPRESS;Initial Catalog=FitWearDatabase;Integrated Security=True;Persist Security Info=False;Asynchronous Processing=True | | False | | Command Settings | | Select * from SampleMaster | | Command Parameters | | No parameters are defined |
| 2 | | 2007-08-22 11:45:03.922 | | General Command Settings | | Text | | System.Data.SqlClient.SqlCommand | | Data Source=IST022246\SQLEXPRESS;Initial Catalog=FitWearDatabase;Integrated Security=True;Persist Security Info=False;Asynchronous Processing=True | | False | | Command Settings | | select * from SampleMaster | | Command Parameters | | 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!
|
By Greg McGuffey - 8/22/2007
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).
|
By Doug Birtell - 8/22/2007
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
|
By StrataFrame Team - 8/22/2007
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.
|
By Doug Birtell - 8/23/2007
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;
}
}
}
|
By StrataFrame Team - 8/23/2007
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"]
|
By Doug Birtell - 8/23/2007
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
|
By StrataFrame Team - 8/23/2007
What do you get in the Watch window when you testboVariable.IsPrimaryKeyField("SampleNumber") ? It should return true... if it's not, then that's the root of our problem.
|
By Doug Birtell - 8/23/2007
Ben,I'm afraid I get true. see attached... Doug
|
By Greg McGuffey - 8/23/2007
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.
|
By Doug Birtell - 8/23/2007
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....
|
By Greg McGuffey - 8/23/2007
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.
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
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.
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
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.
|
By Doug Birtell - 8/24/2007
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.
|
By StrataFrame Team - 8/24/2007
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
|
By Doug Birtell - 8/24/2007
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!
|
By Doug Birtell - 8/24/2007
Ok, I've created a small app that has the following characteristics1. 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. ??????
|
By Doug Birtell - 8/24/2007
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!
|
By StrataFrame Team - 8/24/2007
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  ).
|
By Doug Birtell - 8/24/2007
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
|
By Doug Birtell - 8/24/2007
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
|
By Greg McGuffey - 8/24/2007
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! 
|
By StrataFrame Team - 8/27/2007
No problem, glad you got it working
|
|