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.

We used over 10 web hosting companies before we found Server Intellect. Our new server with cloud hosting,was set up in less than 24 hours. We were able to confirm our order over the phone. They responded to our inquiries within an hour. Server Intellect’s customer support and assistance are the best we’ve ever experienced.

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:
Code Block
Ask a Question
Download Source
Default.aspx
<table style="width: 426px">
<tr>
<td>
 
Category Name:</td>
<td>
<asp:TextBox ID="txtName" runat="server"></asp:TextBox></td>
<td>
</td>
</tr>
<tr>
<td>
Description:&nbsp;</td>
<td>
<asp:TextBox ID="txtDescription" runat="server"></asp:TextBox></td>
<td>
<asp:Button ID="Button1" runat="server" Text="Add" OnClick="Button1_Click" /></td>
</tr>
</table>

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.
Code Block
Ask a Question
Download Source
Default.aspx
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
    DeleteCommand="DELETE FROM [Categories] WHERE [CategoryID] = @CategoryID" InsertCommand="INSERT INTO [Categories] ([CategoryName], [Description]) VALUES (@CategoryName, @Description)"
    SelectCommand="SELECT [CategoryID], [CategoryName], [Description] FROM [Categories]"
    UpdateCommand="UPDATE [Categories] SET [CategoryName] = @CategoryName, [Description] = @Description WHERE [CategoryID] = @CategoryID">
    <DeleteParameters>
        <asp:Parameter Name="CategoryID" Type="Int32" />
    </DeleteParameters>
    <UpdateParameters>
        <asp:Parameter Name="CategoryName" Type="String" />
        <asp:Parameter Name="Description" Type="String" />
        <asp:Parameter Name="CategoryID" Type="Int32" />
    </UpdateParameters>
    <InsertParameters>
        <asp:Parameter Name="CategoryName" Type="String" />
        <asp:Parameter Name="Description" Type="String" />
    </InsertParameters>
</asp:SqlDataSource>

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.
Code Block
Ask a Question
Download Source
Default.aspx

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="CategoryID"
    DataSourceID="SqlDataSource1" Width="426px">
    <Columns>
        <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" CancelText="Cancel"
            DeleteText="Delete" EditText="Edit" UpdateText="Update" />
        <asp:BoundField DataField="CategoryID" HeaderText="CategoryID" InsertVisible="False"
            ReadOnly="True" SortExpression="CategoryID" />
        <asp:BoundField DataField="CategoryName" HeaderText="CategoryName" SortExpression="CategoryName" />
        <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />
    </Columns>
</asp:GridView>
The following code behind performs an insert operation using the InsertCommand SQL string and any parameters that are in the InsertParameters collection.  
Code Block
Ask a Question
Download Source
Default.aspx.vb

Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
SqlDataSource1.InsertParameters("CategoryName").DefaultValue = txtName.Text.ToString()
SqlDataSource1.InsertParameters("Description").DefaultValue = txtDescription.Text.ToString()
SqlDataSource1.Insert()
End Sub
 
 
Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting
 
End Sub
End Class
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!
SQL_Data_2010VB.zip

We used over 10 web hosting companies before we found Server Intellect. They offer dedicated servers, and they now offer cloud hosting!