Hi,

I have a datagrid that I am updating using a StoredProcedure that queries multiple tables. I have Edit, Save and Cancel buttons in the EditItemTemplate of the DataGrid that I am accessing in the ItemCommad event of datagrid. The datagrid also contains a dropdownlist that I am populating from another table in the database. I am able to make the datagrid column editable by clicking the Edit Button, save changes to the dataset using the Save Button. Multiple rows of the datagrid can be updated and the dataset reflects these changes.
Saying this, the problem I now have is..........
I tried to update the SQL Server 2000 database table using a SqlDataAdapter, and SqlCommandBuilder in a single button click event so that all the changes to the grid are posted to the database at once, but get an exception "Dynamic SQL Generation is not supported against Multiple based Tables" . What needs to be done to update the single database table from a dataset that consists values from multiple tables at once.
Second and the major issue I am having is
The user doesnot want to have any buttons in the datagrid, they say that the DropDownList should be accessible for editing by a mouse click. In this case the values selected from the dropdown should be saved to the dataset and the dataset should update the database table at one click event.

The current code snippet I am using is..........

aspx...........

<td vAlign="top">
<div style="BORDER-RIGHT: #00006f thin solid; BORDER-TOP: #00006f thin solid; BACKGROUND-IMAGE: none; BORDER-LEFT: #00006f thin solid; WIDTH: 99.2%; BORDER-BOTTOM: #00006f thin solid; HEIGHT: 200px; BACKGROUND-COLOR: #d2d2d2" align="center"><asp:datagrid id="dgReceivingInfo" runat="server" Width="888px" EnableViewState="True" BorderStyle="Solid" BorderColor="Black" AutoGenerateColumns="False" HorizontalAlign="Justify" PageSize="15" BorderWidth="1" cssclass="tblMain" headerCSS="tblHeader" bodyCSS="tblBody" bodyHeight="190">
<Columns>
<asp:TemplateColumn HeaderText="Add Comment" HeaderStyle-HorizontalAlign="Center" HeaderStyle-Width="75px"
ItemStyle-Width="75px" ItemStyle-HorizontalAlign="Center" HeaderStyle-BorderStyle="Solid" HeaderStyle-BorderWidth="1"
ItemStyle-BorderStyle="Solid" ItemStyle-BorderWidth="1">
       <HeaderStyle></HeaderStyle>
       <ItemStyle Height="20px"></ItemStyle>
       <ItemTemplate>
       <Center>
     <asp:button id="btnEdit" Text="Edit" runat="server" Font-Size="0.7em" Height="18px" width="60px"
     EnableViewState="True" Enabled="true" CommandName="Edit" />
       </Center>
       </ItemTemplate>
       <EditItemTemplate>
     <asp:button id="btnSave" Text="Save" runat="server" Font-Size="0.7em" Height="18px" width="30px"
     EnableViewState="True" Enabled="true" CommandName="Save" />
     <asp:button id="btnCancelChanges" Text="Cancel" runat="server" Font-Size="0.7em" Height="18px"
     width="35px" EnableViewState="True" Enabled="true" CommandName="Cancel" />
       </EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn Visible="False">
       <Itemtemplate>
     <asp:Label Runat = "server" ID = "lblReceiptID" Text='<%# DataBinder.Eval(Container.DataItem, "ReceiptID")%>'>
     </asp:Label>
       </Itemtemplate>
</asp:TemplateColumn>
<asp:TemplateColumn Visible="False">
       <Itemtemplate>
     <asp:Label Runat = "server" ID = "lblPOID" Text='<%# DataBinder.Eval(Container.DataItem, "POID")%>'>
     </asp:Label>
       </Itemtemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="PO Line #" HeaderStyle-BorderStyle="Solid" HeaderStyle-BorderWidth="1"
