How do you change the connection string of a DataSet?
We have been using MSAccess for our database, but have recently upgraded to SQLServer Express. I was able to successfully upsize the Access database and now all information resides on the server. In my VisualBasic program, the dataset itself has around 200 queries total spread out on multiple table adapters. The problem now is that none of these queries work because the dataset is bound to the Access table. I have changed connection string through code and am able to display the information, but am unable to interact with it until i get the dataset connection changed.
I would rather just change the connection of the dataset somehow rather than build a new dataset, reconnect all forms, and rewrite all tableadapter queries.
Thank you for any help that you can provide.
Hi,
Not knowing which versions of VB and SqlExpress you are using makes it difficult for me to give any practical help, especially as I'm not familar with using prior versions, but the following may be of some use to you.
I've been using VisualStudio2005Pro and SqlExpress2005 for some time, and if its practical for you to upgrade to Visual Studio Express etc.(which is now free), you may find your problem easier to solve. In VS2005, you can use the My.Settings to hold your connection string, which makes it much easier to control. I recently changed from using SqlExpress in User Instance mode to Server mode, which meant having to alter nearly 300 connection string references. By using the find and replace facility in VS2005 (Ctrl F) it took maybe five minutes max to make the changes, including changing the connection string in My.Settings.
You can then always access your database using a connection like dbConnection = New SqlConnection(MySettings.Name of your ConnectionString)
John
|||Thank you for the response. To give a little bit more information i am currently using SQLExpress 2005 and using Visual Studio 2005 Pro as well.
The dataset that i have created in the program that is bound to all of my fields i have done extensive work in developing. Each table adapter in the dataset has anywhere from 1 - 50 queries on it. Just a few days ago i was instructed to change our database from MSAccess to SQLServer. I upsized the data and had no problems. I then changed the ConnectionStrings in the VB program since i was unaware of a way to change the connectionstring of the dataset itself (when you create the dataset, it asks for type of database, and location). With the new connection strings at the top of each form, i am able to access all of the data when i go from form to form. The problem lies when i try to add, change, delete, etc data on a form. Since the add/update/delete/etc call the queries that are located in the table adapters, it is still trying to access the old MSAccess database because that dataset (and the table adapters) are bound to the Access database since creation. When i go in to "Configure DataSet With Wizard", it will let me hit "Previous" for a few screens until i get to the location where i chose the datasource. These fields now are darkened and will not allow me to change the location/type of database that the DataSet is bound to. If there is an easy way to directally change the DataSet bindings without having to re-create the entire dataset i would rather go that path. The other problem lies with the table adapter queries. I recently re-wrote the smaller program's dataset instead of trying to figure out a way to convert it when i realized that the SQL commands for the Table Adapters of MSAccess and SQLServer are so completely different. If i will need to rewrite the SQL queries either way, then i might as well just start from scratch with a new dataset that is bound to the server instead of access.
If there is any other information i can provide please let me know. Thank you so much for the response.|||
With my limited knowledge I'm afraid I am not going to be of much help to you.
When I started with VS2005, I made the conscious decision to not use the wizards when working with datasets, tableAdapters etc, purely so as to have better control. It was a lot more work as a beginner, but has paid off, I believe, in ease of maintaining things. The downside is I dont have the experience which could maybe help you.
I'm sorry I'm out of my depth with regards to your particular problem, however I had a look at the msdn site and found the following site http://msdn2.microsoft.com/en-us/library/76ah1sx7.aspx has some information which you may be able to use.
How to: Edit a Dataset: Edit a TableAdapter: Edit TableAdapter Queries. etc.
I hope you find what you need there. Sorry I cannot be of more assistance. Maybe some of the other readers of this site can be more helpful
Good Luck
John
|||
There is probably some way to do this using the Data Set Wizard, but I haven't quite figured it out. If you created the original DataSet using the wizard though, it actually saved the connection string it uses into your application Settings, so you can just modify the string there.
Just open the application properties and switch to the Settings page. You should see a connection string setting. Just change the connection string, but not the setting name. As long as everything else is exactly the same, table names ,etc., it should just work.
Let me know how it turns out.
Regards,
Mike Wachal
SQL Express team
-
Mark the best posts as Answers!
|||You have it exactally right on. I went into the settings and was able to change the string with out any difficulty. I am getting a few errors now but i think that they will be easy to resolve.
Server=ACSFRONTDESK\SQLExpress;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ALTDB.mdf;Database=ALTDB;Trusted_Connection=Yes;
Connection Failed;
SQLState: '42000'
SQL Server Error: 1801
Database 'c\.................\ALTDB.mdf' already exists.
Connection Failed;
SQLState: '42000'
SQL Server Error: 1832
Could not attatch file "C:\.................\ALTDB.mdf" as database 'ALTDB'.
From the looks of it, it is trying to copy it over for some reason. I see that in the connection string it says "AttachDbFilename" but i am not sure what it is for. I did delete that section out of the string and i still got the same message.
It is also not letting me log in to the database at all now giving me some a different error in the program that did have a successful connection:
An error occurred while retrieving the information for the database;
Can not open user default database. Login Failed.
Login failed for user.
All of the connections i have to the database are through TrustedConnection and Windows Authentication. As i said, this is the one connection that kept working even after the "expandos" droped out of the Managment Studio. I will keep playing with it to see if i can come up with anything else. If i am using the incorrect connection string please let me know. I have tried several different variations of the string and none have been successful.|||
If the database is already attached at the server you should not need to specify AttachDbFileName. This keyword is used to cause SQL to automatically attache the database when an application starts. I'd recomend using the following:
Server=ACSFRONTDESK\SQLExpress;Database=ALTDB;Trusted_Connection=Yes;
Mike
|||I have entered the new connection string. I am getting 1 error as i try to synchronize the DataSet and that is:
An error occurred while retrieving the information for the database;
Can not open user default database. Login Failed.
Login failed for user.
This i am sure i can get rid of once i get the database back up and running properly. Thank you so much for your time and help in resolving this issue.
|||
Hope this post is not out of place, but I have a question for Mike.
Using SqlExpress, I use the following connection string:
Data Source= .\SqlExpress;Initial Catalog=Bradview;integrated Security = True which is somewhat different to the example you showed above. Does it do exactly the same thing.?
John
|||
Too many Johns on this thread! 
The two are functionally equivalent as far as I can tell. 'Data Source' specifies the same information as 'Server' and 'Database' specifies the same information as 'Initial Catalog'. I have not worked out what the difference is between these syntaxes, but they are used interchangably in my experience.
There is probably some eceedingly technical reason for these different keywords. I welcome comments from anyone who might know what they are.
Mike
|||
Thanks Mike,
The explanation does make me feel better. I've been trying to help where I think I can add to someone's knowledge, expecially a beginner like myself, but decided I was probably doing more harm than good, so have kept my mouth shut lately (so to speak).
I'd also be interested to know the differences in the keywords, if it's a major point of difference.
There can never be too many Johns.!! <grin>
Thanks again
John
|||One more question about this matter. As i said i used the connection string that you provided me Mike and it works perfect on one of the 2 programs. The other program however errors out and says that i need to include a 'Provider' in the connection string. I did this with a few different 'Providers' and have now come across a weird thing.
The SQL statments inside my DataSet (each table adapter allows you to Add Query) between the 2 programs are now vastly different. I will show some different syntax.
Access Insert:
INSERT INTO `OwnerInfo` (`OwnerDescription`, `Name`, `Address1`, `Address2`, `City`, `State`, `AmtDue`, `Credit`, `Phone`, `Zip`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
SQL Server Insert (no 'Provider'):
INSERT INTO [dbo].[OwnerInfo] ([OwnerDescription], [Name], [Address1], [Address2], [City], [State], [AmtDue], [Credit], [Phone], [Zip]) VALUES (@.OwnerDescription, @.Name, @.Address1, @.Address2, @.City, @.State, @.AmtDue, Credit, @.Phone, @.Zip);
Hybrid (error and requires 'Provider'):
INSERT INTO [dbo].[OwnerInfo] ([OwnerDescription], [Name], [Address1], [Address2], [City], [State], [AmtDue], [Credit], [Phone], [Zip]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
My question is what 'Provider' should i use so that all of my SQL is the same? Both programs are inserting data into the exact same server into the exact same table. Is the 'Provider' even the source of the SQL difference? I am trying to get a standard set so that it will be easy to change/update in the future. One of the programs is completely finished (the one with no 'Provider' - SQL Server Insert example) and would like to finish changing the other one to match the same syntax as the previous. Any ideas?
