The Code That causes the Problem is:







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.

First Off a maintenance form on the the BO that has the Data works fine and a maintenance form on the empty BO that will receive the data also works fine. So????? I guess the sync between the database and the BO's seem to be OK. Since both tables have exactly the same name but not the same structure I had to make sure the FillDataTable("Select * from secretarysystem.dbo.contestants") and the other was FillDataTable("Select * from rodeo.dbo.contestants") but then the maintenance forms worked great.







Now I moved the Me.ContestantsProRodeoBO1.Save() after the Next and before the End IF and put a Break Point on it. I looked at the Me.ContestantsProRodeoBO1.CurrentView in the visualizer and all the records are there with the ContPK filled in with -1...-1293 and the other fields filled in also.

BUT when I execute the Me.ContestantsProRodeoBO1.Save() I get this error(s):



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



Any Ideas?????? TIA

Hi Terry,



Move this code me.ContestantsPRCABO1.MoveNext() after the For..Next loop.



Also may I suggest any of these approaches for you to try:

Edhy Rijo

Edhy I tried your Option 1 and I looked at the BO where the data is being copied with the Visualizer and all of the data is there but when I execute the me.ContestantsProRodeoBO1.Save() and I get exactly the same message about certain fields being invalid.?????

I will try your 2nd option and let you know about it. Will only the fields that are identically named get copied? TIA. Thanks for your input.

Yes, that should be the result. Also try setting the BO.FieldsToExcludeFromInsert and BO.FieldsToExcludeFromUpdate with the field names that are giving you the error.

Edhy Rijo

Also remember to check the help file for more detail on using the methods.



Here is quote from the CopyDataFrom description in the help file:







So based on that quote, my 2nd option should work without the need to exclude any field.

Edhy Rijo

I tried the following code and the if me.ContestantsRodeoBO1.IsDirty is not true so then I put the statement Me.ContestantsRodeoBO1.Save() and it executes without error But it Does not Save anything.

I used the visualizer on me.ContestantsRodeoBO1.CurrentView and all of the data is there except the column ContPK which is the primary key of the BO that I am putting the data into. This column does not exist in the original BO and I guess that is why it does not show up in the copeid data. Therefore, I suppose that is why no data is saved.







I'm at a lost as to what to do??? TIA.

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



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:

Edhy Rijo

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.

Edhy Rijo

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

Nope, it does not.



Did you try it?

Edhy Rijo

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

Try this:



Just change: contestantsBO.CurrentRow.SetModified()



With contestantsBO.CurrentRow.SetAdded()



That should trigger an "Insert command instead of Update"

Edhy Rijo

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

Edhy Rijo

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.

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.

Edhy Rijo

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

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.

Edhy Rijo

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

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.

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.

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



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.

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.

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.

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

Edhy Rijo

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.

Terry,



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



Which BO is connected to witch database?

Edhy Rijo

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

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.

Edhy Rijo

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!

Edhy Rijo

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