[RESOLVED] VB.NET Too Fast For MS Access ?

[oigres]

All,
I have a sync problem between VB.NET and a MS Access database.

I have an unbounded datagrid which shows the content of a table.

When I want to delete a record from the Database, I select the row in the grid, which has also the ID of the record, and I perform a command like
"Delete from mytable where ID = selected_id"

After this sql command, I perform a a "SELECT * from mytable" statement, to show the changes in the datagrid.

The problem is that between the Delete and the Select statements, the code runs so fast that the Select still gets the previous content of the table, so the record which has been just 'deleted', still appears in the datagrid.

If I re-load after a second the datagrid, then it reflects the changes.

Question: is there any way to know when the table has been effectively updated ?

I have tryed also using transaction (begin and commit), but no luck. The problem arises when in the table there are at least 15-20 records or more. When the table contains less records, then it is 'fast enough', and this issue does not arise.

Any help would be greately appreciated.

Sergio.

[dee-u]

I don't know yet how but I in VB6.0 I would set the CursorLocation of the recordset to adUseClient...

[gigemboy]

Hmm.. for the time being, you can try just sleeping the thread..

System.Threading.Thread.Sleep(1000) ' this would sleep for 1 second

[oigres]

Thanks, but OleDBConnection does not seem to have any CursorLocation property to set to "adUseClient" .

I've tryed using it in a ADO connection, but I get the same problem, that is, the database is not yet updated when the next Select is performed. This is the scenario:

- Delete record using ADO and Cursorlocation = adUseClient (the record is going to be deleted from Access DB)
- Select * from mytable (re-load the table content in the datagrid)

result: the datagrid content does not show the changes. If I re-load it again after some second, then I see the change in the datagrid.

The sleep threading solution is indeed a workaround, but it gives the impression the application hangs, so I prefere not to use it.

How would you get around this problem ?

Sergio.

[Aspnot]

I have run into this with Access in the past. The easiest way to get around it is to wrap your updates, deletes and inserts into a Transaction and then post them. That will force Access to process everything immediately.

[oigres]

@Aspnot,
yes I did tryed using a transaction, (an OLEDB transaction) but it didn't work.
Basically I've done something like:

OLEDB begin transaction
delete record
OLEDB transaction commit
re-load the datagrid content (with "Select * from mytable" statement)

Sadly, same "sync" problem. I mean, also in this way, the datagrid does not show the updated table content. If i re-load it again, with another Select, then the datagrid shows the changes.

A workaround would be to put a messagebox every time a record is deleted or added. But this is not accepted as a solution, as it is not really user-friendly for the final users.

The way I'm looking for, should perform the datagrid update - using the Select statement - only when the table has been finally updated.

Sergio.

[techgnome]

dee-u - by design ADO.NET is disconnected client-side, there's no longer a cursor location option.

oigres - Can you post your code on how you are populating your datatables and performing the deletes?

-tg

[oigres]

oigres - Can you post your code on how you are populating your datatables and performing the deletes?
-tg

This is how I delete the entry from Access DB (the commented statements are there why I have also tried with transaction begin and commit):

