Search This Blog

Wednesday, October 5, 2011

ASP.net using a form to insert data into an sql server Database table

  creating  Simple Form in Asp.net inserting data into  Database Table in SQl server

STEP1: Creating the Table in Database.
Create table in SQL using Mangment studio and Solution Explorer , Expand the database Right click on table -> click on new  table , create your column as per your requirement like the following.

 


Note: in this article, The Id is set to auto increment so that the id will be automatically generated for every new added row. To do this select the Column name “Id” and in the column properties set the “Identity Specification” to yes.

Then after adding all the necessary fields, name your Table the way you like. 
STEP2: Setting up the UI.
  Create a web form like following. 


ASPX:

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Sample Registration Pagetitle>
    <style type="text/css">
        .style1
        {
            width: 100%;
        }
    style>
head>
<body>
    <form id="form1" runat="server">
    <div>
   
        <table class="style1">
            <tr>
                <td>
                    Full Name:td>
                <td>
                    <asp:TextBox ID="TxtName runat="server">asp:TextBox>
                td>
            tr>
            <tr>
                <td>
                    Username:td>
                <td>
                    <asp:TextBox ID="TxtUserName" runat="server">asp:TextBox>
                td>
            tr>
            <tr>
                <td>
                    Password:td>
                <td>
                    <asp:TextBox ID="TxtPassword" runat="server" TextMode="Password">asp:TextBox>
                td>
            tr>
            <tr>
                <td>
                    Re Password:td>
                <td>
                    <asp:TextBox ID="TxtRePassword" runat="server" TextMode="Password">asp:TextBox>
                td>
            tr>
            <tr>
                <td>
                    Address:td>
                <td>
                    <asp:TextBox ID="TxtAddress" runat="server">asp:TextBox>
                td>
            tr>
            <tr>
                <td>
                    Age:td>
                <td>
                    <asp:TextBox ID="TxtAge" runat="server">asp:TextBox>
                td>
            tr>
            <tr>
                <td>
                    Gender:td>
                <td>
                <asp:DropDownList ID="DropDownList1" runat="server" AppendDataBoundItems="true">
                <asp:ListItem Value="-1">Selectasp:ListItem>
                <asp:ListItem>Maleasp:ListItem>
                <asp:ListItem>Femaleasp:ListItem>
                asp:DropDownList>
                td>
            tr>
        table>
   
    div>
    <asp:Button ID="Button1" runat="server" Text="Save" onclick="Button1_Click" />
    form>
body>
html>


As you can see, the UI is very simple. Now let’s set up the connection string.

STEP3: Setting up the Connection String

In your web.config file set up the connection string there as shown below:

<connectionStrings>
    <add name="MyConsString" connectionString="Data Source=localhost;Initial Catalog=MyDatabase;Uid=sa;pwd=sa@123"
   providerName="System.Data.SqlClient" />
  connectionStrings>

Note:
 MyConsString is the name of the Connection String that we can use as a reference in our codes for setting the connection string later.

STEP4: Calling up the ConnectionString in our codes

Here’s the method for calling the connection string that was set up in the web.config file.
 C#


  public string GetConnectionString()
    {
        //sets the connection string from your web config file "ConnString" is the name of your Connection String
        return System.Configuration.ConfigurationManager.ConnectionStrings["MyConsString"].ConnectionString;
    }
VB.net
Public Function GetConnectionString() As String
        'sets the connection string from your web config file "ConnString" is the name of your Connection String
        Return System.Configuration.ConfigurationManager.ConnectionStrings("MyConsString").ConnectionString
End Function

STEP5: Writing the method for inserting the data from the registration page to the database.

In this Article, we are using the ADO.NET objects for manipulating the data from the page to the database.
Here’s the code block for inserting the data to the database.

private void ExecuteInsert(string name, string username, string password, string gender, string age, string address)
    {
        SqlConnection conn = new SqlConnection(GetConnectionString());

        string sql = "INSERT INTO tblRegistration (Name, UserName, Password, Gender, Age, Address) VALUES "
                    + " (@Name,@UserName,@Password,@Gender,@Age,@Address)";
        try
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlParameter[] param = new SqlParameter[6];

            //param[0] = new SqlParameter("@id", SqlDbType.Int, 20);
            param[0] = new SqlParameter("@Name", SqlDbType.VarChar, 50);
            param[1] = new SqlParameter("@UserName", SqlDbType.VarChar, 50);
            param[2] = new SqlParameter("@Password", SqlDbType.VarChar, 50);
            param[3] = new SqlParameter("@Gender", SqlDbType.Char, 10);
            param[4] = new SqlParameter("@Age", SqlDbType.Int, 100);
            param[5] = new SqlParameter("@Address", SqlDbType.VarChar, 50);

            param[0].Value = name;
            param[1].Value = username;
            param[2].Value = password;
            param[3].Value = gender;
            param[4].Value = age;
            param[5].Value = address;

            for (int i = 0; i < param.Length; i++)
            {
                cmd.Parameters.Add(param[i]);
            }

            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Insert Error:";
            msg += ex.Message;
            throw new Exception(msg);

        }
        finally
        {
            conn.Close();
        }
    }

