locked
Possibly an Access issue, maybe an IIS7 issue - Cannot immediately delete new data from database RRS feed

  • 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