Wednesday, 6 March 2013

Registration form in asp.net using stored procedures

Registration form in asp.net using stored procedures

Introduction

Here I will explain how to insert data into database tables using Stored Procedure.

Database: 

Create Database:-

create Database sp_exp

create Table:-

create table student(id int identity(1000,1) primary key,name varchar(200),cell varchar(200),email varchar(200))

create Stored Procedure:-

create procedure sp_student_insert(@name varchar(200),@cell varchar(200),@email varchar(200))as
begin
insert into student(name,cell,email)values(@name,@cell,@email)
end

Aspx Page: 

<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
    CodeFile="Default.aspx.cs" Inherits="_Default" %>
<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <h2>
        Registration Form in asp.net using stored procedures.....!
    </h2>
    <div>
        <table>
            <tr>
                <td>
                    <asp:Label ID="Label1" runat="server" Text="name"></asp:Label>
                </td>
                <td>
                    <asp:TextBox ID="txtname" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    <asp:Label ID="Label2" runat="server" Text="mobile"></asp:Label>
                </td>
                <td>
                    <asp:TextBox ID="txtmob" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    <asp:Label ID="Label3" runat="server" Text="email"></asp:Label>
                </td>
                <td>
                    <asp:TextBox ID="txtemail" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                </td>
                <td>
                    <asp:Button ID="submit" runat="server" Text="Submit" OnClick="submit_Click" />
                </td>
            </tr>
            <tr>
                <td>
                </td>
                <td>
                    <asp:Label ID="lblerror" runat="server" ForeColor="Green"></asp:Label>
                </td>
            </tr>
        </table>
    </div>
</asp:Content>

C#.aspx code:-

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["con"].ToString());

    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void submit_Click(object sender, EventArgs e)
    {
        try
        {
            con.Open(); 
            string sql = "sp_student_insert";  
            SqlParameter[] par = new SqlParameter[3];  
            par[0] = new SqlParameter("@name", txtname.Text);
            par[1] = new SqlParameter("@cell", txtmob.Text); 
            par[2] = new SqlParameter("@email", txtemail.Text);   
            SqlCommand cmd = new SqlCommand(sql, con);  
            cmd.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter a in par)
            {
                cmd.Parameters.Add(a);
            }
            string UserId = Convert.ToString(cmd.ExecuteNonQuery());
            if (UserId.Length > 0)
            {
                lblerror.Text = "Data inserted successfully...!";
                txtemail.Text = string.Empty;
                txtmob.Text = string.Empty;
                txtname.Text = string.Empty;
            }
            else
            {
            }
        }
        catch (Exception ex)
        {
            lblerror.Text = ex.Message;
        }
        finally
        {
            con.Close();
        }
    }


}

Web.config:-

<connectionStrings>
        <add name="con" connectionString="Data Source=MAIN;Initial Catalog=sp_exp;Persist Security    Info=True;User ID=sa;Password=123" providerName="System.Data.SqlClient"/>
 </connectionStrings> 

output screen:-