Databases are powerful things and in today’s society, you can’t possibly stay away from them. Presenting them is a totally different animal though, the information has to look neat and not like a bunch of gibberish as we see it. To do so, we would need to connect to the database somehow. In this tutorial we will learn how to connect to a SQL server using Visual Basic and a SQLDataSource control.

Setup

If you have not already done so, open Visual Studio and start a new web project. The database table used for this tutorial can be found in the Northwind database. For further accessibility, we have included the database within the source code files at the bottom of the tutorial.

Here is a shot of what we will be building today.

The first thing we will look into is making the web page. The following code is for your default.aspx page:

Connecting the SqlDataSource Control to a Data Source:

The following example shows a connection to the SQL Server Northwind sample database using a connection string stored in the configuration element. We will first create a “Data Command” for the Delete, Insert,Select and Update functiona. (The default prefix is “@” for Parameter)

Parameter Names:

The data source control creates parameters automatically for the values passed in the IDictionary collections. For an insert operation, the data source control populates its InsertParameters collection with values from the name/value pairs in the Values collection. For an update operation, the data source control populates its UpdateParameters collection with values from the name/value pairs in the Keys, NewValues, and OldValues collections. For a delete operation, the data source control populates its DeleteParameters collection with values from the name/value pairs in the Keys and OldValues collections.

Create a Gridview and Bind to SqlDataSource1:

The CommandField class is a special field used by data bound controls such as GridView and DetailsView to display command buttons that perform delete, edit, insert, or select operations.

The following code behind performs an insert operation using the InsertCommand SQL string and any parameters that are in the InsertParameters collection.

Here we set the insert parameters for CategoryName and Description to input the specific strings needed. Once the program is ran, the following result will be presented in the browser:

A Few Last Words

There are various applications to today’s lesson; it only depends on the expected output or functionality. Learning how to control and manipulate databases is easy with ASP.NET and we intend to be there every step of the way to help! Thank you for being a valued reader and join us next time for additional database tutorials!

Download Source Files