This tutorial will show how we can use Stored Procedures instead of explicit SQL statements to add data from a form to a database. VB version.
Using Stored Procedures instead of specific 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 references:
Imports System.Data.SqlClient
Imports System.Data |
If you're ever in the market for some great Windows web hosting, try Server Intellect. We have been very pleased with their services and most importantly, technical support.
In our Web.config, we declare the connection string:
<appSettings>
<add key="ConnString" value="Data Source=CLIENT-TASK2\SQLEXPRESS;Initial Catalog=BasicDataAccess;Integrated Security=True"/> </appSettings> |
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="Submit" /></td> </tr> </table> |
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!
The Stored Procedure will look something like this:
| ALTER PROCEDURE spInsertData
@name varchar(50),
@city varchar (50) AS
INSERT INTO [tblOne]
(theName, theCity)
VALUES (@name, @city)
RETURN |
I just signed up at Server Intellect and couldn't be more pleased with my Windows Server! Check it out and see for yourself.
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 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.AppSettings("ConnString")))
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 |
Download the Full Working Version of this Project written with Visual Studio.NET VB 2005 Here!
Looking for the C#.NET 2005 Version? Click Here!
Looking for more ASP.NET Tutorials? Click Here!