Using Stored Procedures to Add to Database in VB

Quick Search

Navigator: Home - Advanced - 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.

Need help with Windows Dedicated Hosting? Try Server Intellect. I'm a happy customer!

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>

The ASPX page will look something like this:

<asp:Label ID="Label1" runat="server"></asp:Label><br />
<table>

<tr>

Name:</td>

<asp:TextBox ID="txtName" runat="server"></asp:TextBox></td>

</tr>
<tr>

City:</td>

<asp:TextBox ID="txtCity" runat="server"></asp:TextBox></td>

</tr>
<tr>

<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">

<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>

We moved our web sites to Server Intellect and have found them to be incredibly professional. Their setup is very easy and we were up and running in no time.

The Stored Procedure will look something like this:

ALTER PROCEDURE dbo.spInsertData

@name varchar(50),
@city varchar(50)

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

We migrated our web sites to Server Intellect over one weekend and the setup was so smooth that we were up and running right away. They assisted us with everything we needed to do for all of our applications. With Server Intellect's help, we were able to avoid any headaches!

Looking for the C# version? Click here

Looking for more .NET Database Tutorials? Click Here!

Download Project Source - Enter your Email to be emailed a link to download the Full Source Project used in this Tutorial!

100% SPAM FREE! We will never sell or rent your email address!