User-515789441 posted
I have an asp.net application that was working fine on my old dev machine that run XP pro and Access 2007. Although I did have problems getting the ACE OLEDB provider to work and so stuck with the old Jet provider.
I was given a new Windows 7 pro computer that also has Access 2007, I had no problems moving my app to the new dev machine. No problems that is until I had to add a new column to one of my database tables since then my app has been very weird when
deleting rows (I am using the OleDbDataAdapter to access the database).
Existing data within the database deletes just fine, however if I add new data and then immediately try to delete it nothing happens, no error, no delete, nothing. This is the weird part; if I navigate away from the page I use to maintain the database
(i.e. click my link back to the home page) and then revisit the database maintenance page the new data can be deleted just like old data. I add Response.Redirect() into my code to see if that would work but no, only navigating away from the page and
then returning allows me to delete the data I had just added.
This problem did not occur until i added the new column to the database table. There is no problem with my code, the same code worked fine on the old machine and on the new machine right up until i added the new column to the database table.
My thoughts are that it must be some combination of using Jet and IIS7. Oh and both the app and the database are on the same machine.
Can anyone offer any insight into what is going on here?
Heres my code:
Public Class AccessDatabase 'Access and retrive data from database
Public myDataSet As New DataSet()
Sub LoaddbData(ByVal sqlQuery As String, ByVal myTable As String) 'load from database
'connect to database
Dim conn As String = ConfigurationSettings.AppSettings("accessConnectionString") 'database connection string
Dim myConn As New OleDbConnection(conn)
Dim myAdapter As New OleDbDataAdapter(sqlQuery, myConn)
myAdapter.Fill(myDataSet, myTable)
myAdapter.Dispose()
myConn.Close()
End Sub
Sub SavedbData(ByVal sqlQuery As String, ByVal myTable As String) 'update changes to database
'Make temp dataset
Dim tempDataSet As DataSet = myDataSet.GetChanges()
Dim conn As String = ConfigurationSettings.AppSettings("accessConnectionString") 'database connection string
Dim myConn As New OleDbConnection(conn)
Dim myAdapter As New OleDbDataAdapter(sqlQuery, myConn)
Dim myCommandBuilder As New OleDbCommandBuilder(myAdapter)
myAdapter.Update(tempDataSet, myTable)
tempDataSet.AcceptChanges()
myAdapter.Dispose()
myConn.Close()
End Sub
End Class
'-------------Class containing scripts for the keywords page-----------'
Public Class keywords 'keywords page scripts
Inherits System.Web.UI.Page
Dim dbKeywords As New AccessDatabase() 'create db connection instance
Dim myDataView As DataView
Protected WithEvents btnAddKeyword As System.Web.UI.WebControls.Button
Protected WithEvents btnKeywordList As System.Web.UI.WebControls.Button
Protected WithEvents lstKeywordList As System.Web.UI.WebControls.ListBox
Protected WithEvents txtAddKeyword As System.Web.UI.WebControls.TextBox
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
InitializeComponent()
'get keywords from database
'Clear any existing collection
Dim Tables As DataTableCollection = dbKeywords.myDataSet.Tables
Tables.Clear()
Dim sqlQuery As String = "SELECT * FROM keyWords"
dbKeywords.myDataSet.Tables.Add("existingKeywords") 'create the dataset table
dbKeywords.LoaddbData(sqlQuery, "existingKeywords") 'get the data from the database
'bind to listbox
myDataView = New DataView(dbKeywords.myDataSet.Tables("existingKeywords"))
myDataView.Sort = "keyWordName"
lstKeywordList.DataSource = myDataView
lstKeywordList.DataTextField = "keyWordName"
lstKeyWordList.DataValueField = "keyWordID"
lstKeywordList.DataBind()
End Sub
Protected Sub btnAddKeyword_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddKeyword.Click
'adds a new keyword to the database
Dim newWord As String = txtAddKeyword.Text
If Not (newWord = "") Then
'tidy up string
newWord = newWord.ToLower() 'make all lower case
newWord = newWord.Trim 'trim
'add to db
Try
'Clear any existing collection
Dim Tables As DataTableCollection = dbKeywords.myDataSet.Tables
Tables.Clear()
'get new db data
Dim sqlQuery As String = "SELECT * FROM keyWords"
dbKeywords.myDataSet.Tables.Add("existingKeywords") 'create the dataset table
dbKeywords.LoaddbData(sqlQuery, "existingKeywords") 'get the data from the database
Dim myNewDataRow As DataRow = dbKeywords.myDataSet.Tables("existingKeywords").NewRow()
myNewDataRow("keyWordName") = newWord
dbKeywords.myDataSet.Tables("existingKeywords").Rows.Add(myNewDataRow)
dbKeywords.SavedbData(sqlQuery, "existingKeywords")
Catch Ex As Exception
System.Web.HttpContext.Current.Response.Redirect("keywords.aspx")
End Try
'refresh listbox
lstKeywordList.DataSource = ""
lstKeywordList.Items.Clear()
myDataView = New DataView(dbKeywords.myDataSet.Tables("existingKeywords"))
myDataView.Sort = "keyWordName"
lstKeywordList.DataSource = myDataView
lstKeywordList.DataTextField = "keyWordName"
lstKeyWordList.DataValueField = "keyWordID"
lstKeywordList.DataBind()
newWord = ""
txtAddKeyword.Text = ""
End If
End Sub
Protected Sub btnKeywordList_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnKeywordList.Click
'delete selected keywords from database
'get db table
'Clear any existing collection
Dim Tables As DataTableCollection = dbKeywords.myDataSet.Tables
Tables.Clear()
'get new db data
Dim sqlQuery As String = "SELECT * FROM keyWords"
dbKeywords.myDataSet.Tables.Add("existingKeywords") 'create the dataset table
dbKeywords.LoaddbData(sqlQuery, "existingKeywords") 'get the data from the database
'loop through list to find selected items
Dim i As Integer 'loop counter
Dim myDataRow As DataRow
Dim hasDeletes As Boolean = False
For Each myDataRow In dbKeywords.myDataSet.Tables("existingKeywords").Rows
For i = 0 To lstKeywordList.Items.Count - 1
If lstKeywordList.Items(i).Selected = True Then
If lstKeywordList.Items(i).Value = myDataRow("keywordID").ToString() Then
hasDeletes = True
myDataRow.Delete()
End If
End If
Next
Next
If hasDeletes Then
dbKeywords.SavedbData(sqlQuery, "existingKeywords")
End If
'refresh listbox
lstKeywordList.DataSource = ""
lstKeywordList.Items.Clear()
myDataView = New DataView(dbKeywords.myDataSet.Tables("existingKeywords"))
myDataView.Sort = "keyWordName"
lstKeywordList.DataSource = myDataView
lstKeywordList.DataTextField = "keyWordName"
lstKeyWordList.DataValueField = "keyWordID"
lstKeywordList.DataBind()
End Sub
End Class
Many thanks,
Fido.
