Error when databinding to SQL Server

[CHobbyist]

I am using 2005 and trying to use the data binding for the first time. I want to bind a DataGridView to my SQL database. I haven't used the feature since VB 6.0 and I can't get it working.

I think I'm supposed to use a BindingSource to connect to the database and then connect the DataGridView to the BindingSource. I thought BindingSource was the new DataSource component.

Anyway, I went into SQL 05 and setup the login account and gave full permissions to everything because I wasnt sure what was needed. Then I can't figure out how to put a connection string into the BindingSource. I guess I need to use the advanced dialog. It also wants the name of my SQL database file, mdf or something. I thought that was weird because I was always told that SQL was not a file-based database. I didn't even realize there was a file.

I did everything up how I thought it should be and I was getting access denied errors, until I created the new login account. Now I'm using the SQL Authentication instead of Windows Authentication and I'm getting a new error.

I posted a screen shot. Am I in the right place? I don't understand what I need to do. But I need to be able to change the connection string at run time, or store it in a config file, not go through this dialog.

I was hoping this method would be easier then writing all the data provider code. Data providers take a really long time to write.

:confused:

Thanks.

[jmcilhinney]

There's no such thing as binding to a database. Data-binding links local data to UI controls. Where that local data came from is of no consequence the the data-binding process.

So, you don't "put a connection string into the BindingSource". The BindingSource neither knows nor cares anything about connections. The data access and the data-binding are totally independent operations. Running and jumping are often used together but that doesn't mean that you have to run to jump, or jump to run.

The general principles are thus: you connect to SQL Server using an SqlConnection. You retrieve data into a DataTable using an SqlDataAdapter over that connection. Once you have the data you bind it to your controls for display and editing. Once the user has finished with the data you use the same SqlDataAdapter to save the changes in the DataTable back to the database.

The DataTable is the link there between the data access part and the data-binding part. The DataTable is where the data is put when it's retrieved from the database and also where it's taken from to save back to the database. What happens in between is of no concern to the data access code. The DataTable is also bound to the UI via the BindingSource. Where the data came from to be in the DataTable and where it goes afterwards is of no concern to the data-binding code.

Now, with the new data wizards in VS a lot of the detail is hidden from you. When you create a Data Source using the wizard it will generate a typed DataSet, which contains all the DataTables. It also creates a TableAdapter for each DataTable. The TableAdapters wrap the SqlConnections and the SqlDataAdapters. If you drag a table from your Data Source onto a form it will create the control(s) and the BindingSource for you. That doesn't change what goes on behind the scenes though. It just means that the IDE generates all the code for you.

[CHobbyist]

Hi, Thanks for responding. I was just going to post back before I went to bed. Didn't know anyone had replied.

I got a little farther, I am use to using in VB 6.0 when it updated the database automatically.

Plus I didn't understand what the mdf file is, if SQL isnt a file-based database.

I got it fixed now, I actually used a connection string, but I figured out that I had to have SMSE closed. I was getting access denied errors because the database file (mdf) was in use my SMSE and also because I didn't have permission. So once I run the IDE in admin mode and closed SMSE it worked. I'm not sure how the wizard used the connection string then because it gave me one at the end of the wizard and asked if I wanted the connection string stored in a configuration file. I checked yes and it created another DataSource Component and a DataBinding Component.

The next problem I ran into is that I made changes in the data grid vew, I added records. But when I closed the form the changes were never saved. I also made changes in the database with SMSE and they wern't loaded into the data grid the next time I ran the program.

So, I guess I need to create those data adapters and write the data to the database. Also when I add new columns to the database table with SMSE it doesnt update them in the program.

So I think the mdf file is just a "template" that tells the IDE all of the tables, fields, and other info that lets the data grid view make sure the correct type of data is in the column, but the actual data isn't stored in that file. So I need to completely write the database first, then reference the final mdf file into the IDE. Then when the form loads, I still have to manually query the database for all records, let the user modify them, and when the form closes write the changes back to the database. Its like that mdf file just makes sure I have the correct schema for the controls.

