I have successfully made a datagrid pulling data from an access database. However, when I try to update a field, it doesn't work properly, it just updates the record with what was previously in there. For example, if the record is "Tom" and I try to change it to "Frank" it doesn't work, it will update with "Tom". My question is, what is wrong with my code that is preventing me from updating with a new value?
Code:
<%@ Page Language="VB" Debug="true"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script runat="server">
sub Page_Load
BindData
End sub
public Sub BindData()
Dim dbconn,sql,dbcomm,dbread
dbconn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("content.mdb"))
dbconn.Open()
sql = "SELECT * FROM users"
dbcomm = New OleDbCommand(sql,dbconn)
dbread = dbcomm.ExecuteReader()
usersInfo.DataSource = dbread
usersInfo.DataBind()
dbconn.Close()
end sub
Public Sub DataGrid_Edit(Source As Object, E As DataGridCommandEventArgs)
usersInfo.EditItemIndex = E.Item.ItemIndex
BindData
End Sub
Public Sub DataGrid_Cancel(Source As Object, E As DataGridCommandEventArgs)
usersInfo.EditItemIndex = -1
BindData
End Sub
Public Sub DataGrid_Delete(Source As Object, E As DataGridCommandEventArgs)
Dim myConnection As OleDbConnection
Dim myCommand As OleDbCommand
Dim strUpdateStmt As String
myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("content.mdb"))
strUpdateStmt = "DELETE FROM users WHERE ID = " & E.Item.ItemIndex
myCommand = New OleDbCommand(strUpdateStmt,myConnection)
myConnection.Open()
myCommand.ExecuteNonQuery()
usersInfo.EditItemIndex = -1
BindData()
End Sub
Public Sub DataGrid_Update(Source As Object, E As DataGridCommandEventArgs)
Dim myConnection As OleDbConnection
Dim myCommand As OleDbCommand
Dim txtFirstName As TextBox = E.Item.Cells(3).Controls(0)
Dim strUpdateStmt As String
myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("content.mdb"))
strUpdateStmt = "UPDATE users SET FirstName = '" & txtFirstName.Text & "' WHERE ID = " & E.Item.Cells(2).Text
response.write(strUpdateStmt)
myCommand = New OleDbCommand(strUpdateStmt,myConnection)
myConnection.Open()
myCommand.ExecuteNonQuery()
usersInfo.EditItemIndex = -1
BindData
End Sub
</script>
<html>
<body>
<form runat="server">
<H3>Editing Records in the DataGrid</H3>
<asp:DataGrid id="usersInfo" runat="server" AutoGenerateColumns="False" OnEditCommand="DataGrid_Edit" OnCancelCommand="DataGrid_Cancel" OnUpdateCommand="DataGrid_Update" OnDeleteCommand="DataGrid_Delete">
<Columns>
<asp:EditCommandColumn ButtonType="LinkButton" CancelText="Cancel" EditText="Edit" UpdateText="Update" />
<asp:ButtonColumn Text="Delete" CommandName="Delete" />
<asp:BoundColumn DataField="ID" HeaderText="ID" ReadOnly="True" />
<asp:BoundColumn DataField="FirstName" HeaderText="FirstName" />
</Columns>
</asp:DataGrid>
</form>
</body>
</html>
Thanks,
Jeff M
Update Access Database with datagrid
