access database not updating

G

Gert Albertse

When I try to update my access database, nothing get updated. Here is my
code:

<%@ Page Language="VB" Debug="true" %>
<script runat="server">

' Insert page code here
'

Function UpdateCustomer(ByVal ProjectID As String, ByVal ProjectName
As String) As Integer
Dim connectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data
Source=D:\Websites\Demo\GI2005.mdb"
Dim dbConnection As System.Data.IDbConnection = New
System.Data.OleDb.OleDbConnection(connectionString)

Dim queryString As String = "UPDATE [TProject] SET
[ProjectName]=@ProjectName WHERE ([TProject].[ProjectID] = @Proj"& _
"ekID)"

Dim dbCommand As System.Data.IDbCommand = New
System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_ProjectID As System.Data.IDataParameter = New
System.Data.OleDb.OleDbParameter
dbParam_ProjectID.ParameterName = "@ProjectID"
dbParam_ProjectID.Value = ProjectID
dbParam_ProjectID.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_ProjectID)
Dim dbParam_ProjectName As System.Data.IDataParameter = New
System.Data.OleDb.OleDbParameter
dbParam_ProjectName.ParameterName = "@ProjectName"
dbParam_ProjectName.Value = ProjectName
dbParam_ProjectName.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_ProjectName)

Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try

Return rowsAffected
End Function



Function GetTProject() As System.Data.IDataReader
Dim connectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data
Source=D:\Websites\Demo\GI2005.mdb"
Dim dbConnection As System.Data.IDbConnection = New
System.Data.OleDb.OleDbConnection(connectionString)

Dim queryString As String = "SELECT [TProject].* FROM
[TProject]"
Dim dbCommand As System.Data.IDbCommand = New
System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

dbConnection.Open
Dim dataReader As System.Data.IDataReader =
dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

Return dataReader
End Function



Sub dgCustomers_Edit(sender as Object, e as
DataGridCommandEventArgs)
dgCustomers.EditItemIndex = e.Item.ItemIndex

dgCustomers.DataSource = GetTProject()
dgCustomers.DataBind()
End Sub


Sub dgCustomers_Update(sender as Object, e as
DataGridCommandEventArgs)

Dim ProjectID as String = e.Item.Cells(1).Text

Dim ProjectName as TextBox = e.Item.Cells(2).Controls(0)

UpdateCustomer(ProjectID, ProjectName.Text)

dgCustomers.EditItemIndex = -1

dgCustomers.DataSource = GetTProject()
dgCustomers.DataBind()
End Sub


Sub dgCustomers_Cancel(sender as Object, e as
DataGridCommandEventArgs)
dgCustomers.EditItemIndex = -1

dgCustomers.DataSource = GetTProject()
dgCustomers.DataBind()
End Sub


Sub Page_Load(sender as Object, e as EventArgs)
If Not Page.IsPostBack then
dgCustomers.DataSource = GetTProject()
dgCustomers.DataBind()
End If
End Sub

</script>
<html>
<head>
</head>
<body>
<form runat="server">
<p>
</p>
<p>
<asp:DataGrid id="dgCustomers" runat="server"
AutoGenerateColumns="False" OnCancelCommand="dgCustomers_Cancel"
OnUpdateCommand="dgCustomers_Update" OnEditCommand="dgCustomers_Edit">
<Columns>
<asp:EditCommandColumn ButtonType="LinkButton"
UpdateText="Update" CancelText="Cancel"
EditText="Edit"></asp:EditCommandColumn>
<asp:BoundColumn DataField="ProjectID" ReadOnly="True"
HeaderText="ProjectID"></asp:BoundColumn>
<asp:BoundColumn DataField="ProjectName"
HeaderText="ProjectName"></asp:BoundColumn>
</Columns>
</asp:DataGrid>
<!-- Insert content here -->
</p>
</form>
</body>
</html>
 
G

Guest

Hi Gert,

The probable reason is before executing UpdateCustomer
you run dgCustomers.DataSource = GetTProject(), e.g. in Page_Load. Hence data
changed by user, e.g. ProjectName, is overwritten by data source. The correct
approach is to only bind datagrid with data source when
it's not postback:

If Not IsPostback Then
dgCustomers.DataSource = GetTProject()
dgCustomers.DataBind()
End If

HTH


Elton Wang
(e-mail address removed)



