Database slower than datagrid it seems

[PatM]

In a form I am displaying a datagrid with data from a users table. I have a delete button which works and after the deletion is done I redo the query and reset the itemssource to show the new contents.
It seems that seldom is the row actually gone although very occasionally it is. If I leave the page and come back it shows what is actually in the database. I also have an adduser form where I insert a new record then go back to the same userlist. Rarely the new user appears unless I leave the page then reload yet again.

That suggests to me that the database takes longer to delete or insert than it does to select and show in the datagrid. Is that a reasonable assumption? If so is there some way I can find out when the database is done and redo the datagrid contents at that time?

Or maybe something completely different is happening?

 

[PatM]

Oh I should mention, I close the userlist when I show the new user page so when I go back there is no existing data in the grid since it is completely new again

 

[PatM]

I the meantime I added Threading.Thread.Sleep(1000) to the new user form and to the delete sub to give the database time and that works for now but not exactly what you call a robust solution

 

[jmcilhinney]

Why would you requery the database to get the data you already have? Why wouldn't you just remove the record locally and be done?

If you're determined to do it that way, how about you show us the code you're using? We can't provide definitive answers about why what you're doing works a particular way if we don't know what you're actually doing.

 

[PatM]

Deletiing only the local files leaves me with no way to know which disk based files to delete later unless I keep an array to record them and delete later. Then I have to track which ones were editing and inserted as well. That's why I liked the idea of a DataAdapter but also DataAdapter.update seems simple it adds all sorts of overhead on my part just to create and command them.

But here is my code first the delete function

    Public Function DeleteUser(id As Integer) As Integer
        Dim res As Integer
        Dim query As String = "DELETE FROM users WHERE id=" + id.ToString
        connDB = New OleDbConnection(connStr)
        connDB.Open()
        cmdDB = New OleDbCommand(query, connDB)

        res = cmdDB.ExecuteNonQuery()
        Return res
    End Function

Tnen the calling line

    Private Sub btnDelete_Click(sender As Object, e As RoutedEventArgs) Handles btnDelete.Click
        If (MsgBox("This will delete the user permanently", vbYesNo + vbDefaultButton2 + vbExclamation, "Are you sure?") = MsgBoxResult.No) Then
            Return
        End If
        Dim res As Integer = DeleteUser(SelectedPerson(0)("id"))
        Threading.Thread.Sleep(1000)
        LoadDG()

    End Sub

LoadDG() loads the datagrid but unless I delay for 1 second the database still returns the deleted row

    Private Sub LoadDG()
        loading = True
        Dim query As String
        query = "SELECT
u.id,
u.role as lev,
u.fullname as Fullname,
r.name as Role
FROM users as u
LEFT JOIN roles as r ON u.role=r.role"
        dtUsers = GetUsers(query)
        dg.ItemsSource = dtUsers.DefaultView
        loading = False

 

[jmcilhinney]

Deletiing only the local files leaves me with no way to know which disk based files to delete later unless I keep an array to record them and delete later.

That might matter if that's what I was suggesting but it wasn't so that's irrelevant. Consider this. You and 10 records in the database. You retrieve those 10 records. You want to delete 1 of those records. Based on what you're saying, you determine what record you want to delete, you delete it from the database and then you requery the database to retrieve the remaining 9 records again. What's the point of retrieving those 9 records when you already have them? You have 10 records and you know which one you want to delete. Just delete it locally and from the database. 9 records left and no redundant queries.

Also, you absolutely should be using a DataTable and a data adapter. What you're saying about overhead, as vague as it is, is simply wrong. The problem is that you just don't know how to use ADO.NET properly. That can be remedied.

 

[PatM]

Exactly what I meant, I just can't seem to understand how Adapters are used. I think I might be cluing in though. I've been playing around with just one table this morning using a datasource. Instead of limited selection of rows I just load the whole table then use datatable.select() to find the exact row I want, do what I want, then tableadapter.update(tableobject) and voila, it's updated in the database as well as the datatable.
DO I'm thinking I just .fill() the table I want then use the built in table methods to do what I want and when everything is finished and leave the page, then I call update(). Does that sound about right?
Right now I'm trying to figure out how to show words in a datagrid when the table loaded has a number instead of left joining the words from another table. That would take care of the problem I ran into where a multitable select can't use .update()