Answered by:
Possibly an Access issue, maybe an IIS7 issue - Cannot immediately delete new data from database

Question
-
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.
Saturday, April 17, 2010 7:00 AM
Answers
-
User-515789441 posted
I solved the problem.
After adding a new keyword to the database I was not refreshing my dataset, effectively the dataset was missing the auto-generated primary key for the new item. Therefore when trying to delete the new item there was never going to be a match between the value of the listbox item and the value in the datatable.
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Sunday, April 18, 2010 5:59 PM
All replies
-
User-515789441 posted
Ok I have more information.
It is not a problem with IIS7.
The problem seems to be within my code. I've narrowed it down to the If statement at line 128.
If CStr(lstKeywordList.Items(i).Value) = myDataRow("keywordID").ToString() Then hasDeletes = True myDataRow.Delete() End If
The above statement does not find a match between lstKeyWordList.Items(i).Value and myDataRow("keywordID").ToString() the first time I attempt to delete a new item. On the second attempt the match is found and the delete is completed.
Can anyone help me out with this now?
Thanks,
Fido
Sunday, April 18, 2010 5:37 PM -
User-515789441 posted
I solved the problem.
After adding a new keyword to the database I was not refreshing my dataset, effectively the dataset was missing the auto-generated primary key for the new item. Therefore when trying to delete the new item there was never going to be a match between the value of the listbox item and the value in the datatable.
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Sunday, April 18, 2010 5:59 PM