1. Introduction to SQLDataSource Control

In this article, we will see how to use the Server Side SQLDataSource Control. Also, we will have a look at GridView Control that displays the table rows. Have a look at the below page:

In the above picture, the combo box is showing the publisher’s name from

publishers

table of the

pubs database

. Below that there is a grid which displays the employee names from the same pubs database. When a user picks a name in the combo box, the grid redraws with a new set of Employees who works for the selected item. In this example we will do this using SQLDataSource Control and writing no piece of code.

2. Pubs and NorthWnd Database Setup

The downloadable source code has two SQL scripts. One is for the Pubs Database and the other one is for NorthWnd Database. After launching SQL Server 2005 or later versions, we can run these scripts in the query editor window. Once we are done, we had both Pubs and NorthWnd Database with us. This example uses the Pubs Database. These steps are shown in the video: 001_SetupDB.avi

3. Create New Website Project

In this step we create a new website and name it as DataSource. Most of us know how to create a web project. However, this step also captured as video and one can refer it to know how to create a new Website. Refer: 002_NewSolution.avi

4. Add DataSource Control & Configure

Refer the attached video: 003_AddDataSource for reference while reading the subsections below.

4.1 Place the Control

First, we need to connect to the pubs database. We can do that by using the

SQLDatasource Control

. From the toolbox expand the data group. We will find SQLDatasource from the expanded node. Then, drag & drop the control in the Design area of the web form. Once placed, access the Quick Task menu by clicking the small arrow button at the top and the select the link ‘Configure Data Source…’. The below Picture is shown for guiding.

4.2 Setup Database Connection

The ‘Configure Data Source…’ will bring a dialog to choose our data connection. Click the New Connection button to create a connection for the pubs database which was created in the previous step. In the Add Connection dialog, we type the server name in which the database is running. In our case it is localhost as the database is also running in the same machine. After giving username and password (sa account) for the SQL authentication, we have to select the Pubs Database from the database list. Finally, Click OK once we selected the Pubs Database.

4.3 The ConnectionString

Once this is done, we move to the next step in the wizard by clicking the Next button. Then, we need to name the connection string as ConnectionStr and click the next button. The check mark allows us to save the connection string to the web configuration file. Finally, we will click next to proceed.

4.4 Get Publishers Table to Combo Box

Now we are in ‘Configure Select Statement’ step. First, select the Publishers table from the Name Combo box. This is the table which will populate the name of the publishers in the combo box of our example. Once the Publishers table is selected, the wizard displays the list of columns in the list box which is below the combo box. We select

pubid

and

pubname

 in the columns and then click on the finish button.

Now, we configured the SQLDataSource. This control will get the data from the Publishers table and populates the Combo box control. All these steps are covered in the 003_AddDataSource video.

5. Link Combo Box and SQLDataSource Control

Refer the attached video: 004_AddCombobox.avi

Here, we will add a ComboBox Control to our Web Form and link it to the previously created SQLDataSource Control. Now, we have to expand the standard toolbox and drag and drop the

Dropdown List Control

(ComboBox Control) to the Web Form. Then from the Quick Task select the

Auto Post-back

  option. Our goal is to display the list of employees in the Grid Control for the publisher picked in the ComboBox Control. So, when we change the Combo Box selection, the browser sends the changed data to the Server and Server will take the appropriate action. We achieve this through Auto Post-back option.

Next, we select Chose DataSource from the Quick Task. From the dialog presented, we pick the SQLDataSource set up in the previous step and take the fields as shown in the below picture. After this step, the combo box displays the publisher’s name and also sends the publisher’s id to the server when the user selects a publisher from it.

6. Configure GridView to Display Employees

Now we will setup the Asp.Net GridView Control to show employees. When a user picks a publisher in the combo, the GridView Control displays all the employees of the selected publisher. Refer the videos 005_AddDataGrid.avi, 005a to configure the GridView explained in this step.

6.1 Employee Connection to GridView Control

Our ComboBox is ready! We need to set up the

GridView Control

for the Employee Table. So, first we should place it to our Web Form. To do that, we need to bring up the server explorer from the view menu. If the data connection is not available in the server explorer, create the one to our Pubs database. Once the connection is created, we expand the Table Folder and drag & drop the Employee Table to the design surface. Using Auto Format quick menu, we can set any style for our Grid Control.

6.2 ConnectionString for GridView

From the Quick task menu, select the

Configure DataSource

  menu option. From the displayed dialog select the connection string that was already configured by us. This means, select the connection, ConnectionStr. Click next and in the new page, select the employee table and select the columns except job_id and job_lvl

6.3 GridView SQLDataSource & Publisher’s Condition

Click the Where… button in the Configure Data Source dialog. The dialog shown below is displayed:

We have to perform all the six steps shown in the dialog box. Here, we added the where condition to the Employee selection used by the grid view. This where condition is dynamically changed by the combo box item publisher. Look at the SQL Expression:

[Pub_id] = @pub_id

. The @pub_id is the value passed to the server by the combo as it has the auto-post back set to true. Remember, the display field of the combo box is publisher name (Refer Previous step). But, actual value it stores along with that is a publisher id. So, when we select a publisher name in the combo box, ASP.NET sends the publisher id to the server. Here in the where condition, this @pud_id will get substituted with the combo selection.

6.4 Filter Fields for GridView Control

From the Quick list select Edit Columns options, and from the displayed dialog, select the fields from the Available Fields list and add it to the Selected field list box. The dialog is in the below picture. Selected fields are the one which will be displayed by the GridView Control.

7. Run the Web page Example

1. Right-Click the default.aspx (if the name is not changed)
2. Select View in Browser option.
3. From the displayed page, select different publishers.

Supporting videos for this article: Download
Source Code : Download

• Default Focus and Default Button in ASP.Net
• Calendar Control Properties Explained

Categories: Asp 2.0

Tags: GridView, SQLDataSource

This site uses Akismet to reduce spam. Learn how your comment data is processed.