So looks like I'm still going to need to write all my sprocs and my database provider interface, and data providers manually. The binding components just help on the front end so I dont have to keep updating text boxes, and other contols manually with tons of code. If I have a lot of controls writing that code takes a while. I've done it before. :(

Is that right?

(I was wondering why Google didn't have any examples of how to bind to a database, all the examples bind to List<>.)

[jmcilhinney]

The MDF file is the data file. All databases have to store the data somewhere. Each SQL Server database is an MDF file. The server itself attaches those MDF files and then your app queries the server for the data. Your VB app never sees the MDF file; it just interacts with the server.

When you create an SQL Server database in your project it adds an MDF file to your project folder, along with all the other source files. When you compile your project a copy of that MDF file is made in the output folder, along with all the other output files. When you're debugging your app, adding updating and deleting data, you don't want to be making changes to your master database. If you did then you'd have to clear it out every time you wanted to start afresh or deploy your app. You mess about with a copy and your original stays in pristine condition, or at least it should if you don't go editing it in Management Studio. When it comes time to deploy your app a new copy of the clean master database is created for you to distribute.

I suggest that you read this: http://msdn2.microsoft.com/en-us/library/ms246989(VS.80).aspx

Note that while MS say that Copy If Newer is not recommended, that's just plain stupid. That should have been the default and is what most people end up using.

[CHobbyist]

So when you distribute a database file you should distribute the MDF file? I never even knew they existed before so when I distributed a database I always generated a SQL file with the schema and any default data, and had them create a new database on their server, and execute the SQL file. So I should send the MDF file instead? Then they can open up SMSE and then attach to that MDF file?

Also when I take a backup of my database I always just generate a SQL script from the current database. Should I just be making a copy of the MDF file and saving it on a disc or something instead? Is that a safer backup method?

The article has me confused on how it works. I understand that it points to the MDF file in my bin directory and modifies that instead of my real database, which is cool. When I go to deploy I'm assuming that the MDF file in my release bin is a current, fresh copy of the database, which is copied there everytime I compile. The one in the debug directory is the one that's used for testing. Is the one in the release folder copied on every build? Or do I just make the final copy myself just before I deploy?

The part that has me confused is because I'm use to working with VB 6 and in there I just maintained two databases dbase_master, dbase_design. I modified the design database, and when I deployed the app I just sent the master, and updated the connection string with the new catalog name. Some place there must be a connection string stored so it knows to use the MDF file in my debug/bin directory, so when I deploy the application that needs to be updated somehow to point to the new database server.

I'm going to play around with it, and see if I can figure it out.

Edit:
OK, I think I missed it. I see that Copy If Newer, is what makes it copy each time. If I use the other option Copy always then each time I build any test data I put into the database will be deleted.

This is it I think. I read it but it didn't sink in. :ehh:

Still not sure on how it knows where to find the master database when its deployed.

[jmcilhinney]

If you're distributing specifically for a local SQL Server Express instance then you can distribute the MDF file itself and use the connection string to attach that MDF file to the server on an ad hoc basis. This is a new feature in SQL Server Express.

If you're deploying for a remote SQL Server instance or any instance that is not Express then you cannot create user instances in that way. In that case you have to either attach an existing MDF file at the server, then connect as normal, or else you would have to do as you have been and use SQL statements to build the database upon install.

No matter how you do it, SQL Server stores its data in MDF files. It's just a matter of when and where they're created and attached to the server.

[CHobbyist]

OK, so I've been looking more and it looks like the wizard created a app.config file that I overlooked before. That file has a connection string in it that points to the MDF file in my bin directory. So when I deploy this software all I need to do is generate a SQL script of the database, and send that file. Then all I need to do is update the app.config file with the new connection string, or just put some invalid data like "<insert connection string here>" into the app.config file and allow the installer type the connection string themself. Most likely it will be the second option because I won't know any login details for their server, they can use intergrated or username and password.

I think I was missing that app.config file before, so I couldn't figure out how the program knew where the MDF file was.

So either way my code is the same, my IDataProvider, MSSQLDataProvider:IDataProvider and XMLDataProvider:IDataProvider have the exact same code, I just need to change that connection string on the release version.

I think I was expecting it to work like it did in VB 6 and that's why I was so confused.

I might take another look at this Linq too once I have this working. I think I need to reference that in my MSSQLDataProvider class, to make that class a little smaller. I'll figure it out I still have the article from my other thread on Linq to SQL.