Results 1 to 5 of 5

Thread: Record not being updated - No errors or exceptions

  1. #1

    Thread Starter
    New Member
    Join Date
    Sep 2022
    Posts
    12

    Record not being updated - No errors or exceptions

    The program runs all the way through the Button press event and I get no errors or exceptions, but yet the record does not get updated. Any Ideas?

    Code:
       Private Sub Button2_Click_1(sender As Object, e As EventArgs) Handles btn_status_SAVE.Click
            'Call Grid_Clear()
            dbProvider = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"  'SET UP THE PROVIDER
            TheDatabase = "/Planners Tasks.accdb"  'SET THE DATABASE AND WHERE THE DATABASE IS
            MyFolder = "C:\OP Dashboard"
            FullDatabasePath = MyFolder & TheDatabase
            dbSource = "Data Source = " & FullDatabasePath 'SET THE DATA SOURCE
            con.ConnectionString = dbProvider & dbSource 'SET THE CONNECTION STRING
            con.Open() 'OPEN THE DATABASE
            sql = "UPDATE Tasks " _
                    & " SET FlowStudy =" & cmb_Flow_Study.Text _
                    & " WHERE Tasks.ID = " & lbl_DB_ID.Text
            da = New OleDb.OleDbDataAdapter(sql, con)
            da.TableMappings.Add("Tasks", "Tasks")
            da.Update(ds, "Tasks")
            con.Close()  'Close THE DATABASE  '
            DataGridView1.DataSource = ds.Tables("Tasks")
            MsgBox("Success")
        End Sub

  2. #2

    Thread Starter
    New Member
    Join Date
    Sep 2022
    Posts
    12

    Re: Record not being updated - No errors or exceptions

    This is the statement after selecting the combobox: sql = "UPDATE Tasks SET FlowStudy = In-Progress WHERE Tasks.ID = 1"
    The is only on record in the table and the ID is 1.

  3. #3
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,458

    Re: Record not being updated - No errors or exceptions

    Quote Originally Posted by gofastonit View Post
    This is the statement after selecting the combobox: sql = "UPDATE Tasks SET FlowStudy = In-Progress WHERE Tasks.ID = 1"
    The is only on record in the table and the ID is 1.
    It is possibly down to some of the values being strings but not enclosed in quotes.

    Then again you really don't want to be building SQL using string concatenation anyway - you should look at using parameters. Search these forums for plenty of examples of how to use parameters.

  4. #4

    Thread Starter
    New Member
    Join Date
    Sep 2022
    Posts
    12

    Re: Record not being updated - No errors or exceptions

    Although I will look into parameters, I did try:
    sql = "UPDATE Tasks SET FlowStudy = 'In-Progress' WHERE Tasks.ID = 1"
    sql = "UPDATE Tasks SET FlowStudy = 'In-Progress' WHERE Tasks.ID = '1'"
    sql = "UPDATE Tasks SET FlowStudy = In-Progress WHERE Tasks.ID = '1'"

    None of those worked.

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Record not being updated - No errors or exceptions

    Quote Originally Posted by gofastonit View Post
    Although I will look into parameters, I did try:
    sql = "UPDATE Tasks SET FlowStudy = 'In-Progress' WHERE Tasks.ID = 1"
    sql = "UPDATE Tasks SET FlowStudy = 'In-Progress' WHERE Tasks.ID = '1'"
    sql = "UPDATE Tasks SET FlowStudy = In-Progress WHERE Tasks.ID = '1'"

    None of those worked.
    I would assume that FlowStudy is a text type and ID is a numeric type. In that case, the first one is correct. As suggested though, you should absolutely NOT be doing things that way and should be using parameters instead.

    As for this supposed issue. Debug your code. Set a breakpoint and step through it, line by line. Assign the value returned by ExecuteNonQuery to a variable and look with your eyes to see what it is. You should have already done this before posting. When you call ExecuteNonQuery there are only three possible outcomes:

    1. The operation fails and throws an exception.
    2. The operation succeeds and returns zero.
    3. The operation succeeds and returns a non-zero value (probably 1).

    Once you AND WE know which of those is occurring, then we can determine whether there actually is an issue and what that might be. If there really is no exception thrown then it's either case 2 or case 3. If it's 2 then that just means that there are no records in the database that match your filter. If it's 3 then there is no problem other than that you're looking for the data in the wrong place. That's often the case with Access databases although it doesn't look like it would be in this case.

Posting Permissions

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



Click Here to Expand Forum to Full Width