StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Trying to Take Data from One BO to Another (Not the Same Structure)

Trying to Take Data from One BO to Another (Not the Same Structure)

Terry Bottorff — Tue, 06 Oct 2009 08:06:59 GMT

The Code That causes the Problem is:

[codesnippet]
If Me.ContestantsPRCABO1.MoveFirst Then
Dim howmany As Integer
howmany = Me.ContestantsPRCABO1.Count
MessageBox.Show(howmany.ToString, "Number of contestants")
Dim cnt As Integer
For cnt = 1 To howmany
Me.ContestantsProRodeoBO1.NewRow()

Me.ContestantsProRodeoBO1.RodeoID = Me.ContestantsPRCABO1.rodeoId
Me.ContestantsProRodeoBO1.MembershipCD = Me.ContestantsPRCABO1.membershipCD
Me.ContestantsProRodeoBO1.MembershipNumber = Me.ContestantsPRCABO1.membershipNumber
Me.ContestantsProRodeoBO1.Nationality = Me.ContestantsPRCABO1.nationality
...............
Me.ContestantsProRodeoBO1.Fee = 0
Me.ContestantsProRodeoBO1.EventBB = String.Empty
Me.ContestantsProRodeoBO1.EventBR = ""
Me.ContestantsProRodeoBO1.EventGB = ""
Me.ContestantsProRodeoBO1.EventRB = ""
Me.ContestantsProRodeoBO1.EventSB = ""
Me.ContestantsProRodeoBO1.EventSR = ""
Me.ContestantsProRodeoBO1.EventSW = ""
Me.ContestantsProRodeoBO1.EventTD = ""
Me.ContestantsProRodeoBO1.EventTR = ""
Me.ContestantsProRodeoBO1.Save()

Next

End If
[/codesnippet]

The Lines above the ................... work fine but the ones below Do Not. ????

In the New Table(where the data is going) I have contraints like the following:

ALTER TABLE [dbo].[Contestants] ADD CONSTRAINT [DF_Contestants_EventBB] DEFAULT ('') FOR [EventBB]

ALTER TABLE [dbo].[Contestants] ADD CONSTRAINT [DF_Contestants_SubjectToWithHolding] DEFAULT ((0)) FOR [SubjectToWithHolding]

CONSTRAINT [PK_Contestants] PRIMARY KEY CLUSTERED
([ContPK] ASC
)

The Error I get is a lengthy one:

MicroFour.StrataFrame.Data.DataLayerSavingException was unhandled by user code
Message="Invalid column name 'SubjectToWithHolding'. Invalid column name 'EventBB'. Invalid column name 'EventBR'. Invalid column name 'EventGB'. Invalid column name 'EventRB'. Invalid column name 'EventSB'. Invalid column name 'EventSR'. Invalid column name 'EventSW'. Invalid column name 'EventTD'. Invalid column name 'EventTR'. Invalid column name 'Fee'. Invalid column name 'ContPK'. Invalid column name 'ContPK'."
Source="MicroFour StrataFrame Business"
StackTrace:
at MicroFour.StrataFrame.Data.DataLayer.UpdateDataTable(DataTable TableToUpdate, Boolean Transactional, String TransactionKey)
at MicroFour.StrataFrame.Data.DataLayer.Save(DataTable TableToSave, Boolean Transactional, String TransactionKey)
at MicroFour.StrataFrame.Business.BusinessLayer.Save(Boolean Transactional, String TransactionKey)
at MicroFour.StrataFrame.Business.BusinessLayer.Save()
at DevExpressGridTwoDataBasesSF.Form1.Button3_Click(Object sender, EventArgs e) in C:\VSProjects\DevExpressGridTwoDataBasesSF\DevExpressGridTwoDataBasesSF\Form1.vb:line 53
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.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)
InnerException: System.Data.SqlClient.SqlException
Class=16
ErrorCode=-2146232060
LineNumber=1
Message="Invalid column name 'SubjectToWithHolding'. Invalid column name 'EventBB'. Invalid column name 'EventBR'. Invalid column name 'EventGB'. Invalid column name 'EventRB'. Invalid column name 'EventSB'. Invalid column name 'EventSR'. Invalid column name 'EventSW'. Invalid column name 'EventTD'. Invalid column name 'EventTR'. Invalid column name 'Fee'. Invalid column name 'ContPK'. Invalid column name 'ContPK'."
Number=207
Procedure=""
Server=".\sqlexpress"
Source=".Net SqlClient Data Provider"
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.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at MicroFour.StrataFrame.Data.DbDataSourceItem.InternalExecuteReader(DbCommand Command, Boolean IsTransactional, String TransactionKey)
at MicroFour.StrataFrame.Data.SqlDataSourceItem.UpdateRow(QueryInformation QueryInfo, DataRow RowToUpdate, ConcurrencyExceptionHandler ConcurrencyHandler, AddRowErrorHandler RowErrorHandler, Boolean RecreateCommand)
at MicroFour.StrataFrame.Data.DbDataSourceItem.UpdateRow(QueryInformation QueryInfo, DataRow RowToUpdate, ConcurrencyExceptionHandler ConcurrencyHandler, AddRowErrorHandler RowErrorHandler)
at MicroFour.StrataFrame.Data.DataLayer.UpdateDataTableThread(Object ThreadParams)
InnerException:

