problem updating in datagrid ??

[jason@cyberpine.com]

The guts of the below asp.net vb code was pieced together from another
thread - all due credit to it's original author. Thank you!


I've modified it to maintain a small local Microsoft 2000 access DB
via a datagrid control.


The add and delete functions work great, but the edit does not actually
update the database. I put in some displays and apparently on the first
item in the grid gets set during editing, all others come back empty???



Any help or information is appreciated..


<code snippet below>


%@ Page Language="VB" Debug=true%>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<%@ import Namespace="System.String" %>
<%@ import Namespace="System.Web.Mail" %>


<script runat="server">


Sub Page_Load(sender As Object, e As EventArgs)
If Not IsPostBack Then
BindData()
End If
End Sub


Public Sub BindData()
Dim objConn as new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=c:\webroot\mydb.mdb")
objConn.Open()
Dim oaUser As OleDbDataAdapter
Dim UserDS as DataSet = New DataSet()
oaUser = New OleDbDataAdapter("Select * FROM appworx", objConn)


oaUser.Fill(UserDS,"appworx")
objConn.Close


UserGrid.DataSource = UserDS.Tables("appworx")
UserGrid.DataBind()
End Sub


Public Sub UserGrid_Edit (Source As Object, E As
DataGridCommandEventArgs)
UserGrid.EditItemIndex = E.Item.ItemIndex
BindData()
End Sub


Public Sub UserGrid_Cancel (Source As Object, E As
DataGridCommandEventArgs)
UserGrid.EditItemIndex = -1
BindData()
End Sub


