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:







But most likely, you have your required data source set to SQL Server instead of Access which is causing the issue.

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.

Hi Trent,

Thanks for that peice of information. It was going to be my next question!

Regards

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:







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.

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.

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.

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.

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

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:







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?

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.