Source database not updated using data adapter, data set

[Jeremy Tourville]

My code appears to be working as expected except for getting the column/field named "Foundstone" to update in the source database. I am trying to figure out what I am doing wrong. I am not seeing any errors or exceptions being thrown. I have used this tutorial as a basis for my learning database coding - Visual Basic .NET programming for Beginners - Navigate a Database I have done a lot of reading about the different methods available to work with databases. I have really tried to educate myself before posting here. (I am brand new to both programming in general and database programming. I only had one class as part of my computer major (networking not programming) If I ask a question that seems like basic knowledge it may be because it's all new to me. I am very glad to read and try to educate myself as much as possible so please share links.)

My code is below

Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Windows.Forms.HtmlDocument

Public Class Form1
    Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\VulnScanData.accdb;Persist Security Info=True;Jet OLEDB:Database Password=********")
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String
    Dim maxrows As Integer
    Dim result As Integer
   

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        WebBrowser1.Navigate("https://fs-enterprise.my.private.url/")
        con.Open() 'opens connection to database
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles BtnLogin.Click
        WebBrowser1.Document.GetElementById("name").SetAttribute("value", TextBox1.Text)
        WebBrowser1.Document.GetElementById("password").SetAttribute("value", TextBox2.Text)
        WebBrowser1.Document.GetElementById("Logon").InvokeMember("click")
    End Sub

    Private Sub BtnQuery_Click(sender As Object, e As EventArgs) Handles BtnQuery.Click

        sql = "SELECT [Ticket ID] AS Ticket_ID , [Foundstone] AS Foundstone, [ID] AS ID FROM [Table MAIN] WHERE ([Ticket Days OverDue] >= 0)" 'define the query
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "MAIN")
        If ds.Tables("MAIN").Rows.Count > 0 Then
            TxtRows.Text = ds.Tables("MAIN").Rows.Count
            maxrows = Val(TxtRows.Text.ToString)
        End If
        For i = 0 To maxrows - 1
            If i >= 0 Then
                result = ds.Tables("MAIN").Rows(i).Item("Ticket_ID")
                WebBrowser1.Navigate("https://fs-enterprise.my.private.url/remediation/ticket.exp?ticket=" & result)
                Do While WebBrowser1.ReadyState <> WebBrowserReadyState.Complete
                    Application.DoEvents()
                Loop
                WebBrowser1.AllowNavigation = True
                '<div id="MessageGood_0" class="mvm-status-message msm-msg msm-msg-img" style="background-image:url(/images/fam/cross.png);">The specified ticket does not exist, or is not currently available.</div>  
                For Each el As HtmlElement In WebBrowser1.Document.GetElementsByTagName("div")
                    If (el.GetAttribute("id").Equals("MessageGood_0")) Then
                        TxtTicket.Text = ds.Tables("MAIN").Rows(i).Item("Ticket_ID")
                        Try
                            Dim cb As New OleDb.OleDbCommandBuilder(da)
                            cb.QuotePrefix = "[" 'allows update if table name or field is a reserved word in MS Access
                            cb.QuoteSuffix = "]" 'allows update if table name or field is a reserved word in MS Access
                            ds.Tables("MAIN").Rows(i).Item("Foundstone") = "NotFound"
                            da.Update(ds, "MAIN")
                            da.UpdateCommand = cb.GetUpdateCommand()
                            'MsgBox("Ticket Not Found")
                        Catch ex As Exception
                            MsgBox(ex.Message.ToString, , "Error")
                        End Try
                    Else
                        '<input class="boldbutton" type="button" value="Verify" onclick="this.form.knob.value='ReqVerify';this.form.verify.value=1;this.form.submit()"></td>
                        TxtTicket.Text = ds.Tables("MAIN").Rows(i).Item("Ticket_ID")
                        For Each element As HtmlElement In WebBrowser1.Document.GetElementsByTagName("INPUT")
                            If (element.GetAttribute("value").Equals("Verify")) Then
                                element.InvokeMember("click")
                            End If
                        Next
                    End If
                Next
            End If
        Next
        MessageBox.Show("All Tickets Have Been Processed")
        Me.Close()
        con.Close()
    End Sub
End Class

I tried to do some debugging. I am seeing something like a pin in the "ds.Tables("MAIN").Rows(i).Item("Foundstone") = "NotFound" " section if I hover my mouse on the word tables. I can see what I think might be an error??? In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user. Thoughts? and maybe this has nothing to do with the real issue...

 

[jmcilhinney]

There are a number of issues with your code but let's deal with the actual saving of data here, given that that's what you asked about. It's this line that saves the changes from the DataTable back to the database:

da.Update(ds, "MAIN")

The first thing to do is to determine whether that line actually is saving any changes or not. When you call Update, there are only three possible outcomes:

1. The call fails and an exception is thrown
2. The call succeeds and returns zero, which indicates that there were no changes to save
3. The call succeeds and returns a non-zero value, which indicates that that many rows were affected by the save

You need to trap the returned value and test what it is and proceed accordingly.

Most likely it will be non-zero and everything is actually working exactly as it should. The problem is most likely to be that you're just looking in the wrong place or at the wrong time for the data. It happens all the time.

When you add a data file to your project, it is generally copied from its original location into your project folder. That copy now becomes the source database. Just like the rest of your source files, it doesn't exist at run time. When you build your project, your source files are processed in various ways, based on their type, and the result is placed in the output folder. In the case of your VB code files, the code is compiled into an EXE and that's what gets placed in the output folder. In the case of your data file, it is simply copied from the source folder to the output folder. When you run your compiled application, it's the copy in the output folder that you work against. It makes complete sense if you think about it. Do you really want your source database that you will have to deploy to all your users polluted with test data?

Furthermore, by default, a new copy of the database is created in the output folder every time you rebuild your project. That means that if you run your project, change the data, close the application and then run the application then you'll see the data changes when you run the second time. On the other hand, if you run your project, change the data, close the application, CHANGE THE CODE and then run the project again, you will NOT see the data changes the second time. That's because the rebuild after the code change prompts the data file to be recopied OVER THE TOP of the old copy that you made changes to. In almost all cases where people complain of not seeing changes to their data saved, that's what is actually happening.

The solution is dead simple. Select your data file in the Solution Explorer, open the Properties window and change the Copy To Output Directory property from Always Copy to Copy If Newer. What that means is that, even if you rebuild your project, the source data file will not be copied over the existing working file in the output folder unless the source data file itself has changed. That allows you to retain your test data as long as you want, even as you rebuild your project over and over. Any time you change the schema or default data in the source database, the working database will be overwritten the next time you build. If you ever want to refresh the database without changing the source, you simply change the Copy To Output Directory property back temporarily or, even easier, simply delete the data file from output folder manually.

 

[Jeremy Tourville]

Many thanks for your reply! :joyous: I am embarrassed to say I was looking at the wrong spot. I was looking in my project folder but in the wrong location in the project folder. Sometimes you just need someone to help you see the "forest through the trees" so to speak. Problem resolved. Now to fix the issues with my code as you mentioned... Yes, I am seeing the problems as I am doing further validation and testing. Well, I won't go into that here. I consider this thread resolved.