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 database from GridView control in ASP.NET

Author: Mike Green
Download Source Code : 1351_InsertFromGVToDB.zip

I will explain in this article that how we can insert record into database from GridView control.

 

  1. Open MS Visual Studio 2010
  2. File > New > Website > Visual C# or Visual Basic > ASP.NET Empty Web Site
  3. Select Web Location as File System and Click OK
  4. From Menu, Website > Add New Item > Select Web Form and Click Add.
  5. Write below connection string in web.config file

    <connectionStrings>
           <add name="EmployeeDatabase" connectionString="Data Source=(Local);Initial Catalog=EmployeeDatabase;Integrated Security=True" providerName="System.Data.SqlClient"/>
    </connectionStrings>
     
  6. Write code below in default.aspx page

    <asp:Button ID="Button1" runat="server" Text="Add New Record"
             onclick="Button1_Click" />
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false"
                onRowCommand="GridView1_RowCommand">
                <Columns>
                    <asp:TemplateField HeaderText="Emp ID">
                        <ItemTemplate>
                            <asp:Label ID="lblEmpID" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "EmpID") %>'></asp:Label>
                        </ItemTemplate>
                        <FooterTemplate>
                            <asp:TextBox ID="txtboxEmpID" runat="server"></asp:TextBox>
                        </FooterTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="First Name">
                        <ItemTemplate>
                            <asp:Label ID="lblFName" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "FName") %>'></asp:Label>
                        </ItemTemplate>
                         <FooterTemplate>
                            <asp:TextBox ID="txtboxFName" runat="server"></asp:TextBox>
                        </FooterTemplate>
                    </asp:TemplateField>
                     <asp:TemplateField HeaderText="Last Name">
                        <ItemTemplate>
                            <asp:Label ID="lblLName" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "LName") %>'></asp:Label>
                        </ItemTemplate>
                         <FooterTemplate>
                            <asp:TextBox ID="txtboxLName" runat="server"></asp:TextBox>
                        </FooterTemplate>
                    </asp:TemplateField>
                     <asp:TemplateField HeaderText="City">
                        <ItemTemplate>
                            <asp:Label ID="lblCity" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "City") %>'></asp:Label>
                        </ItemTemplate>
                         <FooterTemplate>
                            <asp:TextBox ID="txtboxCity" runat="server"></asp:TextBox>
                        </FooterTemplate>
                    </asp:TemplateField>
                     <asp:TemplateField HeaderText="State">
                        <ItemTemplate>
                            <asp:Label ID="lblState" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "State") %>'></asp:Label>
                        </ItemTemplate>
                         <FooterTemplate>
                            <asp:TextBox ID="txtboxState" runat="server"></asp:TextBox>
                        </FooterTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField >
                        <ItemTemplate>
                        </ItemTemplate>
                        <FooterTemplate>
                            <asp:Button ID="btnInsertRecord" runat="server" Text="Insert Record" CommandName="InsertRecord" UseSubmitBehavior="False" />
                        </FooterTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>
     
    I have added TextBox controls in FooterTemplate to insert record. We will show FooterTemplate when we click Add New Record button. Button Insert has a CommandName attribute which we will use to call function to insert record.
     
  7. Write below function to get data from database

    C#
     
    private DataTable EmployeeData()
    {
        string connString = ConfigurationManager.ConnectionStrings["EmployeeDatabase"].ConnectionString;
        SqlConnection connection = new SqlConnection(connString);
        connection.Open();
        string cmdText = "SELECT * FROM Employees";
        SqlCommand command = new SqlCommand(cmdText, connection);
        SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
        DataTable table = new DataTable();
        dataAdapter.Fill(table);
        return table;
    }
     
    VB.NET
     
    Private Function EmployeeData() As DataTable
        Dim connString As String = ConfigurationManager.ConnectionStrings("EmployeeDatabase").ConnectionString
        Dim connection As New SqlConnection(connString)
        connection.Open()
        Dim cmdText As String = "SELECT * FROM Employees"
        Dim command As New SqlCommand(cmdText, connection)
        Dim dataAdapter As New SqlDataAdapter(command)
        Dim table As New DataTable()
        dataAdapter.Fill(table)
        Return table
     End Function
     
    First we get connection string from web.config and then we have get data from Employee Table and Fill our DataTable object.
     
  8. Write code below in page load method to bind data to GridView

    C#
     
    protected void Page_Load(object sender, EventArgs e)
    {
        GridView1.DataSource = EmployeeData();
        GridView1.DataBind();
    }
     
    VB.NET
     
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
        GridView1.DataSource = EmployeeData()
        GridView1.DataBind()
    End Sub
     
    Here we have bound EmployeeData to GridView control
     
  9. Write below button click event to add new record

    C#
     
    protected void btnAdd_Click(object sender, EventArgs e)
    {
        GridView1.ShowFooter = true;
        GridView1.DataSource = EmployeeData();
        GridView1.DataBind();
    }
     
    VB.NET
     
    Protected Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click
        GridView1.ShowFooter = True
        GridView1.DataSource = EmployeeData()
        GridView1.DataBind()
    End Sub
     
    We have displayed our FooterTemplate on click of our button to add new record.
     
  10. Write below function to insert record into database

    C#
     
    private void InsertRecord(int id, string fname, string lname, string city, string state)
    {
        string connString = ConfigurationManager.ConnectionStrings["EmployeeDatabase"].ConnectionString;
        SqlConnection connection = new SqlConnection(connString);
        connection.Open();
        string cmdText = "INSERT INTO Employees VALUES(@EmpID, @FName, @LName, @City, @State)";
        SqlCommand command = new SqlCommand(cmdText, connection);
        command.CommandType = CommandType.Text;
        command.Parameters.Add("@EmpID", SqlDbType.Int).Value = id;
        command.Parameters.Add("@FName", SqlDbType.VarChar, 50).Value = fname;
        command.Parameters.Add("@LName", SqlDbType.VarChar, 50).Value = lname;
        command.Parameters.Add("@City", SqlDbType.VarChar, 20).Value = city;
        command.Parameters.Add("@State", SqlDbType.VarChar, 5).Value = state;
        command.ExecuteNonQuery();
    }
     
    VB.NET

    Private Sub InsertRecord(ByVal id As Integer, ByVal fname As String, ByVal lname As String, ByVal city As String, ByVal state As String)
        Dim connString As String = ConfigurationManager.ConnectionStrings("EmployeeDatabase").ConnectionString
        Dim connection As New SqlConnection(connString)
        connection.Open()
        Dim cmdText As String = "INSERT INTO Employees VALUES(@EmpID, @FName, @LName, @City, @State)"
        Dim command As New SqlCommand(cmdText, connection)
        command.CommandType = CommandType.Text
        command.Parameters.Add("@EmpID", SqlDbType.Int).Value = id
        command.Parameters.Add("@FName", SqlDbType.VarChar, 50).Value = fname
        command.Parameters.Add("@LName", SqlDbType.VarChar, 50).Value = lname
        command.Parameters.Add("@City", SqlDbType.VarChar, 20).Value = city
        command.Parameters.Add("@State", SqlDbType.VarChar, 5).Value = state
     
        command.ExecuteNonQuery()
    End Sub
     
    First we have get our connection string form web.config file and then insert data into Employees table. 
     
  11. Write below Row Command event for GridView

    C#

    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName.Equals("InsertRecord"))
        {
            TextBox txtID = (TextBox)GridView1.Controls[0].Controls[0].FindControl("txtboxEmpID");
            TextBox txtFName = (TextBox)GridView1.Controls[0].Controls[0].FindControl("txtboxFName");
            TextBox txtLName = (TextBox)GridView1.Controls[0].Controls[0].FindControl("txtboxLName");
            TextBox txtCity = (TextBox)GridView1.Controls[0].Controls[0].FindControl("txtboxCity");
            TextBox txtState = (TextBox)GridView1.Controls[0].Controls[0].FindControl("txtboxState");
     
            int id = Convert.ToInt32(txtID.Text);
     
            InsertRecord(id, txtFName.Text, txtLName.Text, txtCity.Text, txtState.Text);
     
        }
    }
     
    VB.NET

    Protected Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As GridViewCommandEventArgs) Handles GridView1.RowCommand
       If e.CommandName.Equals("InsertRecord") Then
            Dim txtID As TextBox = DirectCast(GridView1.Controls(0).Controls(0).FindControl("txtboxEmpID"), TextBox)
            Dim txtFName As TextBox = DirectCast(GridView1.Controls(0).Controls(0).FindControl("txtboxFName"), TextBox)
            Dim txtLName As TextBox = DirectCast(GridView1.Controls(0).Controls(0).FindControl("txtboxLName"), TextBox)
            Dim txtCity As TextBox = DirectCast(GridView1.Controls(0).Controls(0).FindControl("txtboxCity"), TextBox)
            Dim txtState As TextBox = DirectCast(GridView1.Controls(0).Controls(0).FindControl("txtboxState"), TextBox)
     
            Dim id As Integer = Convert.ToInt32(txtID.Text)
     
            InsertRecord(id, txtFName.Text, txtLName.Text, txtCity.Text, txtState.Text)
        End If
    End Sub
     
    Here we have found all TextBox controls and get the text in the TextBox controls. Then we called the InsertRecord() function to insert record.
     
  12. Now you can browse the website and insert record into your database 

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to log errors into a text file in ASP.NET

Disclaimer - Privacy
© 2002-2017 DevASP.net