Navigator: Home - Advanced - Using Stored Procedures to Add to Database in VB

Using Stored Procedures to Add to Database in VB

This tutorial will show us how we can use Stored Procedures instead of explicit SQL Statements to add data to our database. VB version.

Download the Full Working Version of this Project written with Visual Studio.NET VB Here!

Looking for the C# version? Click here

Looking for more .NET Database Tutorials? Click Here!

Using Stored Procedures instead of explicit SQL statements is very useful - one Stored Procedure can be referenced many times by many different pages, and changes only need to be made once, for example. Using Stored Procedures is barely more difficult to implement than using SQL statements in code.
First, we need the following assembly reference:

Imports System.Data.SqlClient

In our Web.config, we declare the connection string:

<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
</connectionStrings>

The ASPX page will look something like this:

<form id="form1" runat="server">
<asp:Label ID="Label1" runat="server"></asp:Label><br />
<table>
<tr>
<td style="width: 100px">
Name:</td>
<td style="width: 100px">
<asp:TextBox ID="txtName" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 100px">
City:</td>
<td style="width: 100px">
<asp:TextBox ID="txtCity" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td colspan="2">
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Add to DB" /></td>
</tr>
</table><br />

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="id" DataSourceID="SqlDataSource1" Visible="False" Width="608px">
<Columns>
<asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" ReadOnly="True"
SortExpression="id" />
<asp:BoundField DataField="theName" HeaderText="theName" SortExpression="theName" />
<asp:BoundField DataField="theCity" HeaderText="theCity" SortExpression="theCity" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT * FROM [Table1]"></asp:SqlDataSource>
</form>

The Stored Procedure will look something like this:

ALTER PROCEDURE dbo.spInsertData
@name varchar(50),
@city varchar(50)
AS
INSERT INTO Table1
(theName, theCity)
VALUES (@name, @city)
RETURN

The code-behind will look something like this:

Imports System.Data.SqlClient
Imports System.Data

Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If IsPostBack Then
GridView1.Visible = True
End If
End Sub

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cmd As New SqlCommand("spInsertData", New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString()))
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.AddWithValue("@name", txtName.Text)
cmd.Parameters.AddWithValue("@city", txtCity.Text)

cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()

Label1.Text = "<b>" & txtName.Text & "</b> has been added to the database using a Stored Procedure."
End Sub
End Class


Download the Full Working Version of this Project written with Visual Studio.NET VB Here!

Looking for the C# version? Click here

Looking for more .NET Database Tutorials? Click Here!
411asp.net123aspxDotNetFreaksServer Intellect