468,771 Members | 1,707 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,771 developers. It's quick & easy.

Can't get datagrid to Update record using UpdateCommand

I have a Datagrid which in theory, should allow you to edit and update the
records. I've stripped my test page down so that it's only attempting to
update one field - "description". Yet when I make the changes and click
Update, it reverts back to the old value. I'm stumped! I've also checked
the db table and it is infact not updating. I'm attempting to follow and
example right out of ASP.NET Unleashed but not having much luck. Any
pointers would be appreciated.

My Update Sub Routine:

**************************************************
Sub dgLineItems_UpdateCommand(S As Object, E As DataGridCommandEventArgs)

Dim intReqID As Integer

intReqID = CInt(Request.QueryString("id"))

If intReqID < 1 Then
Response.Redirect("reqform.aspx")
End If
Dim conSqlServer As SqlConnection
Dim strUpdateSQL As String
Dim cmdUpdate As SqlCommand
Dim strConnectString As String

Dim intLineItemID As Integer
Dim txtDescription As TextBox
Dim strDescription As String

intLineItemID = dgLineItems.DataKeys(E.Item.ItemIndex)

txtDescription = E.Item.Cells(1).Controls(0)

strDescription = txtDescription.Text

strConnectString = "server=drpt-server3; database=DRPT-TEST; uid=xxx"
strUpdateSQL = "UPDATE POItems SET Description = '" & strDescription & "'
WHERE ID = " & intLineItemID
conSqlServer = New SqlConnection(strConnectString)
cmdUpdate = New SqlCommand(strUpdateSQL, conSqlServer)

conSqlServer.Open()
cmdUpdate.ExecuteNonQuery()
conSqlServer.Close()

dgLineItems.EditItemIndex = -1
CreateLineItemDisplay(intReqID)

lblTest.Text = strDescription

End Sub
**************************************************

My DataGrid:

**************************************************
<asp:DataGrid id="dgLineItems"
runat="server"
AutoGenerateColumns="false"
BorderWidth="1"
BorderColor="#36667C"
CellPadding="3"
HeaderStyle-CssClass="colheader"
Width="100%"
AlternatingItemStyle-BackColor="#CCCCCC"
DataKeyField="ID"
ItemStyle-CssClass="caption"
OnEditCommand="dgLineItems_EditCommand"
OnCancelCommand="dgLineItems_CancelCommand"
OnDeleteCommand="dgLineItems_DeleteCommand"
OnUpdateCommand="dgLineItems_UpdateCommand">
<columns>
<asp:BoundColumn HeaderText="&nbsp;" ItemStyle-Width="5%"
ReadOnly="true" />
<asp:BoundColumn HeaderText="Brief Description" DataField="Description"
ItemStyle-Width="35%" ItemStyle-CssClass="caption" />
<asp:BoundColumn HeaderText="QTY" DataFormatString="{0:N0}"
DataField="Quantity" ItemStyle-Width="5%" ItemStyle-CssClass="caption" />
<asp:BoundColumn HeaderText="Unit of Measure" DataField="UOM"
ItemStyle-Width="15%" ItemStyle-CssClass="caption" />
<asp:BoundColumn HeaderText="Unit Price" DataFormatString="{0:C2}"
DataField="UnitPrice" ItemStyle-Width="15%" ItemStyle-CssClass="caption" />
<asp:BoundColumn HeaderText="Total Cost" DataFormatString="{0:C2}"
DataField="AllocAmt" ItemStyle-Width="15%" ItemStyle-CssClass="caption"
ReadOnly="true" />
<asp:EditCommandColumn HeaderText="Edit" EditText="Edit"
UpdateText="Update" CancelText="Cancel" />
<asp:BoundColumn HeaderText="&nbsp;" ItemStyle-Width="5%"
ReadOnly="true" />
</columns>
</asp:DataGrid>
**************************************************
Nov 18 '05 #1
3 1986
First, I'd add a DataAdapter and have it and your connection configured as
page level items. This will eliminate the need for you to open and close
your connection since the DataAdapter does this for you anyway. Also,
rather than executing a query against your database, you should take
advantage of the disconnected data paradigm of .NET. What is your DataGrid
bound to as its data source? A DataSet? This would be the best approach.
Take the value that was edited from the DataGrid and modify your DataSet
data. Then just call the DataAdapter's .Update() method. Your
DataAdapter's UpdateCommand.CommandText needs to be configured with your SQL
Update statement first of course.

Also, when you change the data that any databound control is bound to, you
need to re-bind the control to the data if the data has changed. This is
easily done by calling the DataGrid's .DataBind() method.
"D. Shane Fowlkes" <sh**********@h-o-t-m-a-i-l.com> wrote in message
news:u$**************@tk2msftngp13.phx.gbl...
I have a Datagrid which in theory, should allow you to edit and update the
records. I've stripped my test page down so that it's only attempting to
update one field - "description". Yet when I make the changes and click
Update, it reverts back to the old value. I'm stumped! I've also checked
the db table and it is infact not updating. I'm attempting to follow and
example right out of ASP.NET Unleashed but not having much luck. Any
pointers would be appreciated.

My Update Sub Routine:

**************************************************
Sub dgLineItems_UpdateCommand(S As Object, E As DataGridCommandEventArgs)

Dim intReqID As Integer

intReqID = CInt(Request.QueryString("id"))

If intReqID < 1 Then
Response.Redirect("reqform.aspx")
End If
Dim conSqlServer As SqlConnection
Dim strUpdateSQL As String
Dim cmdUpdate As SqlCommand
Dim strConnectString As String

Dim intLineItemID As Integer
Dim txtDescription As TextBox
Dim strDescription As String

intLineItemID = dgLineItems.DataKeys(E.Item.ItemIndex)

