Edit Items In Nested DataGrid within another DataGrid:

Hi jguy07,

i think the problem is when you rise the onEditCommand event how to "tell" the grid to move to edit mode and collect the data that it has before clicking?
if this is correct - you can add an additional column in the nested datagrid wich will holds the passed parameter x and make this column not visible
then when you rise the editcommand event set the dgDetails.EditItemIndex = e.Item.ItemIndex and call again the method for binding the nested datagrid  by passing the parameter saved in the hidden column

Cheers!

mmarinov,
I see what you're saying.  However, how do I accomplish "you can add an additional column in the nested datagrid wich will holds the passed parameter x " ?  

Here's how I send the category to the "GetDetails function":
<asp:DataGrid runat="server" ID="dgDetails" DataSource=<%# GetDetails(container.dataitem("catg"))%> AutoGenerateColumns="false" Width="100%" OnEditCommand="dgDetails_Edit" OnCancelCommand="dgDetails_Cancel" OnUpdateCommand="dgDetails_Update">

Here's the function:
    'Create SQL Connection, query data, bind to sub datagrid control/nested
    Function GetDetails(ByVal x As String)
        'Set date to this month / db uses wierd date format
        Dim queryDate As String = Right(CalendarPopup1.PostedDate, 4) & Left(CalendarPopup1.PostedDate, 2)
     
        '1.  Create a connection to the sql database
        Dim MyConnection As New SqlConnection("Server=x.x.x.x; Initial Catalog=mydb; User ID=me; Password=xxxxxx")
        '2   Create the command object, passing in the SQL string
        Dim strSQL As String
        Dim ds As New DataSet()

        strSQL = "SELECT blah, blah, blah WHERE month>='" & queryDate & "' AND month <='" & queryDate & "'"
        strSQL &= "AND catg ='" & x & "'
        strSQL &= "ORDER BY id"

        Dim da As New SqlDataAdapter(strSQL, MyConnection)

        'Set the datagrid's datasource to the datareader and databaind to control
        da.Fill(ds, "tblDetails")
        Return ds.Tables("tblDetails")
    End Function

With this in mind, I'm not sure how to construct another column to accommodate this entire ds.Tables("tblDetails") that's being returned.  

first add a template column
next make it not visible
add a Label control it

add ItemDataBound event to the Details datagrid

in this event add this

if e.Item.ItemType =  ListItemType.Item orElse e.Item.ItemType = ListItemType.AlternativeItem then
 Dim labelControl as Label = CType(e.Item.FindControl("your label control ID"), Label)
 Dim rowItem as DataRow = e.Item.DataItem
 labelControl.Text = your x parameter
end if

then in the oneditcommand event get the label control from the cell it is added and rebind the grid

regards

mmarinov,
First, thank you for your response.  I have tried your suggestion, but I am getting a compilation error:
Compiler Error Message: BC30408: Method 'Public Sub dgDetails_Identify(sender As Object, e As System.Web.UI.WebControls.DataGridCommandEventArgs)' does not have the same signature as delegate 'Delegate Sub DataGridItemEventHandler(sender As Object, e As System.Web.UI.WebControls.DataGridItemEventArgs)'.

Here's the code from datagrid dgDetails with the templatecolumn:
<!-- Start Inner DataGrid -->
                <asp:DataGrid runat="server" ID="dgDetails" DataSource=<%# GetDetails(container.dataitem("catg"))%> AutoGenerateColumns="false" Width="100%" OnEditCommand="dgDetails_Edit" OnCancelCommand="dgDetails_Cancel" OnUpdateCommand="dgDetails_Update" OnItemDataBound="dgDetails_Identify">
               
                 <AlternatingItemStyle BackColor = "#eeeeee" />
                   <Columns>
                       
                       <asp:TemplateColumn Visible="false">
                            <ItemTemplate>
                               <asp:Label id="lblIdentity" runat="server"></asp:Label>
                            </ItemTemplate>
                       </asp:TemplateColumn>
                       
                       <asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="Update" ItemStyle-Wrap="false" HeaderText="Edit/Update" HeaderStyle-Wrap="False" HeaderStyle-Font-Bold="true" />
                       
                       
                       <asp:BoundColumn DataField="id" HeaderText="RecID" ItemStyle-Width="200px" HeaderStyle-Font-Bold="true"></asp:BoundColumn>
                     
                     <asp:BoundColumn DataField="cust" HeaderText="Customer" ItemStyle-Width="200px" ItemStyle-HorizontalAlign="Left" HeaderStyle-Font-Bold="true" ReadOnly="true"></asp:BoundColumn>
                     
                      <asp:BoundColumn DataField="RealName" HeaderText="Sales Name" HeaderStyle-Font-Bold="true" ReadOnly="true"></asp:BoundColumn>
                       
                      <asp:BoundColumn DataField="amt"  HeaderText="Forecast" HeaderStyle-Font-Bold="true" ></asp:BoundColumn>    
                               
                   </Columns>        
                </asp:DataGrid>        
               <!-- End Inner DataGrid -->


