473,324 Members | 2,356 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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 2180
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Chumley the Walrus | last post by:
IN my code behind .vb page for a delete records script (this also does a deletion confirmation with a javascript popup, this gets called on my front .aspx page with the datagrid), I'm not sure if...
1
by: Rick | last post by:
Hello all, I hope all is well with you. I am having a seriously difficult time with this problem. Allow me to set up the problem. I have a System.Web.UI.Page with the following controls...
1
by: MrMike | last post by:
Hi. My application has dozens of datagrids but for some reason an exception occurs when one of them is updated. When a user edits a datagrid row and then clicks 'Update' the following exception...
4
by: siaj | last post by:
Hello All, If some one has faced a similar issue.. My datagrid Update command is not getting fired in fact it seems that the no event fires on clicking the update link. Although the cancel and the...
1
by: Paul_FFX | last post by:
I try to use EditCommandColumn of Datagrid control to update a record. EditCommand works and Update and Cancel button appear. Then a modify text in one of appeared text boxes. There is a part of my...
2
by: johnb41 | last post by:
I have a very simple application where i just want to display records from a database, and be able to edit the records. Here's the code for the "update" button: Private Sub...
2
by: Derek Vincent | last post by:
What must I do to overcome a problem with my dates becoming formatted as "2/22/2525 12:00:00 AM" in the datagrid? I want to handle all dates as short string of format "2/22/2525." Otherwise when I...
2
by: Hexman | last post by:
Hello All, Well I'm stumped once more. Need some help. Writing a simple select and update program using VB.Net 2005 and an Access DB. I'm using parameters in my update statement and when trying...
0
by: arlie_maija | last post by:
Hey - I'm writing a control that contains a DataGrid, and I'm unable to get the update event to fire. When I click the update link, the edit event fires. heres the details... my control...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.