Any help would be greatly Appreciated. TIA.

RE: Trying to Take Data from One BO to Another (Not the Same Structure)

Terry Bottorff — Tue, 06 Oct 2009 08:06:59 GMT

I will spend a lot of time on this sample. I thank you very much. I know the help file is very good but I just could not find the pieces I needed to get thru this.
This forum is such a great place. Thank you all for hanging in there with me........

RE: Trying to Take Data from One BO to Another (Not the Same Structure)

Edhy Rijo — Mon, 05 Oct 2009 23:21:41 GMT

Hi Terry,

Here is a working project with not DevExpress grids, just regular datagridviews.

Here are some quick things to look at:

1.- AppMain.SetDataSources:
When using more than one database, you must setup the connection for each database and use the ConnectionManager.ApplicationKey and BO.DataSourceKey to properly identify each connection and database.

2.- Business Object:
In your case, you must set the DataSourceKey for each BO.

3.- In the Form:
You must load the data for ContestantsPRCABO1. check the code in the form.Load.

Since the data structure of both tables are not 100% equal, then don't use the BO.CopyDataFrom(), instead loop each record, assign the values, and since your Schema for the Rodeo database have NOT NULL like Greg pointed out before, you MUST assign a default value, or you will get errors when inserting records.

Keep in mind that this process will not check for duplicate records, so whenever you run it, it will insert a bunch of records again.

There is a lot to learn from this process, to avoid future frustration, you must take some time to dig deeper into the SF help file and the forum posts, all techniques I showed here in your project are well explain in both resources.

Good luck!

RE: Trying to Take Data from One BO to Another (Not the Same Structure)

Edhy Rijo — Mon, 05 Oct 2009 22:49:33 GMT

I got and I also reproduced the error. There are couple of thing missing about setting up the BOs connection to different databases. Will post working sample soon.

RE: Trying to Take Data from One BO to Another (Not the Same Structure)

Terry Bottorff — Mon, 05 Oct 2009 22:46:31 GMT

The ContestantsPRCABO is connected to the secretarysystem database and the ContestantsRodeoBO is connected to the Rodeo Database.

RE: Trying to Take Data from One BO to Another (Not the Same Structure)

Edhy Rijo — Mon, 05 Oct 2009 22:01:57 GMT

Terry,

You have 2 BOs and 2 Databases, and in the AppMain.vb only one connection.

Which BO is connected to witch database?

RE: Trying to Take Data from One BO to Another (Not the Same Structure)