Here's the Sub to handle the event:
    Sub dgDetails_Identify(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)

        If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then
            Dim labelControl As Label = CType(e.Item.FindControl("lblIdentity"), Label)
            Dim rowItem As DataRow = e.Item.DataItem
            labelControl.Text = rowItem.Item("catg")
        End If

    End Sub

Any ideas what's causing the error?  

The problem is in the definition of the dgDetails_Identity

change
Sub dgDetails_Identify(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)
to
Sub dgDetails_Identify(ByVal sender As Object, ByVal e As DataGridItemEventArgs)

regards

mmarinov,
Again, thank you for the clarification.  I have added the below to the Sub dgDetails_Identify:

    Sub dgDetails_Identify(ByVal sender As Object, ByVal e As DataGridItemEventArgs)

        If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then
            Dim labelControl As Label = CType(e.Item.FindControl("lblIdentity"), Label)
            Dim rowItem As DataRowView = e.Item.DataItem
            labelControl.Text = rowItem.Item("id")
        End If

    End Sub

I now have a problem with the "on edit" event.  I have this:
    Sub dgDetails_Edit(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)
        dgMasterData.EditItemIndex = ?????
        BindData()
    End Sub

I'm not sure how to bind the editItemIndex to the hidden label that we constructed.  Any idea?  

I do not how exactly you bind the master grid, but a scenario is to
first scenario:
- get the data for master grid and find the index of the saved id in labelControl
- set the EditItemIndex
- bind data

second scenario
- in the labelControl save the current EditItemIndex when the DataBound is called (keep in mind that here when page may have issues)
- in the dgDetails_Edit - get labelControl's value and direct set to EditItemIndex
-bind data


hope this help
regards

mmarinov,
Thanks for your reply.  I see what you're saying in both scenarios, but I'm not sure how to do this with CODE.  I'm stuck on this edit command.  Is there anything that I can display to help you with seeing what I'm trying to do?  As listed above, I wasn't sure how to set up the dgDetials_Edit sub.  

You say:
- get data for master grid and find index of the saved id in labelControl -- How?

- set the EditItemIndex -- How?
- Bind data (I think I can do this one).

Again, I do thank you for your help with this.  I'm having a hard time converting from PHP to ASP.NET  

lets try the second scenario

 Sub dgDetails_Identify(ByVal sender As Object, ByVal e As DataGridItemEventArgs)

        If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then
            Dim labelControl As Label = CType(e.Item.FindControl("lblIdentity"), Label)
            Dim rowItem As DataRowView = e.Item.DataItem
            labelControl.Text = dgMasterData.EditItemIndex
        End If

    End Sub

Sub dgDetails_Edit(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)

        Dim labelControl As Label = CType(e.Item.FindControl("lblIdentity"), Label)
        dgMasterData.EditItemIndex = int.Parse(labelControl.Text)
        BindData()
    End Sub

regards

mmarinov,
Cool!  Thanks for the code peek!  I can follow this now.  However, I am getting an error:
Overload resolution failed because no accessible 'Int' accepts this number of arguments.

on this line:
dgMasterData.EditItemIndex = Int.Parse(labelControl.Text)

It's not liking the "Int.Parse" function.....

oooooooppppps i much c# programmer
replace Int.Parse with Integer.Parse or with Int32.Parse

mmarinov

