Edit Gridview using stored procesure

Discussion in 'ASP .Net Web Controls' started by Madison, Jun 13, 2007.

  1. Madison

    Madison Guest

    Hi all,
    I'm try to write the code for gridview to delete, edit (only one or two
    columns), page, sort using stored procedure to get dataTable to bind data
    (avoid using SQLDataSource to bind data). All the samples I see always use
    SQLDataSource which in the real business is not the good ideas.

    Any ideas or samples would be appreciated.

    Thank you.
     
    Madison, Jun 13, 2007
    #1
    1. Advertising

  2. Hi Madison,

    First, a SqlDataSource is also able to use stored procedure to
    select/insert/delete/update data.

    Please note that if you're not using a DataSource control to bind to
    GridView, you will need to handle many events yourself such as
    OnRowUpdating, OnSorting.

    To do it all yourself, you can refer to following pages:

    #Sorting GridView Manually
    http://www.gridviewguy.com/ArticleDetails.aspx?articleID=176

    #GridView Custom Paging
    http://www.gridviewguy.com/ArticleDetails.aspx?articleID=210


    And here's an example on how to edit/delete rows in GridView when bound to
    a DataTable:


    <%@ Page Language="C#" %>

    <%@ Import Namespace="System.Data" %>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    <script runat="server">

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

    private void BindGrid()
    {
    GridView1.DataSource = GetDataSource();
    GridView1.DataBind();
    }

    protected DataTable GetDataSource()
    {
    const string key = "MyDataSource";
    DataTable dt = Session[key] as DataTable;
    if (dt == null)
    {
    dt = new DataTable();
    dt.Columns.Add("ID", typeof(int));
    dt.Columns.Add("Name", typeof(string));
    dt.Rows.Add(1, "first object");
    dt.Rows.Add(2, "second object");
    Session[key] = dt;
    }
    return dt;
    }

    protected void GridView1_RowEditing(object sender,
    GridViewEditEventArgs e)
    {
    GridView1.EditIndex = e.NewEditIndex;
    BindGrid();
    }
    protected void GridView1_RowCancelingEdit(object sender,
    GridViewCancelEditEventArgs e)
    {
    GridView1.EditIndex = -1;
    BindGrid();
    }
    protected void GridView1_RowUpdating(object sender,
    GridViewUpdateEventArgs e)
    {
    int id = int.Parse(GridView1.Rows[e.RowIndex].Cells[1].Text);
    TextBox txtName =
    GridView1.Rows[e.RowIndex].Cells[2].FindControl("txtName") as TextBox;
    string newname = txtName.Text;

    FindRowByID(id)["Name"] = newname;
    GridView1.EditIndex = -1;
    BindGrid();
    }

    private DataRow FindRowByID(int id)
    {
    DataRow[] rows = GetDataSource().Select("ID = " + id.ToString());
    return rows[0];
    }

    protected void GridView1_RowDeleting(object sender,
    GridViewDeleteEventArgs e)
    {
    int id = int.Parse(GridView1.Rows[e.RowIndex].Cells[1].Text);
    FindRowByID(id).Delete();
    BindGrid();
    }
    </script>

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
    <title>Untitled Page</title>
    </head>
    <body>
    <form id="form1" runat="server">
    <div>
    <asp:GridView DataKeyNames="ID" AutoGenerateColumns="false"
    ID="GridView1" runat="server"
    OnRowCancelingEdit="GridView1_RowCancelingEdit"
    OnRowEditing="GridView1_RowEditing"
    OnRowUpdating="GridView1_RowUpdating"
    OnRowDeleting="GridView1_RowDeleting">
    <Columns>
    <asp:CommandField ShowEditButton="true"
    ShowDeleteButton="true" />
    <asp:BoundField HeaderText="ID" DataField="ID"
    ReadOnly="true" />
    <asp:TemplateField HeaderText="Name">
    <ItemTemplate>
    <asp:Label ID="lblName" runat="server"
    Text='<%# Eval("Name") %>'></asp:Label>
    </ItemTemplate>
    <EditItemTemplate>
    <asp:TextBox ID="txtName" runat="server"
    Text='<%# Bind("Name") %>'></asp:TextBox>
    </EditItemTemplate>
    </asp:TemplateField>
    </Columns>
    </asp:GridView>
    </div>
    </form>
    </body>
    </html>



    Hope this helps.


    Regards,
    Walter Wang (, remove 'online.')
    Microsoft Online Community Support

    ==================================================
    When responding to posts, please "Reply to Group" via your newsreader so
    that others may learn and benefit from your issue.
    ==================================================

    This posting is provided "AS IS" with no warranties, and confers no rights.
     
    Walter Wang [MSFT], Jun 14, 2007
    #2
    1. Advertising

  3. Madison

    Madison Guest

    Hi Walter,

    Thank you for your reply. I will try your coding and let you know how is
    going.

    Madison
     
    Madison, Jun 14, 2007
    #3
  4. Madison

    Madison Guest

    Hi Walter,

    Thanks, the codes work very good.

    I'm interest in using SqlDataSource with stored procedure can you refer to
    web site that give more information? Do you have any pro and con on it?

    Madison.
     
    Madison, Jun 14, 2007
    #4
  5. Hi Madison,

    When you use the wizard to configure SqlDataSource, in the step "How would
    you like to retrieve data from your database", you can select "Specify a
    custom SQL statement or stored procedure"; when you click next, you can
    choose a stored procedure for the SELECT/UPDATE/INSERT/DELETE.

    Hope this helps.


    Regards,
    Walter Wang (, remove 'online.')
    Microsoft Online Community Support

    ==================================================
    When responding to posts, please "Reply to Group" via your newsreader so
    that others may learn and benefit from your issue.
    ==================================================

    This posting is provided "AS IS" with no warranties, and confers no rights.
     
    Walter Wang [MSFT], Jun 15, 2007
    #5
  6. Madison

    JimS Guest

    Hello Walter, and or anyone else!
    How would you update the dataset if one of the fields in the grid was a
    template item? The case I am looking to see is one item in a datagrid that
    has been converted to a dropdown box. The dropdown box value is what I
    would like to show up in the edited record. The example is a drop down
    box of states. When the correct state is chosen I would like to update the
    record with that value.

    Thanks

    "Walter Wang [MSFT]" <> wrote in message
    news:...
    Hi Madison,

    First, a SqlDataSource is also able to use stored procedure to
    select/insert/delete/update data.

    Please note that if you're not using a DataSource control to bind to
    GridView, you will need to handle many events yourself such as
    OnRowUpdating, OnSorting.

    To do it all yourself, you can refer to following pages:

    #Sorting GridView Manually
    http://www.gridviewguy.com/ArticleDetails.aspx?articleID=176

    #GridView Custom Paging
    http://www.gridviewguy.com/ArticleDetails.aspx?articleID=210


    And here's an example on how to edit/delete rows in GridView when bound to
    a DataTable:


    <%@ Page Language="C#" %>

    <%@ Import Namespace="System.Data" %>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    <script runat="server">

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

    private void BindGrid()
    {
    GridView1.DataSource = GetDataSource();
    GridView1.DataBind();
    }

    protected DataTable GetDataSource()
    {
    const string key = "MyDataSource";
    DataTable dt = Session[key] as DataTable;
    if (dt == null)
    {
    dt = new DataTable();
    dt.Columns.Add("ID", typeof(int));
    dt.Columns.Add("Name", typeof(string));
    dt.Rows.Add(1, "first object");
    dt.Rows.Add(2, "second object");
    Session[key] = dt;
    }
    return dt;
    }

    protected void GridView1_RowEditing(object sender,
    GridViewEditEventArgs e)
    {
    GridView1.EditIndex = e.NewEditIndex;
    BindGrid();
    }
    protected void GridView1_RowCancelingEdit(object sender,
    GridViewCancelEditEventArgs e)
    {
    GridView1.EditIndex = -1;
    BindGrid();
    }
    protected void GridView1_RowUpdating(object sender,
    GridViewUpdateEventArgs e)
    {
    int id = int.Parse(GridView1.Rows[e.RowIndex].Cells[1].Text);
    TextBox txtName =
    GridView1.Rows[e.RowIndex].Cells[2].FindControl("txtName") as TextBox;
    string newname = txtName.Text;

    FindRowByID(id)["Name"] = newname;
    GridView1.EditIndex = -1;
    BindGrid();
    }

    private DataRow FindRowByID(int id)
    {
    DataRow[] rows = GetDataSource().Select("ID = " + id.ToString());
    return rows[0];
    }

    protected void GridView1_RowDeleting(object sender,
    GridViewDeleteEventArgs e)
    {
    int id = int.Parse(GridView1.Rows[e.RowIndex].Cells[1].Text);
    FindRowByID(id).Delete();
    BindGrid();
    }
    </script>

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
    <title>Untitled Page</title>
    </head>
    <body>
    <form id="form1" runat="server">
    <div>
    <asp:GridView DataKeyNames="ID" AutoGenerateColumns="false"
    ID="GridView1" runat="server"
    OnRowCancelingEdit="GridView1_RowCancelingEdit"
    OnRowEditing="GridView1_RowEditing"
    OnRowUpdating="GridView1_RowUpdating"
    OnRowDeleting="GridView1_RowDeleting">
    <Columns>
    <asp:CommandField ShowEditButton="true"
    ShowDeleteButton="true" />
    <asp:BoundField HeaderText="ID" DataField="ID"
    ReadOnly="true" />
    <asp:TemplateField HeaderText="Name">
    <ItemTemplate>
    <asp:Label ID="lblName" runat="server"
    Text='<%# Eval("Name") %>'></asp:Label>
    </ItemTemplate>
    <EditItemTemplate>
    <asp:TextBox ID="txtName" runat="server"
    Text='<%# Bind("Name") %>'></asp:TextBox>
    </EditItemTemplate>
    </asp:TemplateField>
    </Columns>
    </asp:GridView>
    </div>
    </form>
    </body>
    </html>



    Hope this helps.


    Regards,
    Walter Wang (, remove 'online.')
    Microsoft Online Community Support

    ==================================================
    When responding to posts, please "Reply to Group" via your newsreader so
    that others may learn and benefit from your issue.
    ==================================================

    This posting is provided "AS IS" with no warranties, and confers no rights.
     
    JimS, Jun 15, 2007
    #6
  7. For a DropDownList, all you need to change in the demo code is to cast the
    control returned from FindControl to a DropDownList and you should be able
    to get the correct value selected the user currently.


    Regards,
    Walter Wang (, remove 'online.')
    Microsoft Online Community Support

    ==================================================
    When responding to posts, please "Reply to Group" via your newsreader so
    that others may learn and benefit from your issue.
    ==================================================

    This posting is provided "AS IS" with no warranties, and confers no rights.
     
    Walter Wang [MSFT], Jun 18, 2007
    #7
  8. Madison

    Matt Guest

    On Jun 18, 4:03 am, (Walter Wang [MSFT])
    wrote:
    > For a DropDownList, all you need to change in the demo code is to cast the
    > control returned from FindControl to a DropDownList and you should be able
    > to get the correct value selected the user currently.
    >
    > Regards,
    > Walter Wang (, remove 'online.')
    > Microsoft Online Community Support
    >
    > ==================================================
    > When responding to posts, please "Reply to Group" via your newsreader so
    > that others may learn and benefit from your issue.
    > ==================================================
    >
    > This posting is provided "AS IS" with no warranties, and confers no rights.


    Or, if you are using a SqlDataSource, as long as the parameter in the
    Update command is the same as the field name (ie State = @State), it
    should work without any hassle. also make sure you have the
    DataKeyName set in your grid (it should be the primary key).
     
    Matt, Jun 18, 2007
    #8
  9. Hi Madison,

    Since I haven't seen your reply yet, I'm writing to check the status of
    this post. Thanks.


    Regards,
    Walter Wang (, remove 'online.')
    Microsoft Online Community Support

    ==================================================
    When responding to posts, please "Reply to Group" via your newsreader so
    that others may learn and benefit from your issue.
    ==================================================

    This posting is provided "AS IS" with no warranties, and confers no rights.
     
    Walter Wang [MSFT], Jun 21, 2007
    #9
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. San Diego Guy
    Replies:
    0
    Views:
    551
    San Diego Guy
    Aug 7, 2003
  2. William F. Robertson, Jr.

    can't edit/create stored procedure

    William F. Robertson, Jr., Oct 17, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    766
    William F. Robertson, Jr.
    Oct 17, 2003
  3. Max
    Replies:
    5
    Views:
    382
  4. =?Utf-8?B?a2Vu?=
    Replies:
    1
    Views:
    10,342
    Wiktor Zychla [C# MVP]
    Jan 23, 2006
  5. Jaime Stuardo

    GridView control enters edit mode when I click Edit link twice

    Jaime Stuardo, Apr 7, 2006, in forum: ASP .Net Web Controls
    Replies:
    0
    Views:
    401
    Jaime Stuardo
    Apr 7, 2006
Loading...

Share This Page