Gert Albertse said:
When I try to update my access database, nothing get updated. Here is my
code:

<%@ Page Language="VB" Debug="true" %>
<script runat="server">

' Insert page code here
'

Function UpdateCustomer(ByVal ProjectID As String, ByVal ProjectName
As String) As Integer
Dim connectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data
Source=D:\Websites\Demo\GI2005.mdb"
Dim dbConnection As System.Data.IDbConnection = New
System.Data.OleDb.OleDbConnection(connectionString)

Dim queryString As String = "UPDATE [TProject] SET
[ProjectName]=@ProjectName WHERE ([TProject].[ProjectID] = @Proj"& _
"ekID)"

Dim dbCommand As System.Data.IDbCommand = New
System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_ProjectID As System.Data.IDataParameter = New
System.Data.OleDb.OleDbParameter
dbParam_ProjectID.ParameterName = "@ProjectID"
dbParam_ProjectID.Value = ProjectID
dbParam_ProjectID.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_ProjectID)
Dim dbParam_ProjectName As System.Data.IDataParameter = New
System.Data.OleDb.OleDbParameter
dbParam_ProjectName.ParameterName = "@ProjectName"
dbParam_ProjectName.Value = ProjectName
dbParam_ProjectName.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_ProjectName)

Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try

Return rowsAffected
End Function



Function GetTProject() As System.Data.IDataReader
Dim connectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data
Source=D:\Websites\Demo\GI2005.mdb"
Dim dbConnection As System.Data.IDbConnection = New
System.Data.OleDb.OleDbConnection(connectionString)

Dim queryString As String = "SELECT [TProject].* FROM
[TProject]"
Dim dbCommand As System.Data.IDbCommand = New
System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

dbConnection.Open
Dim dataReader As System.Data.IDataReader =
dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

Return dataReader
End Function



Sub dgCustomers_Edit(sender as Object, e as
DataGridCommandEventArgs)
dgCustomers.EditItemIndex = e.Item.ItemIndex

dgCustomers.DataSource = GetTProject()
dgCustomers.DataBind()
End Sub


Sub dgCustomers_Update(sender as Object, e as
DataGridCommandEventArgs)

Dim ProjectID as String = e.Item.Cells(1).Text

Dim ProjectName as TextBox = e.Item.Cells(2).Controls(0)

UpdateCustomer(ProjectID, ProjectName.Text)

dgCustomers.EditItemIndex = -1

dgCustomers.DataSource = GetTProject()
dgCustomers.DataBind()
End Sub


Sub dgCustomers_Cancel(sender as Object, e as
DataGridCommandEventArgs)
dgCustomers.EditItemIndex = -1

dgCustomers.DataSource = GetTProject()
dgCustomers.DataBind()
End Sub


Sub Page_Load(sender as Object, e as EventArgs)
If Not Page.IsPostBack then
dgCustomers.DataSource = GetTProject()
dgCustomers.DataBind()
End If
End Sub

</script>
<html>
<head>
</head>
<body>
<form runat="server">
<p>
</p>
<p>
<asp:DataGrid id="dgCustomers" runat="server"
AutoGenerateColumns="False" OnCancelCommand="dgCustomers_Cancel"
OnUpdateCommand="dgCustomers_Update" OnEditCommand="dgCustomers_Edit">
<Columns>
<asp:EditCommandColumn ButtonType="LinkButton"
UpdateText="Update" CancelText="Cancel"
EditText="Edit"></asp:EditCommandColumn>
<asp:BoundColumn DataField="ProjectID" ReadOnly="True"
HeaderText="ProjectID"></asp:BoundColumn>
<asp:BoundColumn DataField="ProjectName"
HeaderText="ProjectName"></asp:BoundColumn>
</Columns>
</asp:DataGrid>
<!-- Insert content here -->
</p>
</form>
</body>
</html>
Click to expand...
 

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

Create Record in Database. 2
Simple Question: Bind DataReader to DatagGrid 3
Prevent duplicate entry to MS Access database 0
select textbox.text 3
Matrix generated Access mdb Update function not working 1
Web Matrix guide tour: INSERT in Access table fails 3
Database values.... 0
Dataset error. Why? 0

Members online

Total: 343 (members: 3, guests: 340)
Robots: 170

Forum statistics

Threads
473,968
Messages
2,570,150
Members
46,696
Latest member
BarbraOLog

Latest Threads

Top