473,225 Members | 1,338 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,225 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 2172
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...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

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.