ItemStyle-BorderStyle="Solid" ItemStyle-HorizontalAlign="Left" ItemStyle-BorderWidth="1">
       <HeaderStyle HorizontalAlign="Center" BackColor="LightGray" Width="75px"></HeaderStyle>
       <ItemStyle Height="20px" Width="75px"></ItemStyle>
       <ItemTemplate>
     <asp:Label Runat = "server" ID = "lblPOLineNo" Height = "20px" Width = "72px" text='<%# DataBinder.Eval
                 (Container.DataItem,"LineNumber") %>'>
     </asp:Label>
        </ItemTemplate>
        <EditItemTemplate>
        <center>
     <asp:TextBox Font-Size="10" Runat = "server" ID = "txtPOLineNo" Height = "19px" Width = "72px" text='<%#
                  DataBinder.Eval(Container.DataItem,"LineNumber") %>' >
     </asp:TextBox>
        </center>
        </EditItemTemplate>
</asp:TemplateColumn>
        <asp:TemplateColumn HeaderText="PO Item #" HeaderStyle-BorderStyle="Solid" HeaderStyle-BorderWidth="1"
     ItemStyle-BorderStyle="Solid" ItemStyle-HorizontalAlign="Left" ItemStyle-BorderWidth="1">
        <HeaderStyle HorizontalAlign="Center" BackColor="LightGray" Width="75px"></HeaderStyle>
        <ItemStyle Width="75px"></ItemStyle>
        <ItemTemplate>
     <asp:label Runat = "server" ID="lblItemID" Height="20px" Width = "72px" text='<%# DataBinder.Eval
                  (Container.DataItem,"ItemID") %>'>
     </asp:label>
        </ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Item Description" HeaderStyle-BorderStyle="Solid" HeaderStyle-BorderWidth="1"
ItemStyle-BorderStyle="Solid" ItemStyle-HorizontalAlign="Left" ItemStyle-BorderWidth="1">
         <HeaderStyle HorizontalAlign="Center" BackColor="LightGray" Width="350px"></HeaderStyle>
         <ItemStyle Width="350px"></ItemStyle>
         <ItemTemplate>
     <asp:label Runat = "server" ID="lblItemDesc" Height="20px" Width = "347px" text='<%# DataBinder.Eval
                  (Container.DataItem,"Description") %>'>
     </asp:label>
          </ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Quantity Received" HeaderStyle-BorderStyle="Solid" HeaderStyle-BorderWidth="1"
ItemStyle-BorderStyle="Solid" ItemStyle-HorizontalAlign="Right" ItemStyle-BorderWidth="1">
        <HeaderStyle HorizontalAlign="Center" BackColor="LightGray" Width="100px"></HeaderStyle>
        <ItemStyle Width="100px"></ItemStyle>
        <ItemTemplate>
     <asp:Label Runat = "server" ID="lblQtyReceived" Height="20px" Width="97px" text='<%# DataBinder.Eval
                  (Container.DataItem,"Quantity") %>'>
     </asp:Label>
         </ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Receiving Comments" HeaderStyle-BorderStyle="Solid" HeaderStyle-BorderWidth="1"
HeaderStyle-HorizontalAlign="Center" ItemStyle-BorderStyle="Solid" ItemStyle-HorizontalAlign="Right"
ItemStyle-BorderWidth="1">
         <HeaderStyle BackColor="LightGray" Width="220px"></HeaderStyle>
         <ItemStyle Height="20px" Width="220px"></ItemStyle>
        <ItemTemplate>
     <asp:label Enabled="True" Font-Size="8" ID="lblRecvComments_values" Text='<%# DataBinder.Eval
                  (Container.DataItem,"ReceivingComments") %>' Width="220px" Runat="server" Height="20px">
     </asp:label>
         </ItemTemplate>
         <EditItemTemplate>
     <asp:DropDownList Enabled="True" Font-Size="8" ID="ddRecvComments" Width="220px" Runat="server"
                    Height="20px" DataSource="<%# TempDataView %>" DataValueField="ReceivingComments"
                    DataTextField="ReceivingComments">
     </asp:DropDownList>
          </EditItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:datagrid>


VB.NET.............


