By Ian Hammond - 5/28/2009
Hi,I am a little lost in the documentation, not being sure what I need to do in the following circumstance. I am trying to open an access daatabase so that I can copy the data to the defaulted sql server database which has a structure change. My points are as follows: 1) I'm trying to use the BOM to create BO on the access database, so far no luck (an earlier topic) 2) Assuming I can create a BO and link it in, I'm not sure how, progamatically, I can connect to the Access database. My application allows me to point to the database so that I can pass its full filepath to a conncetion string. Do you need to supply the entire connection string or are there Strataframe fuctions that would obtain the Povider to which I can append the remainder of the string. 3) What function should I use to add the database to the collection. I realise that I need to associate a predefined DataAccessKey to the connection which is carried onto the BO which will be used to obtain the data. I would appreciate any help, Many thanks
|
By Trent L. Taylor - 5/28/2009
1) I'm trying to use the BOM to create BO on the access database, so far no luck (an earlier topic)
I guess that you are referring to connecting to your access database so that you can map a schema. My guess here is that you are just stuck at this point. In which case all you need to do is supply a standard OLEDB connection string. SF actually has an AccessDbDataSourceItem class that is pre-plumbed to communicate with an Access database. So this process should be relatively straight forward once connected. When you are trying to connect, you just need to use the connection wizard (use the ellipsis button on the right of the OleDB provider), select the Access from the drop down list, then select your MDB. That should be all that is required to connect.
2) Assuming I can create a BO and link it in, I'm not sure how, progamatically, I can connect to the Access database. My application allows me to point to the database so that I can pass its full filepath to a conncetion string. Do you need to supply the entire connection string or are there Strataframe fuctions that would obtain the Povider to which I can append the remainder of the string.
The StrataFrame DataBase Connection Wizard will actually handle this for you. The same wizard available to you at run-time is also used within the BO Mapper. So if you get through that connection hurdle it should make more sense as you would just need to create another RequiredDataSourceItem in the SetDataSources in your AppMain.vb or program.cs file.
3) What function should I use to add the database to the collection. I realise that I need to associate a predefined DataAccessKey to the connection which is carried onto the BO which will be used to obtain the data.
I assume that if you are going down this route you do not want to use the connection string wizard. In this case just supply a valid OLEDB connection string manually to the DataSources collection:
MicroFour.StrataFrame.Data.DataBasics.DataSources.Add(New AccessDataSourceItem("MyAccessDataSourceKey","MyAccess OLEDB Connection String"))
|
By Ian Hammond - 6/7/2009
Thanks for the pointer. As an aside, I would like to be able to input the name of a database and make a connection outside of the normal program.cs SetDataSources method. The documentation seems to hint that you can do this. I have set a new DataSourcekey and used the DataBasics.Add method on the new file: DataBasics.DataSources.Remove(ConfigKey);DataBasics.DataSources.Add(new AccessDataSourceItem(ConfigKey, builder.ConnectionString.ToString())); but when I execute the fill command I get an exception Message="An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'."
I'm obviously missing something out. I'm assuming that this needs to be added to the ConnectionManager collection, but I'm not sure which method is required. Can anyone help, thanks.
|
By Trent L. Taylor - 6/8/2009
You are correct that you can specify a connection anywhere prior to actually needing the data source, and you can even change this on the fly if you have the need. It is just a matter of adding the data source to the DataSources collection:
MicroFour.StrataFrame.Data.DataBasics.DataSources.Add(New ...)
As for your exception, what does your connection string look like? The AccessDataSourceItem doesn't specify a connection string or OLEDDB provider, but rather is geared towards the data source type in question in how the queries, UPDATEs, INSERTs, etc. will interact with the data source. So the AccessDataSourceItem is written to support all of the query commands and so forth for an Access database. The connection string, however, must be a valid provider that is installed on the machine. Hope that makes sense.
In this case, I cannot see your connection string as it is tied up in the builder variable. What does your connection string look like?
|
By Ian Hammond - 6/8/2009
Hi Trent,I had made a mistake using the connection string method. I have replaced the call with: string provider = "Provider=Microsoft.Jet.OLEDB.4.0;";DataBasics .DataSources.Add(new AccessDataSourceItem(ConfigKey, provider + builder.ConnectionString.ToString()));where builder = Data Source="E:\HLS Pro.Net Development\HLS Pro Databases\Config.mdb";User ID=admin;Password=
When this runs, the fill command now gives the exception: Message="Unable to cast object of type 'System.Data.OleDb.OleDbConnection' to type 'System.Data.SqlClient.SqlConnection'."
|
By Trent L. Taylor - 6/8/2009
It would help if you provided a stack trace...somewher in your code you still have a reference to SQL connection. But a stack trace would help so I can see the point of failure and where it is coming from. Thanks.
|
By Ian Hammond - 6/8/2009
Does this help:System.InvalidCastException was unhandled by user code
Message="Unable to cast object of type 'System.Data.OleDb.OleDbConnection' to type 'System.Data.SqlClient.SqlConnection'."
Source="System.Data"
StackTrace:
at System.Data.SqlClient.SqlCommand.set_DbConnection(DbConnection value)
at System.Data.Common.DbCommand.set_Connection(DbConnection value)
at MicroFour.StrataFrame.Data.DbDataSourceItem.GetDataTable(DbCommand Command, OnChangeEventHandler CallBack)
at MicroFour.StrataFrame.Data.DataLayer.GetDataTable(DbCommand Command, Boolean RegisterNotification)
at MicroFour.StrataFrame.Business.BusinessLayer.FillDataTable(DbCommand CommandToExecute)
at ConfigUpdate.Business_Objects.ConfigBO.FillConfigInfo(String dbValue, String tblValue) in E:\HLS Pro.Net Development\ConfigUpdate\ConfigUpdate\ConfigUpdate\Business Objects\ConfigBO.cs:line 65
at ConfigUpdate.Forms.FindConfig.btnStart_Click(Object sender, EventArgs e) in E:\HLS Pro.Net Development\ConfigUpdate\ConfigUpdate\ConfigUpdate\Forms\FindConfig.cs:line 121
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)
many thanks.
|
By Ian Hammond - 6/8/2009
Hi Trent,I have just checked the database connections again and found that the database defined is the Stratframe database and not my exprected database. When I try and edit, the database control seems to be permanently disabled thus preventing me from selecting the appropriate database. In this appl;ication I have used the standard SetDataSource methiod to open the main sql database and I am selecting an access database from within a separate form from which I intend to copy data into the sql database. Not sure if this helps.
|
By Trent L. Taylor - 6/9/2009
Ian,
The Database Connection specified through the StrataFrame menu is for a StrataFrame database only which is used for design time. The Connection Wizard has the ability to disable this and we implemented this a long time ago to prevent people from changing this to their run-time database because it was a common forum issue.
I would suggest deleting the connections table and then starting over. Just delete the connections.dat and appkeys.dat. If you are running Vista you will find it here:
C:\ProgramData\MicroFour\ConnectionData
Once you delete these files, go back into Visual Studio and you will be prompted to connect to the StrataFrame database again. This may also clear out any "junk" you have been dealing with on your access database and allow you to start fresh.
|
By Ian Hammond - 6/10/2009
Hi Trent,Thanks for that peice of information. It was going to be my next question! Regards
|
By Ian Hammond - 6/10/2009
Hi,I have deleted the connection.dat and appkeys.dat files to reset the connections wizard. I have changed my SetDataSources method to open an Access database. This seems to work fine. When I try to execute a FillCommand in my BO I get the exception: InvalidCastException
Unable to cast object of type 'System.Data.OleDb.OleDbConnection' to type 'System.Data.SqlClient.SqlConnection'. Source : System.Data Stack Trace:
at System.Data.SqlClient.SqlCommand.set_DbConnection(DbConnection value)
at System.Data.Common.DbCommand.set_Connection(DbConnection value)
at MicroFour.StrataFrame.Data.DbDataSourceItem.GetDataTable(DbCommand Command, OnChangeEventHandler CallBack)
at MicroFour.StrataFrame.Data.DataLayer.GetDataTable(DbCommand Command, Boolean RegisterNotification)
at MicroFour.StrataFrame.Business.BusinessLayer.FillDataTable(DbCommand CommandToExecute)
at ConfigHLS.DetailsBO.FillDetailsByTableId(Int32 tableId) in E:\HLS Pro.Net Development\ConfigHLS\BusinessObjects\DetailsBO.cs:line 87
at ConfigHLS.Forms.DetailsForm.detailsBO1_ParentFormLoading() in E:\HLS Pro.Net Development\ConfigHLS\Forms\DetailsForm.cs:line 258
at MicroFour.StrataFrame.Business.BusinessLayer.raise_ParentFormLoading()
at MicroFour.StrataFrame.Business.BusinessLayer.OnParentFormLoading()
at MicroFour.StrataFrame.Business.BusinessLayer.RaiseParentFormLoadingEvent()
at MicroFour.StrataFrame.UI.Windows.Forms.BaseForm.InitializeFormLoadObjects()
at MicroFour.StrataFrame.UI.Windows.Forms.BaseForm.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.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)
Am I using the Connections Wizard incorrectly.
|
By Trent L. Taylor - 6/10/2009
Instead of using the connection wizard in your app just manually specify the connection. I don't think that you have your connection type setup as Access but rather SQL.
Remove the SetConnections() line and just add a manual connection string:
MicroFour.StrataFrame.Data.DataBasics.DataSources.Add(new AccessDataSourceItem(...))
But most likely, you have your required data source set to SQL Server instead of Access which is causing the issue.
|
|