Public Function sql_cmd(ByVal sql As String) As Boolean
 
        'MsgBox(Application.ExecutablePath)
 
 
 Dim oleconnstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbpath
        Dim oleconnection As New OleDbConnection(oleconnstring)
 
        Dim olecommand As New OleDbCommand
        'Dim oleTrans As OleDb.OleDbTransaction
 
        Try
 
            oleconnection.Open()
 
            'oleTrans = oleconnection.BeginTransaction()
            olecommand.Connection = oleconnection
            'olecommand.Transaction = oleTrans
            olecommand.CommandText = sql
            olecommand.ExecuteNonQuery()
 
 
 
            'oleTrans.Commit()
 
 
            REM
            'oleTrans = oleconnection.BeginTransaction
 
            'olecommand.Connection = oleconnection
            'olecommand.Transaction = oleTrans
 
            'olecommand.CommandText = strSQL
            'olecommand.ExecuteNonQuery()
 
            'olecommand.CommandText = "SELECT MAX(idMSG) FROM MessageLog"
            'lngResult = olecommand.ExecuteScalar()
 
            'oleTrans.Commit()
 
            REM
 
 
        Catch ex As Exception
 
            MsgBox(ex.Source & vbCr & ex.Message & vbCr & "pfad=" & dbpath & vbCr & "sql=" & sql, MsgBoxStyle.Exclamation)
            Return False
 
        Finally
 
 
            'oleconnection.Close()
            'olecommand = Nothing
            'oleconnection = Nothing
            'Clear objects 
            If Not IsNothing(oleconnection) Then
                If oleconnection.State = ConnectionState.Open Then oleconnection.Close()
                oleconnection.Dispose()
            End If
 
            olecommand.Dispose()
 
 
        End Try
 
        WSS.table_changed = True
        Return True
 
 
    End Function

---------------------------------------

I've also tried using ADO:

Public Function ADO_sql_cmd(ByVal sql As String) As Boolean
 
        Dim ADOconnstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbpath
        Dim ADOconnection As New ADODB.Connection
        Dim ADOcmd = New ADODB.Command
 
        Try
 
            ADOconnection.CursorLocation = ADODB.CursorLocationEnum.adUseClient
            ADOconnection.Open(ADOconnstring)
 
            ADOcmd.activeconnection = ADOconnection
            ADOcmd.commandtext = sql
            ADOcmd.execute()
            ADOcmd.activeconnection.close()
 
 
 
        Catch ex As Exception
 
            MsgBox(ex.Source & vbCr & ex.Message & vbCr & "pfad=" & dbpath & vbCr & "sql=" & sql, MsgBoxStyle.Exclamation)
            Return False
 
        Finally
 
 
            'oleconnection.Close()
            'olecommand = Nothing
            'oleconnection = Nothing
            'Clear objects 
            If Not IsNothing(ADOconnection) Then
                If ADOconnection.State = ConnectionState.Open Then ADOconnection.Close()
                'ADOconnection.Dispose()
            End If
 
            ADOcmd = Nothing
 
 
        End Try
 
        WSS.table_changed = True
        Return True
 
 
    End Function

---------------------------------------
This is how I populate the grid (it's a Janus grid):

Public Sub DBJanusGrid_Fill_Oledb(ByVal dbconnection As OleDbConnection, _
 ByVal sql As String, ByRef grid As Janus.Windows.GridEX.GridEX)
 
 
        Try
 
            Dim ds As New DataSet
            Dim da As New OleDb.OleDbDataAdapter(sql.ToString, dbconnection)
            Dim table As String = "table"
            Dim cols As Integer
            ds.Clear()
            da.Fill(ds, table)
 
            'fill the grid with data
            grid.DataSource = ds.Tables(table)
            grid.RetrieveStructure()
 
            grid.Refresh()
 
            ds.Dispose()
            da.Dispose()
 
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Fehler: DBJanusGrid füllen Oledb")
        End Try
 
    End Sub

---------------------------------------

So, what I do in the code is:

sql = "delete from mytable where id = 2"
'ADO_sql_cmd(sql)
sql_cmd(sql)
'then I re-load the grid
'MsgBox("Success !")
sql = "select * from mytable"
DBJanusGrid_Fill_Oledb(dbconnection, sql, datagrid)

Sadly, when the select is executed, the grid still shows the old table content.
Note that if I uncomment the msgbox, then the grid shows the right table content, because the time spent to click on the ok button seems that gives enough time to Access to update itself.

Sergio.

[oigres]

Solved !

The key is to reconnect to the database:

Reconnect again to your database , I've done the same with no problems.

Here is the thread:
http://www.vbforums.com/showthread.p...=oledb+refresh

Thank you Pirate

Sergio.