Protected TempDataView As DataView

Private Sub dgReceivingInfo_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs ) Handles dgReceivingInfo.ItemCommand

If e.CommandName = "Edit" Then

    LoadDropDownList()
    Dim strSelect As String = dgReceivingInfo.Items(e.Item.ItemIndex).Cells.ToSt ring

    dgReceivingInfo.EditItemIndex = e.Item.ItemIndex
    dgReceivingInfo.DataSource = Session("ds_PO")
    dgReceivingInfo.DataBind()
Else

    If e.CommandName = "Save" Then
     Dim arEdit() As DataRow
     Dim txtPOLineNo As TextBox
     txtPOLineNo = e.Item.Cells(3).FindControl("txtPOLineNo")
     Dim dd_RecvComments As DropDownList
     dd_RecvComments = e.Item.Cells(7).FindControl("ddRecvComments")
     Dim lblReceiptID As Label
     lblReceiptID = e.Item.Cells(1).FindControl("lblReceiptID")
     Dim lblPOID As Label
     lblPOID = e.Item.Cells(2).FindControl("lblPOID")
     Dim lblItemID As Label
     lblItemID = e.Item.Cells(4).FindControl("lblItemID")


     arEdit = Session("ds_PO").Tables(0).Select("ReceiptID='" + lblReceiptID.Text + "' AND POID='" + lblPOID.Text + "' AND ItemID='" + lblItemID.Text + "'")
     arEdit(0)("ReceivingComments") = dd_RecvComments.SelectedValue.ToString

     arEdit(0)("LineNumber") = txtPOLineNo.Text


     Session("ds_PO").Tables(0).Rows.Count()
     dgReceivingInfo.EditItemIndex = -1

     dgReceivingInfo.DataSource = Session("ds_PO").Tables(0)
     dgReceivingInfo.DataBind()

    Else
     If e.CommandName = "Cancel" Then
         dgReceivingInfo.EditItemIndex = -1
         dgReceivingInfo.DataSource = Session("ds_PO").Tables(0)
         dgReceivingInfo.DataBind()
     End If
    End If

End If
End Sub


Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
    UpdateReceivingInfo()
End Sub

Public Sub LoadDropDownList()

Dim drv As DataRowView

Dim ds_DropDownList = New DataSet
Dim da As SqlDataAdapter
Dim conn As SqlConnection = clsConnection.GetConnection()

Dim cmd As SqlCommand = New SqlCommand("Select * from t_ReceivingComments", conn)
da = New SqlDataAdapter(cmd)
da.Fill(ds_DropDownList, "t_ReceivingComments")
Dim y As Int32 = ds_DropDownList.Tables("t_ReceivingComments").Rows .Count
'Session.Add("ds_DropDownList", ds_DropDownList)

TempDataView = New DataView(ds_DropDownList.Tables("t_ReceivingCommen ts"))
drv = TempDataView.AddNew
drv("ReceivingComments") = ""

End Sub

Public Function UpdateReceivingInfo()
Try

    Dim da As SqlDataAdapter = Session("da")

    Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)
    Dim ds As DataSet = Session("ds_PO")

    da.Update(ds)
    ds.Tables("dt").AcceptChanges()
    Return True

Catch ex As Exception
    Return False
End Try
End Function


Thanks!

It looks as if you will have to loop through each row of the grid, grab the values and perform multiple update statements, a separate update for each table that is affected.

Can you provide me with a sample showing how to do this?

Thanks!

Somthing like this:

Dim i As Integer

