none
Vb.net and access 2016 RRS feed

  • Question

  • I am using Access 2016 with accdb format. I am trying to read after write the DB and the fields are not updated. If i look at the table in access - the fields are written fine. Only after i exit the form and come back again the field is updated on the screen.

    Is there any vb data - adapter command that i can use in order to flash the buffers to the db ?

    Jack Sun

    Sunday, May 26, 2019 4:51 PM

All replies

  • When using a DataAdapter or TableAdapter and update or add records you can manually manipulate their container generally a DataSet with one or more DataTable containers.

    I need to know what you mean by write e.g. insert or update and what kind of controls are being used, a screenshot can also be helpful with an explanation/narrative.

    Bottom line there is zero reasons to do what you call "flash" or as other put it refresh. And there is never a reason to reload but I need to know specifics to help.  


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, May 26, 2019 11:12 PM
    Moderator
  • THank you for the replay, here is the code to update the table - I hope this will help to clarify

    If MyConn.State = ConnectionState.Closed Then MyConn.Open()
    Dim daCM As New OleDbDataAdapter(sql, strConnection)
                        
    
    sql = "CusBusID = " & CusBusID & ", " & ' 7016
                            "CusCksInt1 = " & MyVal(txtMonthlyInt1.Text) & ", " &
                            "CusCksInt2 = " & MyVal(txtMonthlyInt2.Text) & ", " &
                            "CusIntDays = " & MyVal(TxtIntDays.Text) & ", " &
                            "CusCksAmt = " & cbxCksAmt.SelectedIndex & ", " &
                            "CusObligo = " & MyVal(txtObligo.Text) & ", " &
                            "CusSolo = " & MyVal(txtSolo.Text) & ", " & ' 7075
                            "CusCreditLine = " & MyVal(txtCreditLine.Text) & ", " &
                            "CusStatus = " & CusStat(cbxStatus.SelectedIndex) & ", " & ' 7003
                            "CusBips = " & MyVal(txtBips.Text) & " " &
                            "WHERE CusID = " & CusID.Text
    
    daCM.UpdateCommand = New OleDbCommand(sql, MyConn)
    daCM.UpdateCommand.Transaction = Transaction
    daCM.UpdateCommand.ExecuteNonQuery()


    Jack Sun

    Monday, May 27, 2019 4:31 AM
  • THank you for the replay, here is the code to update the table - I hope this will help to clarify

    If MyConn.State = ConnectionState.Closed Then MyConn.Open()
    Dim daCM As New OleDbDataAdapter(sql, strConnection)
                        
    
    sql = "CusBusID = " & CusBusID & ", " & ' 7016
                            "CusCksInt1 = " & MyVal(txtMonthlyInt1.Text) & ", " &
                            "CusCksInt2 = " & MyVal(txtMonthlyInt2.Text) & ", " &
                            "CusIntDays = " & MyVal(TxtIntDays.Text) & ", " &
                            "CusCksAmt = " & cbxCksAmt.SelectedIndex & ", " &
                            "CusObligo = " & MyVal(txtObligo.Text) & ", " &
                            "CusSolo = " & MyVal(txtSolo.Text) & ", " & ' 7075
                            "CusCreditLine = " & MyVal(txtCreditLine.Text) & ", " &
                            "CusStatus = " & CusStat(cbxStatus.SelectedIndex) & ", " & ' 7003
                            "CusBips = " & MyVal(txtBips.Text) & " " &
                            "WHERE CusID = " & CusID.Text
    
    daCM.UpdateCommand = New OleDbCommand(sql, MyConn)
    daCM.UpdateCommand.Transaction = Transaction
    daCM.UpdateCommand.ExecuteNonQuery()


    Jack Sun

    Hello Jack,

    What you have shown is what I would expect to see, several inputs to capture information. Going with this I don't see a problem (and I know there is one) so that is why I asked to see a screenshot of the form.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, May 27, 2019 6:59 AM
    Moderator
  • thank you, Screen  Shut of the form?

    The form is very complex, over 300 controls of buttons, textboxs, labels, panels, picturesboxs, grids etc.

    I can send you JPG - is that what you mean?


    Jack Sun

    Monday, May 27, 2019 1:00 PM
  • Create a decent full size screenshot to Microsoft OneDrive, use the same login as in the forums.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, May 27, 2019 1:20 PM
    Moderator
  • I am sorry, i was out of town - you said you need to see screenshots of the form. Can you elaborate if you want to see the code or designer or run time ?

    Should i send you the whole form code ?


    Jack Sun


    • Edited by JackSun Wednesday, May 29, 2019 9:01 AM
    Wednesday, May 29, 2019 6:50 AM
  • Hi,

    Do you resolve the issue?Do you need a vb.net code to refresh the database data after the database update?

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 5, 2019 2:02 AM
    Moderator
  • I am sorry, i was out of town - you said you need to see screenshots of the form. Can you elaborate if you want to see the code or designer or run time ?

    Should i send you the whole form code ?


    Jack Sun


    I would like to see decent sized screenshots from runtime or design time. And another idea is to upload your project to Microsoft OneDrive with the database void of any sensitive information or even an empty database will do and I can look at it.

    If you do upload to OneDrive use a .zip, not a .rar file.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, June 5, 2019 10:55 AM
    Moderator
  • THank you for the replay, here is the code to update the table - I hope this will help to clarify

    If MyConn.State = ConnectionState.Closed Then MyConn.Open()
    Dim daCM As New OleDbDataAdapter(sql, strConnection)
                        
    
    sql = "CusBusID = " & CusBusID & ", " & ' 7016
                            "CusCksInt1 = " & MyVal(txtMonthlyInt1.Text) & ", " &
                            "CusCksInt2 = " & MyVal(txtMonthlyInt2.Text) & ", " &
                            "CusIntDays = " & MyVal(TxtIntDays.Text) & ", " &
                            "CusCksAmt = " & cbxCksAmt.SelectedIndex & ", " &
                            "CusObligo = " & MyVal(txtObligo.Text) & ", " &
                            "CusSolo = " & MyVal(txtSolo.Text) & ", " & ' 7075
                            "CusCreditLine = " & MyVal(txtCreditLine.Text) & ", " &
                            "CusStatus = " & CusStat(cbxStatus.SelectedIndex) & ", " & ' 7003
                            "CusBips = " & MyVal(txtBips.Text) & " " &
                            "WHERE CusID = " & CusID.Text
    
    daCM.UpdateCommand = New OleDbCommand(sql, MyConn)
    daCM.UpdateCommand.Transaction = Transaction
    daCM.UpdateCommand.ExecuteNonQuery()


    Jack Sun

    Jack, 

    What you're doing is not using the dataadatapter as a dataadapter, you use the reference to one of the comands  in it, to perform an crud ExecuteNonQuery. 

    Updating with a DataAdapter means using the Update method from it, which Inserts, Delete and Update rows from a database. 

    I saw that the way you do it, is currently by some promoted on Internet, but in that way you don't need a datadapter, you can direct instance a OleDBcommand. 

    Here how to do it with an oledb adapter.

    https://docs.microsoft.com/en-us/dotnet/api/system.data.oledb.oledbdataadapter?view=netframework-4.8


    Success
    Cor

    Wednesday, June 5, 2019 3:28 PM
  • No - i did not 

    Some here are suggesting diff methods

    but this is not helping.

    I saw that my code somehow how got "shortened" on the SQL update string

    Here is a corrected one 

    Transaction = MyConn.BeginTransaction(IsolationLevel.ReadCommitted)
    If MyConn.State = ConnectionState.Closed Then MyConn.Open()
    Dim daCM As New OleDbDataAdapter(sql, strConnection)

    sql = "UPDATE Customers SET " & 
       "CusBusID = " & CusBusID & ", " & ' 7016
       "CusCksInt1 = " & MyVal(txtMonthlyInt1.Text) & ", " &
       "CusCksInt2 = " & MyVal(txtMonthlyInt2.Text) & ", " &
       "CusIntDays = " & MyVal(TxtIntDays.Text) & ", " &
       "CusCksAmt = " & cbxCksAmt.SelectedIndex & ", " &
       "CusObligo = " & MyVal(txtObligo.Text) & ", " &
       "CusSolo = " & MyVal(txtSolo.Text) & ", " & ' 7075
       "CusCreditLine = " & MyVal(txtCreditLine.Text) & ", " &
       "CusCreditLineDate = '" & DateVAL(txtCreditLineDate.Text) & "', " & ' 7088
       "CusStatus = " & CusStat(cbxStatus.SelectedIndex) & ", " & ' 7003
       "CusBips = " & MyVal(txtBips.Text) & " " &
       "WHERE CusID = " & CusID.Text

    daCM.UpdateCommand = New OleDbCommand(sql, MyConn)
    daCM.UpdateCommand.Transaction = Transaction
    daCM.UpdateCommand.ExecuteNonQuery()


    Jack Sun

    Thursday, June 6, 2019 9:37 PM
  • hi there Cor

    You are referring me to a page that confirms my programming structure

    they show

    adapter.UpdateCommand = new OleDbCommand( "UPDATE Customers SET CustomerID = ?, CompanyName = ? " + "WHERE CustomerID = ?");

    and i have

    Transaction = MyConn.BeginTransaction(IsolationLevel.ReadCommitted)
    If MyConn.State = ConnectionState.Closed Then MyConn.Open()
    Dim daCM As New OleDbDataAdapter(sql, strConnection)

    sql = "UPDATE Customers SET " & 
       "CusBusID = " & CusBusID & ", " & ' 7016
       "CusCksInt1 = " & MyVal(txtMonthlyInt1.Text) & ", " &
       "CusCksInt2 = " & MyVal(txtMonthlyInt2.Text) & ", " &
       "CusIntDays = " & MyVal(TxtIntDays.Text) & ", " &
       "CusCksAmt = " & cbxCksAmt.SelectedIndex & ", " &
       "CusObligo = " & MyVal(txtObligo.Text) & ", " &
       "CusSolo = " & MyVal(txtSolo.Text) & ", " & ' 7075
       "CusCreditLine = " & MyVal(txtCreditLine.Text) & ", " &
       "CusCreditLineDate = '" & DateVAL(txtCreditLineDate.Text) & "', " & ' 7088
       "CusStatus = " & CusStat(cbxStatus.SelectedIndex) & ", " & ' 7003
       "CusBips = " & MyVal(txtBips.Text) & " " &
       "WHERE CusID = " & CusID.Text

    daCM.UpdateCommand = New OleDbCommand(sql, MyConn)
    daCM.UpdateCommand.Transaction = Transaction
    daCM.UpdateCommand.ExecuteNonQuery()

    I tryed the why they are showing and it still did not update "on that spot" - i had to go outside the form and come back again to see the changed data



    Jack Sun

    Thursday, June 6, 2019 9:41 PM
  • Hi,

    Is there any error?I want to see the error message.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 11, 2019 9:13 AM
    Moderator