VB.Net
Private  Sub ExecuteInsert(ByVal name As String, ByVal username As String, ByVal password As String, ByVal gender As String, ByVal age As String, ByVal address As String)
        Dim conn As SqlConnection =  New SqlConnection(GetConnectionString()) 

        String sql = "INSERT INTO tblRegistration (Name, UserName, Password, Gender, Age, Address) VALUES "
                    Dim " (@Name,@UserName,@Password,@Gender,@Age,@Address)" As +
        Try
            conn.Open()
            Dim cmd As SqlCommand =  New SqlCommand(sql,conn) 
            Dim param() As SqlParameter =  New SqlParameter(6) {} 

            'param[0] = new SqlParameter("@id", SqlDbType.Int, 20);
            param(0) = New SqlParameter("@Name", SqlDbType.VarChar, 50)
            param(1) = New SqlParameter("@UserName", SqlDbType.VarChar, 50)
            param(2) = New SqlParameter("@Password", SqlDbType.VarChar, 50)
            param(3) = New SqlParameter("@Gender", SqlDbType.Char, 10)
            param(4) = New SqlParameter("@Age", SqlDbType.Int, 100)
            param(5) = New SqlParameter("@Address", SqlDbType.VarChar, 50)

            param(0).Value = name
            param(1).Value = username
            param(2).Value = password
            param(3).Value = gender
            param(4).Value = age
            param(5).Value = address

            Dim i As Integer
            For  i = 0 To  param.Length- 1  Step  i + 1
                cmd.Parameters.Add(param(i))
            Next

            cmd.CommandType = CommandType.Text
            cmd.ExecuteNonQuery()
        Catch ex As System.Data.SqlClient.SqlException
            Dim msg As String =  "Insert Error:" 
            msg += ex.Message
            Throw New Exception(msg)

        Finally
            conn.Close()
        End Try
End Sub

STEP6: Calling the method ExecuteInsert()

You can call the method above at Button_Click event for saving the data to the database. Here’s the code block below:

 protected void Button1_Click(object sender, EventArgs e)
    {
        if (TxtPassword.Text == TxtRePassword.Text)
        {
            //call the method to execute insert to the database
            ExecuteInsert(TxtName.Text, TxtUserName.Text, TxtPassword.Text, DropDownList1.SelectedItem.Text, TxtAge.Text, TxtAddress.Text);

            Response.Write("Record was successfully added!");

            ClearControls(Page);
        }
        else
        {
            Response.Write("Password did not match");
            TxtPassword.Focus();
        }
    }

VB.NEt
Protected  Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs)

        If TxtPassword.Text = TxtRePassword.Text Then

            'call the method to execute insert to the database

            ExecuteInsert(TxtName.Text, TxtUserName.Text, TxtPassword.Text, DropDownList1.SelectedItem.Text, TxtAge.Text, TxtAddress.Text)



            Response.Write("Record was successfully added!")



            ClearControls(Page)

        Else 

            Response.Write("Password did not match")

            TxtPassword.Focus()

        End If

End Sub


As you can see from the above code block, we check the value of the TxtPassword and TxtRePassword to see if match. If it match then call the method ExecuteInsert else display the error message stating that the “Password did not match”.

You also noticed that we call the method ClearControls for clearing the Text fields in the page. See the code block below for the ClearControls method:

public static void ClearControls(Control Parent)
    {
        if (Parent is TextBox)
        { (Parent as TextBox).Text = string.Empty; }
        else
        {
            foreach (Control c in Parent.Controls)
                ClearControls(c);
        }
    }
VB.net
Public Shared  Sub ClearControls(ByVal Parent As Control)

        If TypeOf Parent Is TextBox Then
          (Parent as TextBox).Text = String.Empty
        Else 

            Dim c As Control
            For Each c In Parent.Controls

                ClearControls(c)
            Next

        End If

 End Sub

4 comments :