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.
Description: In previous articles I
have explained Error: ASP.NET: How to Get Public IP address in ASP.NET using C#
& VB.NET? And Custom Validator: Server Side validation using Custom
Validator in ASP.NET (C# & Vb.NET)And ASP.NET: Bind or
Populate Form View Data control with data from SQL database using c# &
vb.net .
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:
'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.
