How To Easily Change Connection To Access Database Created Using "Add New Data Source" And Draging Table To Form

How To Easily Change Connection To Access Database Created Using "Add New Data Source" And Draging Table To Form

• SeanKornish

  Hi Christophe,

  I'm glad to hear you're making progress!

  As far as Crystal Reports, I'm not at all familiar with those controls, so unfortunately I can't help there. If you don't get any responses from anyone else here, I would suggest starting a new thread in the Crystal Reports for Visual Studio forum: http://forums.microsoft.com/MSDN/ShowForum.aspx ForumID=77&SiteID=1.

  Thanks,
  Sarah

  
  
  

• NOX

  When I run the application with the Load message replaced by:

  CrystalReport1.SetDataSource(Employee_DatabaseDataSet)

  The application stops running at that line and reports the following error:

  CrystalDecisions.CrystalReports.Engine.LoadSaveReportException was unhandled
  Message="Invalid report file path."
  Source="CrystalDecisions.Shared"
  

  This is the only data set that my application has.

  Christopher Lusardi

  Sarah Parra - MSFT wrote:

  I can give you a guess, but keep in mind I have never used the Crystal Reports controls and am not at all familiar with them. It looks like the ReportDocument class has a SetDataSource method, which would correspond to the code that you include in the other poster's response. If you already have a filled DataSet in your application, you can just set it here. I assume you have filled the DataSet somehow, based on your earlier posts. By default, the VS wizards will add an instance of your DataSet class to your form, then will add a Fill call in the Form_Load. So, let's say you have a DataSet instance called northwindDataSet, and it's being filled in Form_Load. Then I think you could just modify your code like this to bind it to the report: CrystalReport1.SetDataSource(northwindDataSet) I have no idea if you do this before or after the call to the Load method, or if you are even supposed to do this if you are already calling Load. Thanks, Sarah

  
  
  

• Andrew Choi

  It sounds like you want to use the DataDirectory keyword in your connection string. Visual Studio likes to do this by default, so I'm surprised it didn't get you like it does most people (see http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=427451&SiteID=1).

  Using DataDirectory, your connection string would look something like this:

  Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\northwind.mdb;Persist Security Info=True

  With a winforms application, |DataDirectory| means the same folder where your executable is. With an ASP.NET app, it means an app_data folder under your virtual root.

  If you are using a TableAdapter, you should be able to change this in Visual Studio. Open the xsd file for the adapter. It will give you a GUI display of your class, let's say EmployeesTableAdapter. Click on that in the designer (on the actual adapter, not the top part of the design, which is usually the table structure itself). Then in the properties for that, you should see the connection. This is usually coming from a config file. Expand the Connection tree, and you should see the ConnectionString. Change it to use DataDirectory like I showed above. You can also usually find this in the Settings.settings file in your project. This will map to the app.config. If you change settings.settings, it should update the app.config and vice versa, so I'm not sure what problems you were having when you tried to change it.

  You shouldn't need to change any code, as long as the schema isn't changing, and it's just the location of the MDB.

  Thanks,
  Sarah

  
  
  

• Sylvia Friedl

  I have been able to eventually display a report with the following code sequence. When I run the application, what the below will do is prompt me for a Login ID and password the first time I open the report. If I just click on cancell it will display the report. The second time I click to open the report it will not prompt me and will open the report immediately.        

          CrystalReport1.Load(Application.StartupPath & "\..\..\CrystalReport1.rpt")
          CrystalReport1.SetDataSource(Employee_DatabaseDataSet) 

  When I put a comma in the method SetDataSource, I get a message tip that says: Passes a recordset or Dataset to the report engine when using a 'Push Model'. For more information on Push and Pull models, see Data Access.

  When I VB search under help with "pull model", I do not see anything except abstract information.

  If I use just the first line above I get the Login prompt and I can't display the report. If I use just the second line above I get a run time error per previous post.

  If I change the Load to any of the next two lines nothing happens differently from just using the two lines above. VB tips tell me these new lines indicate wheather the report is to be opened as a temporary copy or by default.

  CrystalReport1.Load(Application.StartupPath & "\..\..\CrystalReport1.rpt", CrystalDecisions.Shared.OpenReportMethod.OpenReportByTempCopy)

  CrystalReport1.Load(Application.StartupPath & "\..\..\CrystalReport1.rpt", CrystalDecisions.Shared.OpenReportMethod.OpenReportByDefault)

  I cannot change the "Load" in any other way then I have described.

  But, if I put a comma in the argument list of the Load a "tip" message is displayed that says:

  1 of 3: Load (filename As String, openMethod As CrystalDecisions. Shared.OpenReportMethod)

  2 of 3: Load (filename As String, enterpriseSession As CrystalDecisions.Enterprise.EnterpriseSession)

  3 of 3: Load (filename As String, reportAppServer As String)

  Do you have any suggestions

   

  
  
  

