By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,218 Members | 1,111 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,218 IT Pros & Developers. It's quick & easy.

updating from datagrid

P: n/a
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,@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>

Nov 19 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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?

Nov 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.