Can't get datagrid to Update record using UpdateCommand

[D. Shane Fowlkes]

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:

**************************************************
<aspataGrid 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>
</aspataGrid>
**************************************************

 

[Scott M.]

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]

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

 

[Scott M.]

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.