updating from datagrid

J

jason

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+"-"+swaitingon+"-"+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,@comments)", 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>
 
J

jason

I can make the update work with fixed values. all the s variables are
getting set, only the @chain gets set however, all others @varialbes
either revert back or are never passed to the update. any idea?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

?? dropdownlist in datagrid, not defaulting to current value ??? THANK YOU 2
Updating an Access Yes/No field from a datagrid 1
Validation Control in Materpage\Conentpage\ContentPlaceHolder 1
[Newbie Problem]DataGrid OnUpdateCommand "InvalidCastException" problem 2
Incorrect syntax near 'nvarchar'. 2
enter-key works with two inputboxes but not with olny one. 2
OnItemCommand & OnItemDataBound 2
Button to call another Aspx page 2

Top