• Michael Forkey MSFT

  You don't want to change the Settings.Designer.vb file (which is what I think you are referring to above). That file is generated by Visual Studio and may be overwritten, as you are seeing. You want to open the Settings.Settings file, but you don't have to open this file directly to do so (you can, but it's not required). You can do this by right-clicking on the project in the Solution Explorer, select Properties, then select Settings. If you change the connection string here, it will automatically regenerate the settings.designer.vb file.

  Alternatively, you can right-click the XSD file for the dataset that was generated by the wizard, then select View Designer. In the designer window, click on the tableadapter class. Usually you will see a box that has the table and it's columns on top (titled with the name of the table), and on the bottom it will have info about the adapter itself, usually named <tableName>TableAdapter, like EmployeesTableAdapter. You want to click on the bottom part, the adapter itself. Right-click and select Properties. One of the properties is Connection. If you expand this, you will see ConnectionString. If you change the string here, it will automatically be updated in the project Settings as well.

  Thanks,
  Sarah

  
  
  

• Conen

  When I go into the Settings.settings.vb file and change the only reference to the Access database to

  Global.System.Configuration.DefaultSettingValueAttribute("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""X:" & _

  "\Employees\Employees Database.mdb""")>

  and do a build clean, and a rebuild I still see the old database reference when I do a "find symbol" on Employees!

  What am I doing wrong

  
  
  

• MrWilliam

  (1) With this new database connection string, what do I do with the old data connection (it's the only one displayed) that is shown in the "server explorer" Can I just delete that

  The connections in Server Explorer are configured in the development environment, and are not tied to your project. If you create a new data source and select one of these connections, the connection information is copied into your project, and is no longer tied to the Server Explorer. Therefore, yes, you can delete any existing ones if you would like.

  
  (2) If I click on Edit, and Find Symbol with an argument of the database-name (only the location of the database has changed and not the database name itself) I am shown the old path to the database and not the new path. I think a variable expansion of some kind is being shown. Why is this I see 20 or 30 references to the old path. Shouldn't I see a variable expansion to the new database and not the old path

  Is your mdb file included in your project In other words, do you see it as a file in Solution Explorer When you are searching through Edit/Find Symbol, are you using the database name with the .mdb extension For example, if your database is northwind.mdb, are you using northwind.mdb or just northwind I'm trying to figure out which references are coming up for you, because in my case, if I type northwind.mdb, I get nothing (note that my mdb file is not actually included in the Project). If I use northwind, I get references to my DataSet, TableAdapter, etc because the wizard created my table names based on the MDB name. None of these references have anything to do with the path to my file, so I need more information on what you are seeing. Can you give some examples of where the old path references are coming from Are they in source files or some other configuration files

  Regarding using DataDirectory, you just use this right in your connection string. With the wizards, you should have very few copies of the connection string in your application (maybe one in the config and one in the Settings), and they should all be tied together so that if you edit one, VS automatically updates the rest. If you have copied the connection string yourself, that automatic update may not happen everywhere. Since you have so many references to the path as you have indicated above, your situation may be different. Since I don't know the context of those connections, I can't say for sure. If you can provide more information on where you see the old path, I might be able to better answer the question.

  To use DataDirectory, you just change the path in your connection string, and then you make sure the actual MDB file is in the same location as your executable.

  Let's say your connection string is in the application configuration and you can edit it using the project Properties/Settings as I mentioned in a previous post. Just go to that settings file, and change the connection string as follows:

  Here is my current string, with the full path hard-coded:

  Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\databases\northwind.mdb

  Here is my new string, using DataDirectory:

  Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\northwind.mdb

  That's it. Now it will look for northwind.mdb in the same directory as your executable.

  The benefit to using this is that you never have to change your connection string if you move the executable.

  Have you tried to do this and it's not working If not, test it out with a simple application and see how it works. Prove to yourself that you can move the executable and mdb file anywhere, even to a different machine, and verify that you can still connect to it. If you run into problems, post details here.

  Thanks,
  Sarah

  
  
  

• LegolasXVI

  I can give you a guess, but keep in mind I have never used the Crystal Reports controls and am not at all familiar with them.

  It looks like the ReportDocument class has a SetDataSource method, which would correspond to the code that you include in the other poster's response. If you already have a filled DataSet in your application, you can just set it here. I assume you have filled the DataSet somehow, based on your earlier posts. By default, the VS wizards will add an instance of your DataSet class to your form, then will add a Fill call in the Form_Load. So, let's say you have a DataSet instance called northwindDataSet, and it's being filled in Form_Load. Then I think you could just modify your code like this to bind it to the report:

  CrystalReport1.SetDataSource(northwindDataSet)

  I have no idea if you do this before or after the call to the Load method, or if you are even supposed to do this if you are already calling Load.

  Thanks,
  Sarah

  
  
  

• NWO

  Everything in the application appears to be working. What I was seeing in the Edit/Find Symbol were names to files in the project and not to any database name. It was even showing line numbers within the files. I guess it takes a little time to get used to a new programming environment.

  The keyword DataDirectory is working also. I just put the database.mdb in the \bin\Debug subfolder. But, when I  Load the Crystal Report it now prompts me for user ID and password! How can I get it to not ask for user ID and password Can you give me step by step details on how to do this I use the following:

  CrystalReport1.Load(Application.StartupPath & "\..\..\CrystalReport1.rpt")

  Thank you,

  
  
  

• Ted Strom

  Hello,

  Someone suggested to me a solution, but I'm at a loss as to how to proceed. Can you look at what he recommends and

  modify my code at the very bottom of this article per his suggestion, and post how you would accomplish the task  

  Thank you,

  ------------------------------------------------------------------------------------------------------------------------------

  Re: Loading Crystal Reports Report, How To Stop Prompt Enter Login ID And Password, This Never Happen Before Was this post helpful

  if your using vb.net load the data to the dataset create a report that uses the project dataset as the datasource. heres a sample dynamic b-day report in which the user is asked to enter the month the result of the Sp is stored in the dataset  Dim crs1 As New rpt_bday Me.SqlDataAdapter1.SelectCommand.Parameters("@month").Value = Me.ComboBox1.SelectedIndex + 1 Me.DataSet11.getbday.Clear() SqlDataAdapter1.Fill(DataSet11.getbday) crs1.SetDataSource(DataSet11) Me.CrystalReportViewer1.ReportSource = crs1

  '-----------------------------------------------------------------------------------------------------------------------------------------------------------

  'Here is my code that opens the report:

  Private Sub Open_Report_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Open_Report.Click

  Dim CrystalReport1 As New ReportDocument

  EMPLOYEEDataGridView.Visible = False

  EMPLOYEEDataGridView.Enabled = False

  Me.WindowState = FormWindowState.Maximized

  CrystalReportViewer1.Enabled = True

  CrystalReportViewer1.Visible = True

  CrystalReport1.Load(Application.StartupPath & "\..\..\CrystalReport1.rpt")

  CrystalReportViewer1.ReportSource = CrystalReport1

  CrystalReportViewer1.Zoom(150)

  Close_Report.Visible = True

  Close_Report.Enabled = True

  End Sub

  
  
  

• Alexandre Mineev MSFT

  I assume you will still need the Load call, or some form of it, so it knows which report to use. Then the DataSet provides the actual data. However, given that I am not familiar with these controls, I don't know the right combination of calls to make in order to get everything hooked up correctly. I think you have the correct DataSet, but clearly it also wants to know more information about the report. Maybe there is some way you can set the .rpt file without calling Load, or maybe you need to modify something in the rpt file so that it is not bound to a specific data source, but instead will use the DataSet that you provide.

  I promise you that you know more about these controls than I do (at least you've used them ), so I'm afraid I'm not going to be able to offer more help on this one. I think you will have better luck if you continue to pursue this with the posters on the Crystal Reports forum. They should have the expertise needed to help.

  Thanks,
  Sarah

  
  
  

• Alexander Stevenson

  I tested the next two lines by placing them before the load method, but I got the error listed below.

  'CrystalReport1.SetDatabaseLogon(user As String, password As String, server As String, database As String)

  CrystalReport1.SetDatabaseLogon("", "", "", Application.StartupPath & "\..\..\CrystalReport1.rpt")

  CrystalDecisions.CrystalReports.Engine.LoadSaveReportException was unhandled
  Message="Invalid report file path."
  Source="CrystalDecisions.Shared"
  StackTrace:
  ...

  I also tried moving the SetDatabaseLogon after the load message in the code, but I am still prompted for a Login ID and password.

  If I used non-null values with SetDatabaseLogon for Login ID, password, and database path the behavior did not change.

  If I used the overloaded SetDatabaseLogon with just the first two argument the behavior does not change.

  Chris Lusardi

  
  
  

• HWM

  Considering all the below questions what is the best way for me to go

  I want to do something that would allow me to never have to change the connection string. Can I keep the database with the executable and only move the folder containing both of them. Can you explain to me how to do that in a step by step process Will the keyword DataDirectory help. Explain to me, in a detailed way, how to use it

  Also, if I change the path of a database using the technique of right clicking on the project in the solution explorer, selecting properties, choosing settings, and changing the connection string there, the question I have for you is: How do I use the DataDirectory keyword mechanism to make the connection string point to the database in the folder with the executable I.E.: I would like to keep the database and executable in the same folder. Can you give me a step by step description of how to set the connection string using the DataDirectory mechanism

  Does using the DataDirectory keywork buy me much Can you elaborate on the good and bad things of using it How with a simple example how do I fix all the above problems

  Thank you,

  
  
  

• Psilo

  This technique (of right clicking on the projects in the solution explorer, selecting properties, and selecting settings, and changing connection string there) appears to work. For example, if I delete my old database file completely and change the connection string to a new one I am able to read/write to the new database. Thanks!

  But, I have questions for you that may or may not be cosmetic.

  (1) With this new database connection string, what do I do with the old data connection (it's the only one displayed) that is shown in the "server explorer" Can I just delete that

  (2) If I click on Edit, and Find Symbol with an argument of the database-name (only the location of the database has changed and not the database name itself) I am shown the old path to the database and not the new path. I think a variable expansion of some kind is being shown. Why is this I see 20 or 30 references to the old path. Shouldn't I see a variable expansion to the new database and not the old path

  Thank you,

  
  
  
• How To Easily Change Connection To Access Database Created Using "Add New Data Source" And Draging Table To Form