For i = 0 To dgTest.Items.Count - 1
   'Grab values you need from the grid.
   <var> = dgTest.Items(i).Cells(<cell #>).Text
   <var2> = ... etc. Do this for each value you need.

   'Do your Update statements here....
Next

I was able to update the table with multiple rows of data at once using the SQLDataAdaptor, SelectCommand and UpdateCommand, but am getting stuck with concurrency violation exception for one particular record even though I am the only user accessing the table. The code snippet for the update routine is as follows any advice or a better approach is requested.
Thanks!


Public Function UpdateReceivingInfo()
        Dim conn As SqlConnection = clsConnection.GetConnection()
        Try

            Dim da As SqlDataAdapter

            da = New SqlDataAdapter("spPOReceivingInfoSelect", conn)
            da.SelectCommand.CommandType = CommandType.StoredProcedure

            With da.SelectCommand.Parameters
                .Add(New System.Data.SqlClient.SqlParameter("@ReceiptID", System.Data.SqlDbType.VarChar, 10, "ReceiptID"))
                .Add(New System.Data.SqlClient.SqlParameter("@POID", System.Data.SqlDbType.VarChar, 10, "POID"))
                .Add(New System.Data.SqlClient.SqlParameter("@ItemID", System.Data.SqlDbType.VarChar, 5, "ItemID"))
            End With

            da.UpdateCommand = New SqlCommand(Nothing, conn)
            With da.UpdateCommand
                .CommandText = "UPDATE t_POReconciliation SET ReceivingComments = @ReceivingComments WHERE (ReceiptID = @ReceiptID) AND (POID=@POID) AND (ItemID = @ItemID); SELECT POReconID AS POReconID, POID, wh_id, ReceiptID, LineNumber, ItemID, StatusID, ReceiptDate, ReceivingComments, BuyerComments, LastModifiedDate, LastModifiedUser, TimeStamp FROM t_POReconciliation WHERE (ReceiptID = @ReceiptID) AND (POID = @POID) AND (ItemID = @ItemID)"
                .Parameters.Add(New System.Data.SqlClient.SqlParameter("@ReceiptID", System.Data.SqlDbType.VarChar,
                 10, "ReceiptID"))
                .Parameters.Add(New System.Data.SqlClient.SqlParameter("@POID", System.Data.SqlDbType.VarChar,
                 10, "POID"))
                .Parameters.Add(New System.Data.SqlClient.SqlParameter("@ItemID", System.Data.SqlDbType.VarChar,
                       5, "ItemID"))
                .Parameters.Add(New System.Data.SqlClient.SqlParameter("@ReceivingComm ents",
                 System.Data.SqlDbType.VarChar, 256, "ReceivingComments"))
           End With
            Dim ds As DataSet = Session("ds_PO")
            da.Update(ds)
            ds.AcceptChanges()
            Return True

        Catch ex As Exception
            Return False
        Finally
            conn.Close()
        End Try
    End Function

The following line of code

.CommandText = "UPDATE t_POReconciliation SET ReceivingComments = @ReceivingComments WHERE (ReceiptID = @ReceiptID) AND (POID=@POID) AND (ItemID = @ItemID); SELECT POReconID AS POReconID, POID, wh_id, ReceiptID, LineNumber, ItemID, StatusID, ReceiptDate, ReceivingComments, BuyerComments, LastModifiedDate, LastModifiedUser, TimeStamp FROM t_POReconciliation WHERE (ReceiptID = @ReceiptID) AND (POID = @POID) AND (ItemID = @ItemID)"

is actually

.CommandText = "UPDATE t_POReconciliation SET ReceivingComments = @ReceivingComments WHERE (ReceiptID = @ReceiptID) AND (POID=@POID) AND (ItemID = @ItemID); SELECT POReconID AS POReconID, POID, wh_id, ReceiptID, LineNumber, ItemID, StatusID, ReceiptDate, ReceivingComments, BuyerComments, LastModifiedDate, LastModifiedUser FROM t_POReconciliation WHERE (ReceiptID = @ReceiptID) AND (POID = @POID) AND (ItemID = @ItemID)"

There is no column named TimeStamp in the SELECT

Thanks!

What is the actual error you are getting? Also, are you preforming the update in a loop?

The actual error I am getting is
Concurrency VIOLATION Number of Rows Updated = 0

No I am not using any looping, I am updating the database table via the SQLDataAdaptor Select and Update Commands.
These commands post the changes from the updated dataset to the database table.