I want to insert,update,delete,select using dataset in vb.net
kvprajapati 1,826 Posting Genius Team Colleague
You need to do.
1. Create an instance of DataTable.
2. Add column into Columns collection (Instances of DataColumn).
3. Rows collection to add/remove/update/list rows.
pauldani -2 Light Poster
I want to insert,update,delete,select using dataset in vb.net
To bind to a gridview u dont require a dataset instead u can use a datatable for that.
Here i explain u how to do that.
1. Drag drop one grid view into ur form
2. Make it autogenerate colums "false" and showfooter=true
3. Right click on the top of the gridview and select option edit columns
4. Add 4 template columns over there
Name each columns header text as u want like user no, name, desg, dob
5. The gridview code am given below
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" ShowFooter="True" CellPadding="4" ForeColor="#333333" GridLines="None" OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowCommand="GridView1_RowCommand" OnRowDeleting="GridView1_RowDeleting" OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating">
<Columns>
<asp:TemplateField HeaderText="UserNo">
<ItemTemplate>
<asp:Label ID="lblNo" runat="server" Text='<%#Eval("No") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtFNo" runat="server" ></asp:TextBox>
</FooterTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtENo" runat="server" Text='<%#Eval("No") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<asp:Label ID="lblName" runat="server" Text='<%#Eval("Name") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtFName" runat="server"></asp:TextBox>
</FooterTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtEName" runat="server" Text='<%#Eval("Name") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Designation">
<ItemTemplate>
<asp:Label ID="lblDesignation" runat="server" Text='<%#Eval("Designation") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtFDesignation" runat="server"></asp:TextBox>
</FooterTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtEDesignation" runat="server" Text='<%#Eval("Designation") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="DateofBirth">
<ItemTemplate>
<asp:Label ID="lblDOB" runat="server" Text='<%#Eval("DOB") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtFDOB" runat="server"></asp:TextBox>
</FooterTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtEDOB" runat="server" Text='<%#Eval("DOB") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<FooterTemplate>
<asp:Button runat="server" Text="Add" CommandName="Add"/>
</FooterTemplate>
</asp:TemplateField>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
</Columns>
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#EFF3FB" />
<EditRowStyle BackColor="#2461BF" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
Code Behind
==============
protected void Page_Load(object sender, EventArgs e)
{
//In page load we have to bind that grid view
if (!IsPostBack)
{
BindGridView();
}
}
private void BindGridView()
{
//Declare a datatable for the gridview
DataTable dt = new DataTable();
//Add Columns to the datatable
dt.Columns.Add("No");
dt.Columns.Add("Name");
dt.Columns.Add("Designation");
dt.Columns.Add("DOB");
//Define a datarow for the datatable dt
DataRow dr = dt.NewRow();
//Now add the datarow to the datatable
dt.Rows.Add(dr);
//Now bind the datatable to gridview
GridView1.DataSource = dt;
GridView1.DataBind();
//Now hide the extra row of the grid view
GridView1.Rows[0].Visible = false;
//Delete row 0 from the datatable
dt.Rows[0].Delete();
dt.AcceptChanges();
//View the datatable to the viewstate
ViewState["Data"] = dt;
}
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "Add")
{
//Findout the controls inside the gridview
TextBox txtNo = (TextBox)GridView1.FooterRow.FindControl("txtFNo");
TextBox txtName = (TextBox)GridView1.FooterRow.FindControl("txtFName");
TextBox txtDesignation = (TextBox)GridView1.FooterRow.FindControl("txtFDesignation");
TextBox txtDOB = (TextBox)GridView1.FooterRow.FindControl("txtFDOB");
//Add the items to the gridview
DataTable dt = new DataTable();
//Assign the viewstate to the datatable
dt = (DataTable)ViewState["Data"];
DataRow dr = dt.NewRow();
dr["No"] = txtNo.Text;
dr["Name"] = txtName.Text;
dr["Designation"] = txtDesignation.Text;
dr["DOB"] = txtDOB.Text;
//Add the datarow to the datatable
dt.Rows.Add(dr);
//Now bind the datatable to the gridview
GridView1.DataSource = dt;
GridView1.DataBind();
//Add the details to viewstate also
ViewState["Data"] = dt;
}
}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
//Change the gridview to edit mode
GridView1.EditIndex = e.NewEditIndex;
//Now bind the gridview
GridView1.DataSource = (DataTable)ViewState["Data"];
GridView1.DataBind();
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
//Findout the controls inside the gridview
TextBox txtNo = (TextBox)GridView1.Rows[e.RowIndex].Cells[0].FindControl("txtENo");
TextBox txtName = (TextBox)GridView1.Rows[e.RowIndex].Cells[1].FindControl("txtEName");
TextBox txtDesignation = (TextBox)GridView1.Rows[e.RowIndex].Cells[2].FindControl("txtEDesignation");
TextBox txtDOB = (TextBox)GridView1.Rows[e.RowIndex].Cells[3].FindControl("txtEDOB");
//Assign the ViewState to the datatable
DataRow dr = ((DataTable)ViewState["Data"]).Rows[e.RowIndex];
dr.BeginEdit();
dr["No"] = txtNo.Text;
dr["Name"] = txtName.Text;
dr["Designation"] = txtDesignation.Text;
dr["DOB"] = txtDOB.Text;
dr.EndEdit();
dr.AcceptChanges();
GridView1.EditIndex = -1;
//Now bind the datatable to the gridview
GridView1.DataSource = (DataTable)ViewState["Data"];
GridView1.DataBind();
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
((DataTable)ViewState["Data"]).Rows[e.RowIndex].Delete();
((DataTable)ViewState["Data"]).AcceptChanges();
if (((DataTable)ViewState["Data"]).Rows.Count > 0)
{
GridView1.DataSource = (DataTable)ViewState["Data"];
GridView1.DataBind();
}
else
{
BindGridView();
}
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
GridView1.DataSource = (DataTable)ViewState["Data"];
GridView1.DataBind();
}
============================================
While saving into the database u can take the gridview data from ViewState["Data"]
like this
DataTable dt=(DataTable)ViewState["Data"];
Hope this will clear ur doubt
kvprajapati commented: Please read OP. -1
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.