Update Data in Joined Table Grid

Discussion in 'ASP .Net Datagrid Control' started by Michael Murphy, Nov 18, 2003.

  1. Hi,
    I have a datagrid that shows data joined from multiple tabes and works just
    fine. I load it with a SQLDataReader, and all was great.
    Now I need to allow update of the data strickly in one of the joined tables.
    Example:
    Orders and Order Items are the Join. In the grid would be column heading
    similar to:
    Customer Name Item Number Item Name Qty

    This is not exactly what I am trying to do but in the example I would like
    to enable the user to be able to update the Item Number (a field in the
    OrderItems Table).
    All information I have found so far keys on the fact that the datagrid is
    based on a DataAdapter/Dataset based on one table--simple but not realistic.
    Any pointers would be appreciated.
    Regards,
    Michael Murphy

    954-452-1047
    Michael Murphy, Nov 18, 2003
    #1
    1. Advertising

  2. Include the unique key column for the order items table in the query that
    joins the two tables. Set the Datagrid's DataKeyField property to the name
    of the key column. You do not need to display this column. In the
    Datagrid's Update event handler, use the DataKeys collection to retrieve the
    key for the order item record that you want to update as follows:

    string sTemp = DataGrid1.DataKeys[e.Item.ItemIndex].ToString();
    nID = System.Int32.Parse(sTemp);

    Once you have the key you can run an update statement against just order
    item record with that key.

    "Michael Murphy" <> wrote in message
    news:eM%...

    > Hi,
    > I have a datagrid that shows data joined from multiple tabes and works

    just
    > fine. I load it with a SQLDataReader, and all was great.
    > Now I need to allow update of the data strickly in one of the joined

    tables.
    > Example:
    > Orders and Order Items are the Join. In the grid would be column heading
    > similar to:
    > Customer Name Item Number Item Name Qty
    >
    > This is not exactly what I am trying to do but in the example I would like
    > to enable the user to be able to update the Item Number (a field in the
    > OrderItems Table).
    > All information I have found so far keys on the fact that the datagrid is
    > based on a DataAdapter/Dataset based on one table--simple but not

    realistic.
    > Any pointers would be appreciated.
    > Regards,
    > Michael Murphy
    >
    > 954-452-1047
    >
    >
    >
    Mark Schubert, Nov 19, 2003
    #2
    1. Advertising

  3. Mark,
    Thanks for the feedback. In regular windows development this is such a no
    brainer, but life goes on. Anyway, can I stick with my current
    implementation using the SQLDataReader to load the grid??
    Also, is there a quick and easy way for me to access the column to copy what
    I need to update?
    If I understand you correctly, you are saying get what you need to update
    from the grid and then run an update query to write the data back to the
    database. As it stands right now, I can put the Edit, Update, Cancel button
    on the grid and can get the button to fire, but the grid is read only. Is is
    read only becuase of a property setting or because I am using a datareader
    as the data source.
    Thanks Again for your time.
    Michael

    "Mark Schubert" <> wrote in message
    news:...
    > Include the unique key column for the order items table in the query that
    > joins the two tables. Set the Datagrid's DataKeyField property to the

    name
    > of the key column. You do not need to display this column. In the
    > Datagrid's Update event handler, use the DataKeys collection to retrieve

    the
    > key for the order item record that you want to update as follows:
    >
    > string sTemp = DataGrid1.DataKeys[e.Item.ItemIndex].ToString();
    > nID = System.Int32.Parse(sTemp);
    >
    > Once you have the key you can run an update statement against just order
    > item record with that key.
    >
    > "Michael Murphy" <> wrote in message
    > news:eM%...
    >
    > > Hi,
    > > I have a datagrid that shows data joined from multiple tabes and works

    > just
    > > fine. I load it with a SQLDataReader, and all was great.
    > > Now I need to allow update of the data strickly in one of the joined

    > tables.
    > > Example:
    > > Orders and Order Items are the Join. In the grid would be column heading
    > > similar to:
    > > Customer Name Item Number Item Name Qty
    > >
    > > This is not exactly what I am trying to do but in the example I would

    like
    > > to enable the user to be able to update the Item Number (a field in the
    > > OrderItems Table).
    > > All information I have found so far keys on the fact that the datagrid

    is
    > > based on a DataAdapter/Dataset based on one table--simple but not

    > realistic.
    > > Any pointers would be appreciated.
    > > Regards,
    > > Michael Murphy
    > >
    > > 954-452-1047
    > >
    > >
    > >

    >
    >
    Michael D. Murphy, Nov 19, 2003
    #3
  4. Follow these steps to make a DataGrid editable:

    1. Add <asp:EditCommandColumn ...> to your .aspx file. This just gives you
    the column with hyperlinks - nothing else.

    2. Next you add the EditCommand event handler. You add event handlers from
    the properties window for the DataGrid. Click the lighning bolt at the top
    of the properties window to display all of the possible events. Double
    click the EditCommand event. This automatically adds the following function
    to your codebehind file. You need to fill in the body as I have done below.
    e.Item.ItemIndex gives you the number of the row where you selected "edit".
    Setting the EditItemIndex property is what actually makes the row editable.
    Then you bind to the data.
    private void DataGrid1_EditCommand(object source,
    System.Web.UI.WebControls.DataGridCommandEventArgs e)

    {

    DataGrid1.EditItemIndex = e.Item.ItemIndex;

    DataGrid1.Databind();

    }

    3. At this point your application can put the grid into edit mode for any of
    the rows. You next need to add an event handler for the CancelCommand just
    as you did for the EditCommand above. The CancelCommand event should be
    implemented as follows:

    private void DataGrid1_CancelCommand(object source,
    System.Web.UI.WebControls.DataGridCommandEventArgs e)
    {

    DataGrid1.EditItemIndex = -1;

    DataGrid1.Databind();

    }

    Setting EditItemIndex to -1 causes the grid to display with no rows
    editable.

    4. Next add a event handler for the UpdateCommand event. This is the most
    complicated because you must get the modified data from the grid and update
    the database. An example of this is as follows.

    private void DataGrid1_UpdateCommand(object source,
    System.Web.UI.WebControls.DataGridCommandEventArgs e)
    {

    TextBox tb = (TextBox)(e.Item.Cells[0].Controls[0]);

    int nID = System.Int32.Parse(tb.Text);

    tb = (TextBox)(e.Item.Cells[1].Controls[0]);

    string sName = tb.Text;

    /* PERFORM UPDATE HERE

    ...........................................................

    */

    DataGrid1.EditItemIndex = -1;

    BindGrid();

    }

    In my example above, the grid contains an ID and a Name. They are both
    displayed in the grid. It's easy to extract these and update the database.

    In your case, you don't want to display the ID column or edit it, but you
    need access to it in the UpdateCommand event handler. This is very common.
    What you need to do is set the DataKeyField property to the name of the
    column. You can do this in the properties page for the grid or by editing
    the grid properties manually in the .aspx. This will cause the keys to be
    saved to the DataGrid's DataKeys collection grid when the data is bound.
    You would need to access this data in your UpdateCommand Event Handler as
    follows:

    string sTemp = DataGrid1.DataKeys[e.Item.ItemIndex].ToString();
    nID = System.Int32.Parse(sTemp);


    Now you have the "hidden" data that you need to update the database.

    Good luck





    "Michael D. Murphy" <> wrote in message
    news:...

    > Mark,
    > Thanks for the feedback. In regular windows development this is such a no
    > brainer, but life goes on. Anyway, can I stick with my current
    > implementation using the SQLDataReader to load the grid??
    > Also, is there a quick and easy way for me to access the column to copy

    what
    > I need to update?
    > If I understand you correctly, you are saying get what you need to update
    > from the grid and then run an update query to write the data back to the
    > database. As it stands right now, I can put the Edit, Update, Cancel

    button
    > on the grid and can get the button to fire, but the grid is read only. Is

    is
    > read only becuase of a property setting or because I am using a datareader
    > as the data source.
    > Thanks Again for your time.
    > Michael
    >
    > "Mark Schubert" <> wrote in message
    > news:...
    > > Include the unique key column for the order items table in the query

    that
    > > joins the two tables. Set the Datagrid's DataKeyField property to the

    > name
    > > of the key column. You do not need to display this column. In the
    > > Datagrid's Update event handler, use the DataKeys collection to retrieve

    > the
    > > key for the order item record that you want to update as follows:
    > >
    > > string sTemp = DataGrid1.DataKeys[e.Item.ItemIndex].ToString();
    > > nID = System.Int32.Parse(sTemp);
    > >
    > > Once you have the key you can run an update statement against just order
    > > item record with that key.
    > >
    > > "Michael Murphy" <> wrote in message
    > > news:eM%...
    > >
    > > > Hi,
    > > > I have a datagrid that shows data joined from multiple tabes and works

    > > just
    > > > fine. I load it with a SQLDataReader, and all was great.
    > > > Now I need to allow update of the data strickly in one of the joined

    > > tables.
    > > > Example:
    > > > Orders and Order Items are the Join. In the grid would be column

    heading
    > > > similar to:
    > > > Customer Name Item Number Item Name Qty
    > > >
    > > > This is not exactly what I am trying to do but in the example I would

    > like
    > > > to enable the user to be able to update the Item Number (a field in

    the
    > > > OrderItems Table).
    > > > All information I have found so far keys on the fact that the datagrid

    > is
    > > > based on a DataAdapter/Dataset based on one table--simple but not

    > > realistic.
    > > > Any pointers would be appreciated.
    > > > Regards,
    > > > Michael Murphy
    > > >
    > > > 954-452-1047
    > > >
    > > >
    > > >

    > >
    > >

    >
    >
    Mark Schubert, Nov 20, 2003
    #4
  5. Hi Mark,
    Thanks for the help. I will give it a try.
    Michael

    "Mark Schubert" <> wrote in message
    news:%...
    > Follow these steps to make a DataGrid editable:
    >
    > 1. Add <asp:EditCommandColumn ...> to your .aspx file. This just gives

    you
    > the column with hyperlinks - nothing else.
    >
    > 2. Next you add the EditCommand event handler. You add event handlers

    from
    > the properties window for the DataGrid. Click the lighning bolt at the

    top
    > of the properties window to display all of the possible events. Double
    > click the EditCommand event. This automatically adds the following

    function
    > to your codebehind file. You need to fill in the body as I have done

    below.
    > e.Item.ItemIndex gives you the number of the row where you selected

    "edit".
    > Setting the EditItemIndex property is what actually makes the row

    editable.
    > Then you bind to the data.
    > private void DataGrid1_EditCommand(object source,
    > System.Web.UI.WebControls.DataGridCommandEventArgs e)
    >
    > {
    >
    > DataGrid1.EditItemIndex = e.Item.ItemIndex;
    >
    > DataGrid1.Databind();
    >
    > }
    >
    > 3. At this point your application can put the grid into edit mode for any

    of
    > the rows. You next need to add an event handler for the CancelCommand

    just
    > as you did for the EditCommand above. The CancelCommand event should be
    > implemented as follows:
    >
    > private void DataGrid1_CancelCommand(object source,
    > System.Web.UI.WebControls.DataGridCommandEventArgs e)
    > {
    >
    > DataGrid1.EditItemIndex = -1;
    >
    > DataGrid1.Databind();
    >
    > }
    >
    > Setting EditItemIndex to -1 causes the grid to display with no rows
    > editable.
    >
    > 4. Next add a event handler for the UpdateCommand event. This is the most
    > complicated because you must get the modified data from the grid and

    update
    > the database. An example of this is as follows.
    >
    > private void DataGrid1_UpdateCommand(object source,
    > System.Web.UI.WebControls.DataGridCommandEventArgs e)
    > {
    >
    > TextBox tb = (TextBox)(e.Item.Cells[0].Controls[0]);
    >
    > int nID = System.Int32.Parse(tb.Text);
    >
    > tb = (TextBox)(e.Item.Cells[1].Controls[0]);
    >
    > string sName = tb.Text;
    >
    > /* PERFORM UPDATE HERE
    >
    > ..........................................................
    >
    > */
    >
    > DataGrid1.EditItemIndex = -1;
    >
    > BindGrid();
    >
    > }
    >
    > In my example above, the grid contains an ID and a Name. They are both
    > displayed in the grid. It's easy to extract these and update the

    database.
    >
    > In your case, you don't want to display the ID column or edit it, but you
    > need access to it in the UpdateCommand event handler. This is very

    common.
    > What you need to do is set the DataKeyField property to the name of the
    > column. You can do this in the properties page for the grid or by editing
    > the grid properties manually in the .aspx. This will cause the keys to be
    > saved to the DataGrid's DataKeys collection grid when the data is bound.
    > You would need to access this data in your UpdateCommand Event Handler as
    > follows:
    >
    > string sTemp = DataGrid1.DataKeys[e.Item.ItemIndex].ToString();
    > nID = System.Int32.Parse(sTemp);
    >
    >
    > Now you have the "hidden" data that you need to update the database.
    >
    > Good luck
    >
    >
    >
    >
    >
    > "Michael D. Murphy" <> wrote in message
    > news:...
    >
    > > Mark,
    > > Thanks for the feedback. In regular windows development this is such a

    no
    > > brainer, but life goes on. Anyway, can I stick with my current
    > > implementation using the SQLDataReader to load the grid??
    > > Also, is there a quick and easy way for me to access the column to copy

    > what
    > > I need to update?
    > > If I understand you correctly, you are saying get what you need to

    update
    > > from the grid and then run an update query to write the data back to the
    > > database. As it stands right now, I can put the Edit, Update, Cancel

    > button
    > > on the grid and can get the button to fire, but the grid is read only.

    Is
    > is
    > > read only becuase of a property setting or because I am using a

    datareader
    > > as the data source.
    > > Thanks Again for your time.
    > > Michael
    > >
    > > "Mark Schubert" <> wrote in message
    > > news:...
    > > > Include the unique key column for the order items table in the query

    > that
    > > > joins the two tables. Set the Datagrid's DataKeyField property to the

    > > name
    > > > of the key column. You do not need to display this column. In the
    > > > Datagrid's Update event handler, use the DataKeys collection to

    retrieve
    > > the
    > > > key for the order item record that you want to update as follows:
    > > >
    > > > string sTemp = DataGrid1.DataKeys[e.Item.ItemIndex].ToString();
    > > > nID = System.Int32.Parse(sTemp);
    > > >
    > > > Once you have the key you can run an update statement against just

    order
    > > > item record with that key.
    > > >
    > > > "Michael Murphy" <> wrote in message
    > > > news:eM%...
    > > >
    > > > > Hi,
    > > > > I have a datagrid that shows data joined from multiple tabes and

    works
    > > > just
    > > > > fine. I load it with a SQLDataReader, and all was great.
    > > > > Now I need to allow update of the data strickly in one of the joined
    > > > tables.
    > > > > Example:
    > > > > Orders and Order Items are the Join. In the grid would be column

    > heading
    > > > > similar to:
    > > > > Customer Name Item Number Item Name Qty
    > > > >
    > > > > This is not exactly what I am trying to do but in the example I

    would
    > > like
    > > > > to enable the user to be able to update the Item Number (a field in

    > the
    > > > > OrderItems Table).
    > > > > All information I have found so far keys on the fact that the

    datagrid
    > > is
    > > > > based on a DataAdapter/Dataset based on one table--simple but not
    > > > realistic.
    > > > > Any pointers would be appreciated.
    > > > > Regards,
    > > > > Michael Murphy
    > > > >
    > > > > 954-452-1047
    > > > >
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >
    Michael D. Murphy, Nov 21, 2003
    #5
    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. rb
    Replies:
    0
    Views:
    311
  2. blu3g85

    recently joined member?

    blu3g85, Apr 3, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    304
    =?Utf-8?B?VE9KTw==?=
    Apr 4, 2004
  3. Ersin Gençtürk

    how to fill typed dataset with joined queries ?

    Ersin Gençtürk, Jan 19, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    913
    Eliyahu Goldin
    Jan 19, 2005
  4. news.sbcglobal.net
    Replies:
    2
    Views:
    634
    Erland Sommarskog
    Jun 11, 2006
  5. Remco Swoany

    Show info from table wich is joined

    Remco Swoany, Nov 6, 2007, in forum: Ruby
    Replies:
    5
    Views:
    81
    Todd Benson
    Nov 7, 2007
Loading...

Share This Page