Terry Bottorff — Mon, 05 Oct 2009 21:47:58 GMT

Edhy if you can see the grid you can delete it since I just had it on the form to see the original data but the data loads when the form loads so it really is not needed.

RE: Trying to Take Data from One BO to Another (Not the Same Structure)

Edhy Rijo — Mon, 05 Oct 2009 17:09:49 GMT

Sorry Terry, I don't have devExpress so I will not be able to test your sample project.

RE: Trying to Take Data from One BO to Another (Not the Same Structure)

Terry Bottorff — Mon, 05 Oct 2009 16:32:58 GMT

The two databases are secretarysystem and rodeo with secretarysystem.dbo.contestants having the data I want into rodeo.dbo.contestants.

The Visualstudio 2008 project is: DevExpressGridTwoDatabasesSF.

RE: Trying to Take Data from One BO to Another (Not the Same Structure)

Trent L. Taylor — Mon, 05 Oct 2009 15:17:19 GMT

Terry,

What I meant for you to post was an actual sample. We can bounce back and forth another 30 posts. This thread has gotten really long since you started it and you still do not have an answer.

If you can post a sample so that we do not have to try and get all of your structures and development environment in our heads without being able to actually see it, you will get some answers much more quickly.

This is a policy that we use even internally when we run into things. It helps lower the frustration level on all parties and the ultimate goal is helping you find an answer...but all of our standard suggestion have not seemed to help.

If you need to post an mdf of SQL backup and a small sample, not your actual full application, but a sample app with BOs that reproduces the issue, that should be your next step. Thanks.

RE: Trying to Take Data from One BO to Another (Not the Same Structure)

Greg McGuffey — Mon, 05 Oct 2009 10:55:24 GMT

Boy, there a lot of NOT NULLs in the target table :blink:

I think the SetDefaultValues is going to be your friend (either handle this within ContestantsRodeoBO itself, or add handlers in the form/code that is doing this work on the SetDefaultValues event of ContestantsRodeoBO1). You need to set default values for all the columns in the target table that won't be filled in. Usually when you use the BO to add a new row, it will deal with the PK automatically. Otherwise you'll have to deal with that as well.

RE: Trying to Take Data from One BO to Another (Not the Same Structure)

Terry Bottorff — Mon, 05 Oct 2009 10:16:33 GMT