txtDescription = E.Item.Cells(1).Controls(0)

strDescription = txtDescription.Text

strConnectString = "server=drpt-server3; database=DRPT-TEST; uid=xxx"
strUpdateSQL = "UPDATE POItems SET Description = '" & strDescription & "' WHERE ID = " & intLineItemID
conSqlServer = New SqlConnection(strConnectString)
cmdUpdate = New SqlCommand(strUpdateSQL, conSqlServer)

conSqlServer.Open()
cmdUpdate.ExecuteNonQuery()
conSqlServer.Close()

dgLineItems.EditItemIndex = -1
CreateLineItemDisplay(intReqID)

lblTest.Text = strDescription

End Sub
**************************************************

My DataGrid:

**************************************************
<asp:DataGrid id="dgLineItems"
runat="server"
AutoGenerateColumns="false"
BorderWidth="1"
BorderColor="#36667C"
CellPadding="3"
HeaderStyle-CssClass="colheader"
Width="100%"
AlternatingItemStyle-BackColor="#CCCCCC"
DataKeyField="ID"
ItemStyle-CssClass="caption"
OnEditCommand="dgLineItems_EditCommand"
OnCancelCommand="dgLineItems_CancelCommand"
OnDeleteCommand="dgLineItems_DeleteCommand"
OnUpdateCommand="dgLineItems_UpdateCommand">
<columns>
<asp:BoundColumn HeaderText="&nbsp;" ItemStyle-Width="5%"
ReadOnly="true" />
<asp:BoundColumn HeaderText="Brief Description" DataField="Description" ItemStyle-Width="35%" ItemStyle-CssClass="caption" />
<asp:BoundColumn HeaderText="QTY" DataFormatString="{0:N0}"
DataField="Quantity" ItemStyle-Width="5%" ItemStyle-CssClass="caption" />
<asp:BoundColumn HeaderText="Unit of Measure" DataField="UOM"
ItemStyle-Width="15%" ItemStyle-CssClass="caption" />
<asp:BoundColumn HeaderText="Unit Price" DataFormatString="{0:C2}"
DataField="UnitPrice" ItemStyle-Width="15%" ItemStyle-CssClass="caption" /> <asp:BoundColumn HeaderText="Total Cost" DataFormatString="{0:C2}"
DataField="AllocAmt" ItemStyle-Width="15%" ItemStyle-CssClass="caption"
ReadOnly="true" />
<asp:EditCommandColumn HeaderText="Edit" EditText="Edit"
UpdateText="Update" CancelText="Cancel" />
<asp:BoundColumn HeaderText="&nbsp;" ItemStyle-Width="5%"
ReadOnly="true" />
</columns>
</asp:DataGrid>
**************************************************

Nov 18 '05 #2
The datagrid is bound to a SQLDataReader.

As far as I know, I am rebinding it by calling
CreateLineItemDisplay(intReqID) at the end up the UpdateCommand routine. I
pasted this sub below. Can you see why my current structure does not work?
Sub CreateLineItemDisplay(intReqID As Integer)

'This sub routine is passed the ID of the requisition in progress (from
QueryString) and populates the
' Line Item Display (datagrid) with line items from the POItems table.

Dim conSqlServer As SqlConnection
Dim cmdLineItemData As SqlCommand
Dim strConnectString As String
Dim strSQL As String
Dim drLineItems As SqlDataReader

strConnectString = "server=drpt-server3; database=DRPT-TEST; uid=xxxx"

strSQL = "SELECT * FROM POItems WHERE PurchaseOrderID = " & intReqID & "
AND Returned = 0 ORDER BY ID"
conSqlServer = New SqlConnection(strConnectString)
cmdLineItemData = New SqlCommand(strSQL, conSQLServer)

conSqlServer.Open()
drLineItems = cmdLineItemData.ExecuteReader()
dgLineItems.DataSource = drLineItems
dgLineItems.DataBind()
drLineItems.Close()
conSqlServer.Close()

End Sub

Nov 18 '05 #3
But where are you getting the data from the first time the page loads?
Again, you should re-write your code to use a DataAdapter and a DataSet,
rather than creating, configuring, opening, executing, closing the
connection manually.
"D. Shane Fowlkes" <sh**********@h-o-t-m-a-i-l.com> wrote in message
news:O9*************@TK2MSFTNGP12.phx.gbl...
The datagrid is bound to a SQLDataReader.

As far as I know, I am rebinding it by calling
CreateLineItemDisplay(intReqID) at the end up the UpdateCommand routine. I pasted this sub below. Can you see why my current structure does not work?
Sub CreateLineItemDisplay(intReqID As Integer)

'This sub routine is passed the ID of the requisition in progress (from
QueryString) and populates the
' Line Item Display (datagrid) with line items from the POItems table.

Dim conSqlServer As SqlConnection
Dim cmdLineItemData As SqlCommand
Dim strConnectString As String
Dim strSQL As String
Dim drLineItems As SqlDataReader

strConnectString = "server=drpt-server3; database=DRPT-TEST; uid=xxxx"

strSQL = "SELECT * FROM POItems WHERE PurchaseOrderID = " & intReqID & "
AND Returned = 0 ORDER BY ID"
conSqlServer = New SqlConnection(strConnectString)
cmdLineItemData = New SqlCommand(strSQL, conSQLServer)

conSqlServer.Open()
drLineItems = cmdLineItemData.ExecuteReader()
dgLineItems.DataSource = drLineItems
dgLineItems.DataBind()
drLineItems.Close()
conSqlServer.Close()

End Sub

Nov 18 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by MrMike | last post: by
4 posts views Thread by siaj | last post: by
2 posts views Thread by Derek Vincent | last post: by
reply views Thread by arlie_maija | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
2 posts views Thread by Marin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.