In "Express Essentials: Connecting Visual Studio 2005 to SQL Server Express" (www.sqlmag.com/Article/ArticleID/97216 ), I covered the basics of how to create a connection between Visual Basic (VB) 2005 Express Edition and SQL Server Express. Now you can use that connection to bind VB interface components to SQL Server database columns.
There are multiple ways to connect Visual Studio Express, and VB in particular, to SQL Server Express. In "Connecting Visual Studio 2005 to SQL Server Express," I used the VB interface features and Data Source Configuration Wizard because that's where most beginners would start. However, you can also connect your VB applications to SQL Server Express using code, without bothering with the wizard. That technique is a bit more advanced, but it has the benefit of making it easier to copy and modify your projects for other database uses. I'll cover those code-only techniques in a future Express Essentials column. But now let's complete our look at the wizard-driven connection method.
In my last column, you saw how to establish a basic connection using the Data, Add New Data Source menu option. Stepping through the wizard, you selected a SQL Server database source and then pointed the wizard to the database .mdf file that you wanted to use. (Requiring you to find the raw .mdf file is obviously a poor design choice, but that's what Microsoft did.) We selected the Northwind.mdf file, and that's where we left off in part 1.
After you select the .mdf file, the Data Source Configuration Wizard presents the Choose Your Database Objects dialog box, which lets you connect to specific tables, views, stored procedures, or functions. Let's continue with the example we were using by connecting to the Northwind Customers table: First, expand the Tables node, then select the Customers table and click Finish. The Data Sources window appears on the right side of the screen and shows a new DataSet object called NORTHWNDDataSet, which contains one Data Table called Customers. To see whether the new data object works, you can right-click the Customers DataTable and select the Preview Data option, which opens a window containing a rowset that shows the contents of the Northwind Customers table.
Next, you need to add some UI elements to your form to connect to your new DataSet. Open the VB Toolbox and expand the Data section. Drag the DataGridView control onto your blank form, then click the Tasks arrow and drop down the Choose Data Source prompt. Expand Other Data Sources, then Project Data Sources, then NORTHWNDDataSet, and select Customers. The Customers table metadata is brought back into the designer. Running the project will populate the DataGridView with the data from SQL Server Express's Northwind Customers table. You've successfully connected the VB Express DataGridView component to the SQL Server Express Northwind Customers table.