1.11M Members

using dataset insert,update,delete,select

 
0
 

I want to insert,update,delete,select using dataset in vb.net

 
0
 

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.

 
-1
 

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
You
This article has been dead for over six months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article