Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 1.0 and 1.1 > ASP.NET 1.1
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
ASP.NET 1.1 As of 10/6/2005, this forum is locked as part of the reorganization described here: http://p2p.wrox.com/topic.asp?TOPIC_ID=35394. No posts have been deleted. Open ongoing discussions from the last week have been moved to either ASP.NET 1.0 and 1.1 Beginners http://p2p.wrox.com/asp-net-1-0-1-1-basics-60/ or ASP.NET 1.0 and 1.1 Professional. http://p2p.wrox.com/forum.asp?FORUM_ID=50. See my sticky post inside for more.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 1.1 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Display Modes
  #1 (permalink)  
Old July 20th, 2005, 06:28 PM
Authorized User
 
Join Date: Dec 2004
Location: teaneck, , USA.
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default ASP.NET datagrid Edit and Update with Click Event

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!
  #2 (permalink)  
Old July 21st, 2005, 12:03 AM
Friend of Wrox
Points: 4,332, Level: 27
Points: 4,332, Level: 27 Points: 4,332, Level: 27 Points: 4,332, Level: 27
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2003
Location: , NJ, USA.
Posts: 1,348
Thanks: 0
Thanked 5 Times in 5 Posts
Default

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.

  #3 (permalink)  
Old July 21st, 2005, 01:14 AM
Authorized User
 
Join Date: Dec 2004
Location: teaneck, , USA.
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Thanks!
  #4 (permalink)  
Old July 21st, 2005, 09:05 AM
Friend of Wrox
Points: 4,332, Level: 27
Points: 4,332, Level: 27 Points: 4,332, Level: 27 Points: 4,332, Level: 27
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2003
Location: , NJ, USA.
Posts: 1,348
Thanks: 0
Thanked 5 Times in 5 Posts
Default

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



  #5 (permalink)  
Old July 21st, 2005, 09:25 PM
Authorized User
 
Join Date: Dec 2004
Location: teaneck, , USA.
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #6 (permalink)  
Old July 21st, 2005, 09:29 PM
Authorized User
 
Join Date: Dec 2004
Location: teaneck, , USA.
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!
  #7 (permalink)  
Old July 21st, 2005, 11:09 PM
Friend of Wrox
Points: 4,332, Level: 27
Points: 4,332, Level: 27 Points: 4,332, Level: 27 Points: 4,332, Level: 27
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2003
Location: , NJ, USA.
Posts: 1,348
Thanks: 0
Thanked 5 Times in 5 Posts
Default

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

  #8 (permalink)  
Old July 21st, 2005, 11:54 PM
Authorized User
 
Join Date: Dec 2004
Location: teaneck, , USA.
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel sheet accessing(edit/update) in asp.net hemant.dna ASP.NET 1.0 and 1.1 Basics 0 January 31st, 2007 06:37 AM
Extra conditional update click event in FormView nuttylife2 ASP.NET 2.0 Professional 1 July 31st, 2006 08:00 PM
how to capture right click event of datagrid cell swati_joshi ASP.NET 1.0 and 1.1 Basics 0 April 18th, 2006 01:51 AM
DataGrid click event Clive Astley Beginning VB 6 0 July 4th, 2005 12:44 AM
how to edit and update my access db using asp.net method ASP.NET 1.0 and 1.1 Basics 4 April 25th, 2005 03:03 PM



All times are GMT -4. The time now is 01:58 PM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.