Tuesday, 15 September 2015

GridView: Insert data In gridview in ASP.NET using C# & vb.Net

In this article I will explain how insert data in database using Gridview data Control. Generally we use gridview to Select, delete and update data. But we can insert data using gridview easily.

I have used Row_Command Event to insert data in database. I have added textbox controls in footer Template of gridview to enter data.


Demo: 


Create table in database:

Create Table “Student_Info”  in Database “Blog” and Enter some data to display.
Column Name
DataType
Student_Id
Int
Student_name
Varchar(100)
Age
Int
Class
Varchar(100)

 Now design your web page:
Design Gridview:


<asp:GridView ID="GridView1" runat="server" AllowPaging="True" Width="380px"  AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333" PageSize="7" OnPageIndexChanging="GridView1_PageIndexChanging" ShowFooter="True" OnRowCommand="GridView1_RowCommand">
            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
            <Columns>
                <asp:TemplateField HeaderText="Name">
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Student_name") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("Student_name") %>'></asp:Label>
                    </ItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="txtName" runat="server" Text='<%#Bind("Student_Name") %>'></asp:TextBox>
                    </FooterTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Age">
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("Age") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label2" runat="server" Text='<%# Bind("Age") %>'></asp:Label>
                    </ItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="txtAge" runat="server" Text='<%#Bind("Age") %>'></asp:TextBox>
                    </FooterTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Class">
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("Class") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label3" runat="server" Text='<%# Bind("Class") %>'></asp:Label>
                    </ItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="txtClass" runat="server" Text='<%#Bind("Class") %>'></asp:TextBox>
                    </FooterTemplate>
                </asp:TemplateField>
                <asp:TemplateField ShowHeader="False">            
                      
                        <FooterTemplate> <asp:LinkButton ID="LinkButton1" runat="server" CommandArgument='<%#Bind("Student_id") %>' CausesValidation="False" CommandName="Add" Text="Insert"></asp:LinkButton>
                  </FooterTemplate>
                </asp:TemplateField>
            </Columns>
          
            <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
         
        </asp:GridView>



Asp.net Code:
Import Name Spaces:

C#: Add Following Namespaces.
Using System.Data;
Using System.Data.SqlClient;
Using System.Configuration;

VB.NET: Import Following Namespaces.
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration


Using C#: Code to insert data

//Create Connection:

    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);

//Bind grid On page load:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Fill_grid();
        }
    }

//Bind gridview Function with database:

    public void Fill_grid()
    {
        if (con.State == ConnectionState.Closed)
        {
            con.Open();
        }
        SqlCommand cmd = new SqlCommand("Fill_Dataset", con);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlDataAdapter adpData = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        adpData.Fill(ds);
        GridView1.DataSource = ds;
        GridView1.DataBind();
        con.Close();
    }

//Page Index in gridview

    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        Fill_grid();
    }

//Insert data in gridview:

    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (con.State == ConnectionState.Closed)
        {
            con.Open();
        }
        if (e.CommandName == "Add")
        {
            TextBox txtname = (TextBox)GridView1.FooterRow.FindControl("txtName");
            TextBox txtAge = (TextBox)GridView1.FooterRow.FindControl("txtAge");
            TextBox txtClass = (TextBox)GridView1.FooterRow.FindControl("txtClass");

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "insert into Student_info (Student_Name, age, class) values(@name,@age,@class)";
            cmd.Parameters.AddWithValue("@name", txtname.Text);
            cmd.Parameters.AddWithValue("@age", txtAge.Text);
            cmd.Parameters.AddWithValue("@class", txtClass.Text);
            cmd.ExecuteNonQuery();
            con.Close();
            cmd.Dispose();
            Fill_grid();
        }
    }
}



Using Vb.net:Code to insert data
'Create Connection:

    Private con As New SqlConnection(ConfigurationManager.ConnectionStrings("con").ConnectionString)

    'Bind grid On page load:

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Fill_grid()
        End If
    End Sub

    'Bind gridview Function with database:

    Public Sub Fill_grid()
        If con.State = ConnectionState.Closed Then
            con.Open()
        End If
        Dim cmd As New SqlCommand("Fill_Dataset", con)
        cmd.CommandType = CommandType.StoredProcedure
        Dim adpData As New SqlDataAdapter(cmd)
        Dim ds As New DataSet()
        adpData.Fill(ds)
        GridView1.DataSource = ds
        GridView1.DataBind()
        con.Close()
    End Sub

    'Page Index in gridview

    Protected Sub GridView1_PageIndexChanging(sender As Object, e As GridViewPageEventArgs) Handles GridView1.PageIndexChanging
        GridView1.PageIndex = e.NewPageIndex
        Fill_grid()
    End Sub

    'Insert data in gridview:

    Protected Sub GridView1_RowCommand(sender As Object, e As GridViewCommandEventArgs) Handles GridView1.RowCommand
        If con.State = ConnectionState.Closed Then
            con.Open()
        End If
        If e.CommandName = "Add" Then
            Dim txtname As TextBox = DirectCast(GridView1.FooterRow.FindControl("txtName"), TextBox)
            Dim txtAge As TextBox = DirectCast(GridView1.FooterRow.FindControl("txtAge"), TextBox)
            Dim txtClass As TextBox = DirectCast(GridView1.FooterRow.FindControl("txtClass"), TextBox)

            Dim cmd As New SqlCommand()
            cmd.Connection = con
            cmd.CommandText = "insert into Student_info (Student_Name, age, class) values(@name,@age,@class)"
            cmd.Parameters.AddWithValue("@name", txtname.Text)
            cmd.Parameters.AddWithValue("@age", txtAge.Text)
            cmd.Parameters.AddWithValue("@class", txtClass.Text)
            cmd.ExecuteNonQuery()
            con.Close()
            cmd.Dispose()
            Fill_grid()
        End If
    End Sub


In this Article we have discussed how to insert data in gridview.if you have any query regarding this you can leave your question in comment section.

0 comments:

Post a Comment