Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to insert record into SQL Server database using Stored Procedure in ASP.NET

Author: Alanna Kremer
Download Source Code : 1315_InsertRecordUsingStoredProcedure.zip

Records can be inserted into SQL Server database by using SQL query in your ADO.NET code or you can write a stored procedure to insert records into database.

 

Stored procedures are the better way to insert, update and delete records in database because these are created and compiled in SQL Server. You need to call these stored procedures in your ADO.NET code with parameters.
 
In one of my previous article on DevASP.NET, I have explained that how you can call a stored procedure in your application. In this article, I will show how you can insert record into database using stored procedure. I have written a stored procedure in PUBS database to insert record into “authors” table and called this stored procedure in ADO.NET code.
 
Let’s see how we can do it. You can download source code.
 
  1. Open Visual Studio 2010
  2. File > New > Web Site
  3. Visual C# or Visual Basic > ASP.NET Empty Web Site > Click Ok
  4. Website > Add New Item > Web Form > Click Add
  5. Add code below in your Web Form

    <table>
            <tr>
                <td>
                Author ID:
                </td>
                <td>
                    <asp:TextBox ID="txtboxID" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                Last Name:
                </td>
                <td>
                    <asp:TextBox ID="txtboxLastName" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                First Name:
                </td>
                <td>
                    <asp:TextBox ID="txtboxFirstName" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                Phone:
                </td>
                <td>
                    <asp:TextBox ID="txtboxPhone" runat="server"></asp:TextBox>
                </td>
            </tr>
        </table>
            <asp:Button ID="btnInsertRecord" runat="server" Text="Insert Record"
                onclick="btnInsertRecord_Click" />
            <asp:Label ID="lblMessage" runat="server" ></asp:Label>

     
  6. Now let’s see our stored procedure

    CREATE PROCEDURE dbo.InsertRecord
          
           (
           @authorid varchar(11),
           @lastname varchar(40),
           @firstname varchar(20),
           @phone char(12),
           @contract bit
           )
    AS
    BEGIN
     
    INSERT INTO authors(au_id, au_lname, au_fname, phone, contract)
    VALUES(@authorid, @lastname, @firstname, @phone, @contract)
     
    END
     
    We will insert record into “authors” table in PUBS database. Although we have nine columns in “authors” table but we are defining five parameters in our stored procedure because other columns allow nulls. We have to mention column names in INSERT statement because we are not inserting values for all columns. If you want to insert values for all columns in a table than you can leave column names in INSERT statement but you must have to provide values according to the sequence of columns otherwise you will get unexpected result. 

     
  7. Open code behind file and include following namespace.

    C#
     
    using System.Data;
    using System.Data.SqlClient;
     
    VB.NET
     
    Imports System.Data
    Imports System.Data.SqlClient

     
  8. Write below function in code behind file.

    C#
     
    private void InsertRecord(string authorid, string lastname, string firstname, string phone, bool contract) {
        string constring = "Data Source=local;Initial Catalog=PUBS;Integrated Security=True";
        SqlConnection conn = new SqlConnection(constring);
        SqlCommand cmd = new SqlCommand("InsertRecord", conn);
        conn.Open();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("authorid", SqlDbType.VarChar, 11).Value = authorid;
        cmd.Parameters.Add("lastname", SqlDbType.VarChar, 40).Value = lastname;
        cmd.Parameters.Add("firstname", SqlDbType.VarChar, 20).Value = firstname;
        cmd.Parameters.Add("phone", SqlDbType.Char, 12).Value = phone;
        cmd.Parameters.Add("contract", SqlDbType.Bit).Value = contract;
     
        try {
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex) {
            throw ex;
        }
        Finally {
            conn.Close();
        }
    }
     
    VB.NET
     
    Private Sub InsertRecord(ByVal authorid As String, ByVal lastname As String, ByVal firstname As String, ByVal phone As String, ByVal contract As Boolean)
        Dim constring As String = "Data Source=local;Initial Catalog=PUBS;Integrated Security=True"
        Dim conn As New SqlConnection(constring)
        Dim cmd As New SqlCommand("InsertRecord", conn)
        conn.Open()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("authorid", SqlDbType.VarChar, 11).Value = authorid
        cmd.Parameters.Add("lastname", SqlDbType.VarChar, 40).Value = lastname
        cmd.Parameters.Add("firstname", SqlDbType.VarChar, 20).Value = firstname
        cmd.Parameters.Add("phone", SqlDbType.[Char], 12).Value = phone
        cmd.Parameters.Add("contract", SqlDbType.Bit).Value = contract
     
        Try
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            Throw ex
        Finally
            conn.Close()
        End Try
    End Sub
     
    The “InsertRecord” function takes five parameters for five columns in database table. Create an instance of SqlConnection and connect to SQL Server. Create a SqlCommand instance and pass the Stored Procedure name and connection string as parameters. Now mention CommandType as Stored Procedure and open connection. Add five parameters to SqlCommand object. You can see I have mentioned the parameter name, SqlDbType and size of the column. You also need to provide the value for each parameter. In “Try” block, call ExecuteNonQuery() method of SqlCommand object. At the end, close your connection in “Finally’ block.

     
  9. Now write code below in Button click event method in code behind file

    C#
     
    protected void btnInsertRecord_Click(object sender, EventArgs e) {
        string id = txtboxID.Text;
        string fname = txtboxFirstName.Text;
        string lname = txtboxLastName.Text;
        string phone = txtboxPhone.Text;
     
        InsertRecord(id, fname, lname, phone, false);
     
        lblMessage.Text = "Record inserted successfully";
    }
     
    VB.NET
     
    Protected Sub btnInsertRecord_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnInsertRecord.Click
        Dim id As String = txtboxID.Text
        Dim fname As String = txtboxFirstName.Text
        Dim lname As String = txtboxLastName.Text
        Dim phone As String = txtboxPhone.Text
     
        InsertRecord(id, fname, lname, phone, False)
     
        lblMessage.Text = "Record inserted successfully"
    End Sub
     
    In button click event, get the text of Textbox controls and call InsertRecod() method providing parameters. You can see I have provided the fifth parameter value as “false” because it is “bit” field in database table.
     
  10. Press F5 to see result in browser

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< Cross Page PostBack in ASP.NET

Disclaimer - Privacy
© 2002-2017 DevASP.net