Public Sub UserGrid_Update (Source As Object, E As
DataGridCommandEventArgs)
Dim objConn as new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=c:\webroot\mydb.mdb")
Dim cmd As OleDbCommand = new OleDbCommand ("UPDATE appworx SET

responsible=@responsible, status=@status, waitingon=@waitingon,
comments=@comments WHERE chain = @chain", objConn)


Dim schain As String = e.Item.Cells(2).Text
Dim sresponsible As String = e.Item.Cells(3).Text
Dim sstatus As String = e.Item.Cells(4).Text
Dim swaitingon As String = e.Item.Cells(5).Text
Dim scomments As String = e.Item.Cells(6).Text


response.write(schain+"-"+sresponsible+"-"+sstatus+"-"+swait*ingon+"-"+scomments)



cmd.Parameters.Add(new OleDbParameter("@chain", schain))
cmd.Parameters.Add(new OleDbParameter("@responsible",
sresponsible))
cmd.Parameters.Add(new OleDbParameter("@status", sstatus))
cmd.Parameters.Add(new OleDbParameter("@waitingon", swaitingon))



cmd.Parameters.Add(new OleDbParameter("@comments", scomments))


objConn.Open()
cmd.ExecuteNonQuery()
objConn.Close


UserGrid.EditItemIndex = -1
BindData()


End Sub


Public Sub UserGrid_Command(sender As Object, e As
DataGridCommandEventArgs)
Select (CType(e.CommandSource, LinkButton)).CommandName


Case "Delete"
Dim objConn as new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=c:\webroot\mydb.mdb")
Dim cmd As OleDbCommand = new OleDbCommand("DELETE FROM
appworx WHERE chain = @chain", objConn)


cmd.Parameters.Add(new OleDbParameter("@chain",
e.Item.Cells(2).Text))
objConn.Open()
cmd.ExecuteNonQuery()
objConn.Close
Case Else
' Do Nothing


End Select


BindData()
End Sub


Public Sub UserGrid_ItemCreated(sender As Object, e As
DataGridItemEventArgs)
Select Case e.Item.ItemType
Case ListItemType.Item, ListItemType.AlternatingItem,
ListItemType.EditItem


' Add confirmation to Delete button
Dim tblCell As TableCell
Dim btnDelete As LinkButton


tblCell = e.Item.Cells(1)
btnDelete = tblCell.Controls(0)
btnDelete.Attributes.Add("onclick", "return confirm('Are you
sure you want to delete?');")
End Select
End Sub


Public Sub AddUser_Click(sender As Object, e As EventArgs)


chainLabel.Visible = true
responsibleLabel.Visible = true
statusLabel.Visible = true
waitingonLabel.Visible = true
commentsLabel.Visible = true
addchain.Visible = true
addresponsible.Visible = true
addstatus.Visible = true
addwaitingon.Visible = true
addcomments.Visible = true
AddNewUser.Visible = true
AddCancel.Visible = true


End Sub


Public Sub AddNewUser_Click(sender As Object, e As EventArgs)
Dim objConn as new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=c:\webroot\mydb.mdb")
Dim cmd As OleDbCommand = new OleDbCommand("INSERT INTO appworx
(chain, responsible,status,waitingon,comments)
values(@chain,@responsible,@status,@waitingon,@com ments)", objConn)


cmd.Parameters.Add(new OleDbParameter("@responsible",
Addresponsible.Text))
cmd.Parameters.Add(new OleDbParameter("@chain", Addchain.Text))
cmd.Parameters.Add(new OleDbParameter("@status", Addstatus.Text))



cmd.Parameters.Add(new OleDbParameter("@waitingon",
Addwaitingon.Text))
cmd.Parameters.Add(new OleDbParameter("@comments",
Addcomments.Text))


objConn.Open()
cmd.ExecuteNonQuery()
objConn.Close


chainLabel.Visible = false
responsibleLabel.Visible = false
statusLabel.Visible = false
waitingonLabel.Visible = false
commentsLabel.Visible = false


BindData()
End Sub


Public Sub AddCancel_Click(sender As Object, e As EventArgs)


chainLabel.Visible = false
responsibleLabel.Visible = false
statusLabel.Visible = false
waitingonLabel.Visible = false
commentsLabel.Visible = false


addchain.Visible = false
addresponsible.Visible = false
addstatus.Visible = false
addwaitingon.Visible = false
addcomments.Visible = false


AddNewUser.Visible = false
AddCancel.Visible = false


' Reset text fields (for next time)
addchain.text = ""
addresponsible.text = ""
addstatus.text = ""
addwaitingon.text = ""
addcomments.text = ""


End Sub


</script>


<html>
<head>
</head>
<body>
<form method="post" runat="server">
<asp:button id="AddUser_Button" Text="Add New User" runat="server"
onClick="AddUser_Click" />
<table border=0>
<tr><td>
<asp:datagrid id="UserGrid" runat=server AutoGenerateColumns=false
BorderStyle="Dotted" BorderWidth="2"
BackgroundColor="red"
CellPadding="5"
Font-Name="Arial" Font-Size="8pt"
OnEditCommand="UserGrid_Edit"
OnCancelCommand="UserGrid_Cancel"
OnUpdateCommand="UserGrid_Update"
OnItemCommand="UserGrid_Command"
OnItemCreated="UserGrid_ItemCreated">


<HeaderStyle BackColor="#aaaadd">
</HeaderStyle>


<EditItemStyle BackColor="yellow">
</EditItemStyle>


<ItemStyle Wrap="false">
</ItemStyle>


<Columns>
<asp:EditCommandColumn
ButtonType ="LinkButton"
CancelText = "Cancel"
EditText = "Edit"
UpdateText = "Update">
</asp:EditCommandColumn>


<asp:ButtonColumn
HeaderText="Delete?"
ButtonType="LinkButton"
Text="Delete"
CommandName="Delete"/>
<asp:BoundColumn
DataField = "chain"
HeaderText = "chain"
ReadOnly = true />
<asp:BoundColumn
DataField = "responsible"
HeaderText = "responsible"/>
<asp:BoundColumn
DataField = "status"
HeaderText = "status"/>
<asp:BoundColumn
DataField = "waitingon"
HeaderText = "waitingon"/>
<asp:BoundColumn
DataField = "comments"
HeaderText = "comments"/>
</Columns>


</asp:datagrid>
</td><td valign="top">
<table border=0 bgcolor=yellow>
<tr><td><asp:label id="chainLabel" Text="chain:"
visible="false" runat="server" /></td>
<tr><td><asp:textbox id="Addchain" runat="server"
visible="false" /></td>
<tr><td><asp:label id="responsibleLabel" Text="responsible:"
visible="false" runat="server" /></td>
<tr><td><asp:textbox id="Addresponsible" runat="server"
visible="false" /></td>
<tr><td><asp:label id="statuslabel" Text="status:"
visible="false" runat="server" /></td>
<tr><td><asp:textbox id="Addstatus" runat="server"
visible="false"/></td>
<tr><td><asp:label id="waitingonLabel" Text="waiting on:"
visible="false" runat="server" /></td>
<tr><td><asp:textbox id="Addwaitingon" runat="server"
visible="false" /></td>
<tr><td><asp:label id="commentsLabel" Text="comments:"
runat="server" visible="false" /></td>
<tr><td><asp:textbox id="Addcomments" runat="server"
visible="false" /></td>
<tr><td><asp:button id ="AddNewUser" Text="Add New Record"
runat="server" visible="false" onClick="AddNewUser_Click" />
<td><asp:button id ="AddCancel" Text="Cancel" runat="server"
visible="false" onClick="AddCancel_Click"/>
</table>


</td></table>
</form>

[Roy]

Your update is wrong. The reason it works for the first one is because
your boundcolumn is set to readonly = "true."

"Dim schain As String = e.Item.Cells(2).Text "
will work only if the cell is readonly.

The remainder should be:
"Dim sresponsible As String = CType(e.Item.Cells(3).Controls(0),
TextBox).Text"

Good luck!

[jason@cyberpine.com]

Roy wrote:

> Your update is wrong. The reason it works for the first one is

because

> your boundcolumn is set to readonly = "true."
>
> "Dim schain As String = e.Item.Cells(2).Text "
> will work only if the cell is readonly.
>
> The remainder should be:
> "Dim sresponsible As String = CType(e.Item.Cells(3).Controls(0),
> TextBox).Text"
>
> Good luck!

Thank you . The response.write display now matched what I changed, but
the record still does update.

[Roy]

Good to hear it's all better now!