StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » BusinessLayerException error message

BusinessLayerException error message

Edhy Rijo — Wed, 19 Sep 2007 13:56:34 GMT

Hi All,

I am testing on a simple form with a one field and when trying to save I got the error below:

[quote]BusinessLayerException
An error occurred while saving an the data to the server.
DataLayerSavingException
The INSERT statement conflicted with the FOREIGN KEY constraint "Relation_203". The conflict occurred in database "ATR", table "dbo.Buildings", column 'PK_Buildings'.
The statement has been terminated.
SqlException
The INSERT statement conflicted with the FOREIGN KEY constraint "Relation_203". The conflict occurred in database "ATR", table "dbo.Buildings", column 'PK_Buildings'.
The statement has been terminated.

Source : MicroFour StrataFrame Business
[/quote]

The table ServiceCalls is related to the table Buildings, but in the form only a ServiceCallsBO is in place.

I will appreciate if anybody could help me figure out what may be wrong in this case. I am new to SF, VB.NET & MS-SQL Express 2005. My background is with Visual FoxPro.

Thanks

RE: BusinessLayerException error message

Edhy Rijo — Wed, 19 Sep 2007 13:56:34 GMT

Hi Ben,

Once again, thanks for your input. Now I know how to get there in either case.

RE: BusinessLayerException error message

StrataFrame Team — Wed, 19 Sep 2007 13:00:14 GMT

We help along the referential integrity by setting foreign key values when you specify a relationship within the business objects, but we don't enforce referential integrity; that's the sole responsibility of the database.

As for using a dummy record, there are a couple of ways you could go about doing this. You could know the PK ahead of time, either because you manually assigned it (IDENTITY INSERT ON) and always use the same PK, or because you put a flag on the record and retrieve it when you start up the application. Either way you would have the PK in a shared variable or a constant somewhere that would allow you to assign the value within the SetDefaultValues() of a business object. So, records would start out bound to that dummy record unless the user chooses to assign them to a different building.

Like I said, though, you certainly don't have to use a dummy record, and if keeping track of the dummy record is a pain, then using 0 (which you would always know) would certainly work, too, but you would have to turn off the enforcing of your PK (cascading deletes/updates would still work, though because you have the relationship defined, turning off the enforcement only prevents the DB from throwing an exception when the relationship isn't followed).

RE: BusinessLayerException error message

Edhy Rijo — Wed, 19 Sep 2007 12:45:10 GMT

[quote][b]Ben Chase (09/19/2007)[/b][hr]Your only other option would be to enforce the relationship, but create a "dummy" building record that is the "catch-all" for the service calls that don't have a building. That way, you end up with a "<No Building Assigned>" building record and all of the service calls are still attached to a building, but might be attached to that one.[/quote]

Hi Ben,

Thanks for the confirmation and your new idea above, I do like it and I am considering implementing the dummy building record. If it is not too much to ask, could you elaborate a bit on this idea on how to accomplish this with SF? So far I know I can create the record manually with specific PK or add another field to flag this record as a dummy one, but then how to tell the ServiceCalls form to use that record for non assigned building? Is the answer by knowing ahead of time the PK or the flag field of the dummy record and setting it as default for the combobox lookup field of the ServiceCallsBO?

Also another question, does SF has anything to do with handling the enforcement of referential integrity or it is the sole responsibility of the database to handle that?

RE: BusinessLayerException error message

StrataFrame Team — Wed, 19 Sep 2007 09:04:58 GMT

If the ServiceCall is the child, but it does not necessarily have a building as it's parent, then yes, you changed the correct property. Your only other option would be to enforce the relationship, but create a "dummy" building record that is the "catch-all" for the service calls that don't have a building. That way, you end up with a "<No Building Assigned>" building record and all of the service calls are still attached to a building, but might be attached to that one.

It all makes sense on the way you want to query the records. If you want to join the ServiceCalls to the Buildings table, you will have to to an OUTER JOIN to get the ServiceCalls that don't have an assigned building. Where as, if you create the dummy record, both an INNER JOIN and an OUTER JOIN will retrieve the records. It's all up to you.

RE: BusinessLayerException error message

Edhy Rijo — Tue, 18 Sep 2007 15:51:19 GMT

[quote][b]Trent L. Taylor (09/18/2007)[/b][hr]Yeah, this goes back to my eariler point. First, you have a BO that represents the Buildings table I assume. This is the table that you are trying to update. So does the primary key field auto-increment or do you have to provide the primary key. Second, it is a child of ServiceCalls so the value that is in the PK_Buildings field violates the relationship. So the first thing to address is that the primary key is being auto-incremented by the server or provided by you somehow. Next, you may need to review the relationship (Relation_203) to see if a parent record is required. If so, then you may have to have a ServiceCalls record which the Buildings is associated with (I think this is less likely).[/quote]

For testing pupose I went to the Relation properties in SQLExpress Manager console and set "Enforce Foreign Key Constraint = No" and that allow the record to be saved, even thought I am not so sure what this setting may break .

Now to explain better my settings, I have a form to handle the ServiceCalls data with a ServiceCallsBO.vb class, I add just one field CustomerName and when trying to save I got the initial error in the first message.

ServiceCalls is a child table of the Building table, so eventually in this form I will have a combobox to lookup the building data, but so far I am just testing with a plain and simple field. All primary fields in both tables are auto-incremented.

To resume, in this case a ServiceCall record may have a Building record associated with it, but not necessarily, so is the changed I did above in the relation correct?

Thanks!

RE: BusinessLayerException error message

Trent L. Taylor — Tue, 18 Sep 2007 15:03:37 GMT

Yeah, this goes back to my eariler point. First, you have a BO that represents the Buildings table I assume. This is the table that you are trying to update. So does the primary key field auto-increment or do you have to provide the primary key. Second, it is a child of ServiceCalls so the value that is in the PK_Buildings field violates the relationship. So the first thing to address is that the primary key is being auto-incremented by the server or provided by you somehow. Next, you may need to review the relationship (Relation_203) to see if a parent record is required. If so, then you may have to have a ServiceCalls record which the Buildings is associated with (I think this is less likely).

RE: BusinessLayerException error message

Edhy Rijo — Tue, 18 Sep 2007 12:58:49 GMT

[quote][b]Trent L. Taylor (09/18/2007)[/b][hr]This is a message that is being returned by SQL Server. A foreign key relationship is setup on the table but you did not provide a primary key or a foreign key. You generally see this more on child records. But it is possible that you have a PK that is not autoincrementing and you did not provide a value for the PK.[/quote]

Hi Trent,

This is a bit confusing for me since I use xCase to design my data and relationship are usually created automatically. There are other tables which also has relations and does not generate this error.

Let me provide more detail about this table and its related table:

[codesnippet]

Main Table Name: ServiceCalls

PRIMARY FIELD: PK_ServiceCalls
FOREING FIELD: FK_Buildings

Lookup Table: Buildings

PRIMARY FIELD: PK_Buildings
[/codesnippet]

In the form, only the ServiceCalls data is used with a SF BO, I setup this form with the same procedure as the others I have done while testing and this is the only one giving me that error. Please notice in the image the relation detail for relation named Relation_203. Just so you know, these two tables has not records, and both Primary fields are AutoIncremented by the server.

RE: BusinessLayerException error message

Trent L. Taylor — Tue, 18 Sep 2007 11:28:13 GMT

This is a message that is being returned by SQL Server. A foreign key relationship is setup on the table but you did not provide a primary key or a foreign key. You generally see this more on child records. But it is possible that you have a PK that is not autoincrementing and you did not provide a value for the PK.