This is the structure of the BO that Has Data:
CREATE TABLE [dbo].[Contestants](
[rodeoId] [int] NOT NULL,
[membershipCD] [char](1) NOT NULL,
[membershipNumber] [int] NOT NULL,
[contestantName] [varchar](50) NOT NULL,
[invertedName] [varchar](50) NOT NULL,
[city] [varchar](30) NULL,
[state] [varchar](10) NULL,
[creditCD] [char](1) NULL,
[circuitCD] [char](3) NULL,
[contestantId] [int] NOT NULL,
[subjectToWithholdings] [bit] NULL,
[nationality] [varchar](50) NULL,
[contestantGivenName] [varchar](50) NULL,
[contestantNumber] [int] NULL,

This is the Structure of the BO I want to Copy the Data TO and Save:
CREATE TABLE [dbo].[Contestants](
[ContPK] [int] IDENTITY(1,1) NOT NULL, This is My Primary Key
[RodeoID] [int] NOT NULL,
[MembershipCD] [char](1) NOT NULL,
[MembershipNumber] [int] NOT NULL,
[ContestantID] [int] NOT NULL,
[ContestantNumber] [int] NOT NULL,
[ContestantName] [varchar](50) NOT NULL,
[InvertedName] [varchar](50) NOT NULL,
[ContestantGivenName] [varchar](50) NOT NULL,
[City] [varchar](30) NOT NULL,
[State] [varchar](10) NOT NULL,
[CreditCD] [char](1) NOT NULL,
[CircuitCD] [char](3) NOT NULL,
[SubjectToWithHolding] [bit] NOT NULL,
[Nationality] [varchar](50) NOT NULL,
[EventBB] [char](2) NOT NULL,
[EventBR] [char](2) NOT NULL,
[EventGB] [char](2) NOT NULL,
[EventRB] [char](2) NOT NULL,
[EventSB] [char](2) NOT NULL,
[EventSR] [char](2) NOT NULL,
[EventSW] [char](2) NOT NULL,
[EventTD] [char](2) NOT NULL,
[EventTR] [char](2) NOT NULL,
[Fee] [numeric](9, 2) NOT NULL,

Now when I copy the data since the contpk is not in the original table it does not show up in the RodeoBO. Since it is my PK the Save does not work or at least that is the way I understand it. If you need more info please let me know. Thanks for your help.

RE: Trying to Take Data from One BO to Another (Not the Same Structure)

Trent L. Taylor — Mon, 05 Oct 2009 09:39:05 GMT

Terry,

I have been away for a while and as you have noticed haven't been out here. But I think that this is getting way more complicated that it needs to be.

First, from what I understand, you have two different BO structures and are trying to pass the data from one into the other. Is this correct?

Second, moving data between BOs is actually something that can be done easily and there are a few gotchas if the structure are different and you are trying to save. For example the BO has the AllFieldsList which is used by the BO when saving a record so it knows how to produce the UPDATE, etc. If you have columns that do not exist within the BO but are expected, then you would want to use the ExcludeFieldsFromUpdate/Insert on the BO to prevent the BO from trying to save column values that are not there.

Finally, if this doesn't get you going, the I recommend created a quick sample showing what you are trying to do so that there is no guess work as it will help you get an answer more quickly.

RE: Trying to Take Data from One BO to Another (Not the Same Structure)

Greg McGuffey — Mon, 05 Oct 2009 09:28:57 GMT

You might try using the SetDefaultValues event to get the work done. You might have to add a property to the ContestantsRodeoBO so it knows when to use this. I got busy and haven't been following this completely, so I might be way off...

RE: Trying to Take Data from One BO to Another (Not the Same Structure)

Edhy Rijo — Mon, 05 Oct 2009 08:16:14 GMT

You can setup a Bo without a primary key. Look in the help file or the forums for samples. I am not in the office today.

RE: Trying to Take Data from One BO to Another (Not the Same Structure)

Terry Bottorff — Mon, 05 Oct 2009 08:00:43 GMT

The ContestantsRodeoBO does have a PK in the database and it shows up in the Business Object Mapper called contpk. The ContestantsPRCABO does not have this field in it at all since it does not use a PK. I did not design it I just have to use it. So when the data gets copied no contpk field shows up in the ContestantsRodeoBO1 and therefore I can not set up a loop to fill it with -1. ?

RE: Trying to Take Data from One BO to Another (Not the Same Structure)

Edhy Rijo — Sun, 04 Oct 2009 22:22:01 GMT

[quote][b]Terry Bottorff (10/04/2009)[/b][hr]
I tried the above code and I get the following error:

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

[/quote]

Ok, lets clear up some things: ContestantsRodeoBO does have a PK field, but it is not the same as the ContestantsPRCABO1 pk field, right?

If so, when you copied the data, in the visualizer what is the value of the ContestantsRodeoBO PK field?, it should be -1, if not, then in the for..next loop, just set the value of the PK field to -1 and try again.

RE: Trying to Take Data from One BO to Another (Not the Same Structure)

Terry Bottorff — Sun, 04 Oct 2009 22:07:40 GMT

[codesnippet]
For Each contestantsBO As ContestantsRodeoBO In Me.ContestantsRodeoBO1.GetEnumerable()
'-- Change the CurrentRow state to make sure the record will be saved in the Activation process.
If contestantsBO.CurrentRow.RowState = Data.DataRowState.Unchanged Then
'contestantsBO.CurrentRow.SetModified()
contestantsBO.CurrentRow.SetAdded()
End If

Next

If Me.ContestantsRodeoBO1.IsDirty Then
Me.ContestantsRodeoBO1.Save()
End If
[/codesnippet]

I tried the above code and I get the following error:

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

RE: Trying to Take Data from One BO to Another (Not the Same Structure)

Edhy Rijo — Sun, 04 Oct 2009 17:44:42 GMT

[quote][b]Terry Bottorff (10/04/2009)[/b][hr]Well Edhy with your new code(Data.DataRowState.Unchange )
Cannot create UPDATE command because the updating DataTable does not contain columns for all PrimaryKeyFields.

Of course since the ContPK was not copied I know I need to somehow add it too the BO..[/quote]

Yes your contestantsBO must have the ContPK field so it will be properly updated.

RE: Trying to Take Data from One BO to Another (Not the Same Structure)

Edhy Rijo — Sun, 04 Oct 2009 17:41:14 GMT

Try this:

Just change: contestantsBO.CurrentRow.SetModified()

With contestantsBO.CurrentRow.SetAdded()

That should trigger an "Insert command instead of Update"

RE: Trying to Take Data from One BO to Another (Not the Same Structure)

Terry Bottorff — Sun, 04 Oct 2009 17:31:33 GMT

Well Edhy with your new code(Data.DataRowState.Unchange ) I now get this error which I think is a great improvement.

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

Of course since the ContPK was not copied I know I need to somehow add it too the BO. In your previous reply you mentioned "you may need to use Data.DataRowState.Added" but I can not seem to find any examples of just how that would work. Is there a sample somewhere???? TIA.

I may just be using the wrong search words to find an example. TIA.....

RE: Trying to Take Data from One BO to Another (Not the Same Structure)

Edhy Rijo — Sun, 04 Oct 2009 17:25:42 GMT

Nope, it does not.

Did you try it?

RE: Trying to Take Data from One BO to Another (Not the Same Structure)

Terry Bottorff — Sun, 04 Oct 2009 17:17:29 GMT

Does it make a difference that one BO is coming from one database and the other BO is in another database?

RE: Trying to Take Data from One BO to Another (Not the Same Structure)

Edhy Rijo — Sun, 04 Oct 2009 16:26:13 GMT

Also you may need to play with the value needed for each RowState, in my sample before I am using Data.DataRowState.Unchange but you may need to use Data.DataRowState.Added so your ContPK gets updated with a new autoincrement value.

RE: Trying to Take Data from One BO to Another (Not the Same Structure)

Edhy Rijo — Sun, 04 Oct 2009 16:20:53 GMT

[quote][b]Terry Bottorff (10/04/2009)[/b][hr]I'm at a lost as to what to do??? TIA.[/quote]

Don't worry, this is part of the learning curve. :D

I believe the problem is that the CopyDataFrom does not keep the RowState so you will have to loop one more time in the copied BO and change the RowState for all the records, so the Save() can actually save them. Something like this could do the trick:
[quote]
If Me.ContestantsPRCABO1.Count > 0 Then
'-- Copy data to Me.ContestantsRodeoBO1
Me.ContestantsRodeoBO1.CopyDataFrom(Me.ContestantsPRCABO1, MicroFour.StrataFrame.Business.BusinessCloneDataType.ClearAndFillFromCompleteTable)

'-- Loop the Me.ContestantsRodeoBO1 and change the RowState if needed
For Each contestantsBO As ContestantsRodeoBO In Me.ContestantsRodeoBO1.GetEnumerable()
'-- Change the CurrentRow state to make sure the record will be saved in the Activation process.
If contestantsBO.CurrentRow.RowState = Data.DataRowState.Unchanged Then
contestantsBO.CurrentRow.SetModified()
End If
Next

'-- Save the Record
If Me.ContestantsRodeoBO1.IsDirty Then
Me.ContestantsRodeoBO1.Save()
End If
End If
[/quote]