O.k., it at least displays the data with the edit button.  Now, when I click the "edit" link, the page posts back and just blinks.  Nothing is editble.  Is it because of my bind data sub?  The bind data sub is only pulling DISTINCT catg from the table.  The inner table actually needs to be the one edited.  I'm pulling the inner table with this:

    Function GetDetails(ByVal x As String)
        'Set date to this month
        Dim queryDate As String = Right(CalendarPopup1.PostedDate, 4) & Left(CalendarPopup1.PostedDate, 2)
        'Set location
        Dim location As String = ddlPlant.SelectedItem.Value

        '1.  Create a connection to the sql database  
        Dim MyConnection As New SqlConnection("Server=x.x.x.x; Initial Catalog=mydata; User ID=me; Password=xxxxxxxx")

        '2   Create the command object, passing in the SQL string
        Dim strSQL As String
        Dim ds As New DataSet()

        strSQL = "SELECT id,cust,amt,srep,(SELECT salesname FROM salesreps WHERE srep=SalesNum) AS RealName FROM weekly WHERE fcmonth>='" & queryDate & "' AND fcmonth <='" & queryDate & "'"
        strSQL &= "AND catg ='" & x & "'
       
        Dim da As New SqlDataAdapter(strSQL, MyConnection)
        'Set the datagrid's datasource to the datareader and databaind to control
        da.Fill(ds, "tblDetails")
        Return ds.Tables("tblDetails")
    End Function  

The sql statement from binddata is:
SELECT DISTINCT catg FROM weekly

Again, I'm interested in editing the sql from GetDetails function.  

ok, i think this is the last step
when you call the BindData() actually you have bind the MasterData control
what you need is to also bind the selected index for editing in dgDetails - so where do you call GetDetails? is must be somewhere in the BindData, correct ?

regards

No, actually, GetDetails is called from a nested datagrid:

<asp:DataGrid runat="server" ID="dgDetails" DataSource=<%# GetDetails(container.dataitem("catg"))%> AutoGenerateColumns="false" Width="100%" OnEditCommand="dgDetails_Edit" OnCancelCommand="dgDetails_Cancel" OnUpdateCommand="dgDetails_Update" OnItemDataBound="dgDetails_Identify">

I'm calling GetDetails from the DatSource property and sending the values for "catg" to it.  "catg" is obtained from the BindData function (the select distinct query listed above):

    'Create SQL Connection, query data, bind to sub datagrid control/nested
    Function GetDetails(ByVal x As String)
        'Set date to this month
        Dim queryDate As String = Right(CalendarPopup1.PostedDate, 4) & Left(CalendarPopup1.PostedDate, 2)

        '1.  Create a connection to the sql database
        Dim MyConnection As New SqlConnection("Server=x.x.x.x; Initial Catalog=mydata; User ID=me; Password=xxxxxxx")
        '2   Create the command object, passing in the SQL string
        Dim strSQL As String
        Dim ds As New DataSet()

        strSQL = "SELECT id,cust,amt,srep,(SELECT salesname FROM salesreps WHERE srep=SalesNum) AS RealName FROM forecastweekly WHERE fcmonth>='" & queryDate & "' AND fcmonth <='" & queryDate & "'"
        strSQL &= "AND catg ='" & x & "'
       
        Dim da As New SqlDataAdapter(strSQL, MyConnection)

        'Set the datagrid's datasource to the datareader and databaind to control
        da.Fill(ds, "tblDetails")
        Return ds.Tables("tblDetails")
    End Function

The data in the function above is what I need to edit.  The outer datagrid is just categories.  In the inner grid are all of the data items under each seperate category.  

We're very close.....

I haven't catch why the comment from Date: 11/13/2006 05:51PM EET haven't help
if the data is ok in the MasterData grid then in the dgDetails must be ok too

can you post the BindData() sub?

regards

Here's the sub:

   'Create SQL Connection, query data, bind to control.
    Sub BindData()
        Dim queryDate As String = Right(CalendarPopup1.PostedDate, 4) & Left(CalendarPopup1.PostedDate, 2)
        Dim warehouse As String = ddlPlant.SelectedItem.Value

       '1.  Create a connection to the sql database
        Dim MyConnection As New SqlConnection("Server=x.x.x.x; Initial Catalog=mydata; User ID=me; Password=xxxxxx")
        '2   Create the command object, passing in the SQL string
   
   Const strSQL As String = "SELECT DISTINCT catg FROM weekly WHERE fcmonth>=@mydate AND fcmonth <=@mydate AND whs=@location"
           
            Dim MyCommand As New SqlCommand(strSQL, MyConnection)
            MyCommand.Parameters.AddWithValue("@mydate", queryDate)
            MyCommand.Parameters.AddWithValue("@location", warehouse)
           
            'Set the datagrid's datasource to the datareader and databaind to control
            MyConnection.Open()
            dgMasterData.DataSource = MyCommand.ExecuteReader(CommandBehavior.CloseConnection)
            dgMasterData.DataBind()
            MyConnection.Close()

    End Sub