dcsimg
Results 1 to 14 of 14

Thread: updating data table fails

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Mar 2016
    Posts
    255

    updating data table fails

    Hi,
    I encountered a strange behaviour trying to update to MS access.
    I use VB 2010 express, Datatables are bound using bindingsource to a datagrid. Update is handled using bindingsource.endedit, tableadapter.update(..), and tableadapter.fill(..).
    I devlop the application on one computersystem running W7 and the application at the end runs on a system with W10.
    So far it worked on both systems but recently I get update problems on the W10 system (with exactly the same .exe that runs on W7). A red exclamation mark shows up and the data is not saved. It looks like there is a problem with rowstate.
    I have tried to read the rowstate of the row to be updated, but I am not sure how to access it, because there is no collection due to the bindingsource that I use.
    Can anybody help?
    thanks

  2. #2
    Fanatic Member
    Join Date
    Sep 2017
    Posts
    666

    Re: updating data table fails

    Have you included QuotePrefix and Suffix?

    IE
    Code:
          Using conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;
                                                            Data Source=C:\DATA\Access\AccessDB.accdb;")
                Using DA As New OleDbDataAdapter("SELECT * FROM yourtable", conn)
                    Dim cb As New OleDbCommandBuilder(DA)
                    cb.QuotePrefix = "["
                    cb.QuoteSuffix = "]"
                    DA.Update(dt)
                End Using
            End Using

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Mar 2016
    Posts
    255

    Re: updating data table fails

    I use the automatically generated DataAdapters and their methodes like artikleTableAdapter.update(DataSet.artikle) where artikle is the datatable.
    This seemed to work so far without any problem. In case square brackets are required they are automatically included by a function which I use.

  4. #4
    Fanatic Member
    Join Date
    Sep 2017
    Posts
    666

    Re: updating data table fails

    I cant help you with that.

    To answer your question though, here is how to get your rowstate where dt= datatable

    Code:
            For Each row As DataRow In dt.Rows
                MsgBox(row.RowState.ToString)
            Next
    If you ever want to ditch the wizard, here is a rather complete class you can use as an example for connecting to, binding, and updating an access database. It includes storing an image, which is not advisable but possible.

    Code:
    Imports System.Data.OleDb
    
    Public Class AccessConn
        Dim dt As New DataTable
        Dim bs As New BindingSource
        Private Sub AccessConn_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            Using conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;
                                                            Data Source=C:\DATA\Access\AccessDB.accdb;")
                Using DA As New OleDbDataAdapter("SELECT * FROM images", conn)
                    DA.Fill(dt)
                    bs.DataSource = dt
                    DataGridView1.DataSource = bs
    
                    PictureBox1.DataBindings.Add("Image", bs, "image", True)
                End Using
            End Using
    
        End Sub
    
        Private Sub Btn_BrowseForFile_Click(sender As Object, e As EventArgs) Handles Btn_BrowseForFile.Click
            Dim fb As New OpenFileDialog
            fb.InitialDirectory = "c:\"
            fb.Filter = "jpg files (*.jpg)|*.jpg|BMP files (*.bmp)|*.bmp"
            fb.RestoreDirectory = True
            If fb.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                PictureBox1.Image = Image.FromFile(fb.FileName)
            End If
        End Sub
    
        Private Sub Btn_Update_Click(sender As Object, e As EventArgs) Handles Btn_Update.Click
            Using conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;
                                                            Data Source=C:\DATA\Access\AccessDB.accdb;")
                Using DA As New OleDbDataAdapter("SELECT * FROM images", conn)
                    Dim cb As New OleDbCommandBuilder(DA)
                    cb.QuotePrefix = "["
                    cb.QuoteSuffix = "]"
                    DA.Update(dt)
                End Using
            End Using
        End Sub
    
    
    End Class

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Mar 2016
    Posts
    255

    Re: updating data table fails

    thanks for input. I' ll use 'for each' to walk through the table and try to understand the problem

  6. #6
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,823

    Re: updating data table fails

    So far it worked on both systems but recently I get update problems on the W10 system (with exactly the same .exe that runs on W7). A red exclamation mark shows up and the data is not saved. It looks like there is a problem with rowstate.
    The exe may be the same but is the database the same? You may have data in one that violate constraints but not in the other. " A red exclamation mark shows up" where does this show up? Is there an error message?

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Mar 2016
    Posts
    255

    Re: updating data table fails

    Code:
            For Each row As DataRow In dt.Rows
                MsgBox(row.RowState.ToString)
            Next
    to call the RowStates helped. It looks lilke sometimes the rowstate is still in "modified" state. Calling table.acceptchanges before updating seems to fix it.
    The strange thing is, that this happens randomly. I had the impression that a slow PC-System inforces this to happen. Is there some rational behind it?

  8. #8
    Fanatic Member
    Join Date
    Sep 2017
    Posts
    666

    Re: updating data table fails

    Im not real sure how Mr. Wizard handles the updates.
    bindingsource.endedit, tableadapter.update(..), and tableadapter.fill(..).
    This here is suggesting that the table is going to load dupe data.
    Maybe clear the table before the fill
    Code:
    dt.rows.clear

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Mar 2016
    Posts
    255

    Re: updating data table fails

    I further investigated and there are indications that vb, windows or MSaccess blocks the connection or the datatransfer. I found as follows:
    when there is a problem
    - I can only open msaccess readonly.
    - If msaccess is already open I can not edit fields
    I connect the DB using my.settings.item("connectionstring") = connection
    I wonder if VB keeps the connection open as long as the application is running.
    If so, would it be better to use command.open, command.executeNonQuery(), execute.close instead?
    Can I close the connection that is opened by the programm and reopen it without causing problems to the dataset?
    thanks for your comments.

  10. #10

    Thread Starter
    Addicted Member
    Join Date
    Mar 2016
    Posts
    255

    Re: updating data table fails

    I think I found the trouble maker!
    When I start the program I also start an email routine that sends the database in use to an external address for backup. This takes some time and blocks the updates of tables.
    The same happens if I make a local copy. Then I started the email via a process that calls a sepate .exe file. Even this did not allow the update as long as the email has not been completet. Is there a way to copy the db without blocking database access?

  11. #11
    Fanatic Member
    Join Date
    Sep 2017
    Posts
    666

    Re: updating data table fails

    Youre really not giving us much to go on. What is your email routine? Why even copy the file? Why are you using a wizard? Why use Access in the first place?

    my.settings.item("connectionstring") = connection
    This is not connecting to a db, this is setting a settings setting to "connection" what ever that may be

  12. #12

    Thread Starter
    Addicted Member
    Join Date
    Mar 2016
    Posts
    255

    Re: updating data table fails

    I am sorry if I could not explain well enough what I am trying to do.
    When I start the application that uses a ms access as db I want to make a backup that is sent to a different location. In the beginning I did not assume that calling a file as attechment would block the file for the time of creating the mime converting. This blocking caused the update problem of the database (which I found later after checking rowstatus and other tests).
    Next I tried to copy the database into another file (which is a lot faster) and send this second file as backup which saves blocking time, so the user would not experience long waiting times.
    Now, I have the whole update call put into a thread which gives satisfactory results. Here some snippets to explain it
    Code:
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            EmailThread = New Threading.Thread(AddressOf Emailserver)
            EmailThread.Start()
    
        Private Sub Emailserver()
            Emailcompleted = False
            Dim DB As String = ""
            If File.Exists(Arbeitsordner + "\" + Datenbankdatei) Then
                DB = Arbeitsordner + "\backup\" + "DB_" + Now.ToString("dd.MM.yy HH.mm") + ".accdb"
                File.Copy(Arbeitsordner + "\" + Datenbankdatei, DB)
            End If
            Dim Smtp_Server As New SmtpClient()
    '.......
            AddHandler Smtp_Server.SendCompleted, AddressOf SmtpClient_OnCompleted
            Smtp_Server.SendAsync(e_mail, Nothing)
    
        Public Sub SmtpClient_OnCompleted(ByVal sender As Object, ByVal e As AsyncCompletedEventArgs)
            Emailcompleted = True
        End Sub 'SmtpClient_OnCompleted
    Emailcompleted is used during shut down to wait until the email has been send

    I appreciate your inputs which helped me to identify the problem

  13. #13
    Fanatic Member
    Join Date
    Sep 2017
    Posts
    666

    Re: updating data table fails

    So you have resolved the problem? I am guessing that "e_mail" has a method of attaching the file to an email? Are you attaching the db that is subject to update, or the backup file you are creating. I would think it would be better to do something like this on closing instead of loading.

  14. #14

    Thread Starter
    Addicted Member
    Join Date
    Mar 2016
    Posts
    255

    Re: updating data table fails

    the application runs in a manufacturing line and includes a planning system (gantt chart). We keep a backup copy at a remote location. So we do one update in the morning when the operation starts and one in the evening when the program will shut down.
    In order to avoid waiting times for the user (planner) while sending the backup file I copy the file to the local backup directory and send this copy to the remote location. This way the planner can work during email processing. Copying is very fast and the copy is not accessed by the program.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width