How to update in MS Access using query in vb.net?

Question

First here's my code:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Try
            'Variables

            Dim PatientNo As Integer = CInt(ID_TextBox.Text)
            Dim updatedDate As DateTime = DateTimePicker1.Value

            dbcon.Open()

            'If Selected "Cleared"

            If ComboBox1.SelectedIndex = 0 Then
                query = "UPDATE Swab SET isActive = FALSE, NextSwab = NULL WHERE PatientNo = " & PatientNo & ""
                Dim cmd As OleDbCommand = New OleDbCommand(query, dbcon)
                Dim dbada As New OleDbDataAdapter(cmd)
           
                'If Selected "Positive"

            ElseIf ComboBox1.SelectedIndex = 1 Then
                updatedDate = updatedDate.AddDays(7)
                query = "UPDATE Swab SET DateOfSpecimenCollection = " & DateTimePicker1.Value & ", NextSwab = " & updatedDate & " WHERE PatientNo = " & PatientNo & ""
                Dim cmd As OleDbCommand = New OleDbCommand(query, dbcon)
                Dim dbada As New OleDbDataAdapter(cmd)
             
            End If

            dbcon.Close()
    Catch ex As Exception

            MessageBox.Show(ex.ToString)

    End Try
End Sub

Note: The AutoID is the PatientNo

I want to create a program that will update a value in a ms-access database using PatientNo ID to identify what record should be updated. If I choose index 0, it will update isActive to False and make the date NextSwab to NULL or blank.

However if i choose 1, it will update DateOfSpecimenCollection based on date and time picked in the vb program and NextSwab based on DateTime picked + 7 days.

The problem with my code, for some reason it returns nothing. It didn't get errors but it doesn't return the values that i want in my database either. I tried using the ExecuteNonQuery() before but it doesn't work either. Need help I'm a beginner and trying to learn vb.net with MS Access, thank you.

jmcilhinney · Accepted Answer · 2020-08-07 03:58:43Z

2

Your code doesn't execute any SQL against the database. The code as it is doesn't make any sense as you are completely misusing the data adapter.

The point of a data adapter is to aggregate CRUD operations. It has four properties for command objects: SelectCommand, InsertCommand, UpdateCommand and DeleteCommand. If you understand basic SQL then the purposes of those commands should be obvious. When you invoke the constructor that you are, the command you provide is assigned to the SelectCommand property. It should be obvious that populating the SelectCommand with an UPDATE statement is not appropriate.

The idea is that you provide a SELECT statement in the SelectCommand and call Fill to execute that SQL and populate a DataTable with the result set of the query. You then make edits to that data as required, adding, modifying and deleting rows where appropriate. You then call Update on the data adapter and it will execute the InsertCommand for each added row, the UpdateCommand for each modified row and the DeleteCommand for each deleted row to commit the changes made locally back to the database.

If you want to properly use a data adapter, DataTable and DataGridView then you need to make your changes to the DataTable first, either via the UI or in code, and then use the data adapter to properly save those changes back to the database.

If you want to modify data directly in the database then you should not be using a data adapter. You should be calling ExecuteNonQuery on the command itself. Note that that is not going to change anything in your grid though, so you'd need to repopulate that from the database if you want it to stay current.

Basically, you need to spend some time reading about ADO.NET to learn how to do it properly.

edited Aug 7, 2020 at 3:58

answered Aug 7, 2020 at 3:12

jmcilhinney

54.9k5 gold badges30 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aldrin Dino Over a year ago

I read how to use OleDbDataAdapter in microsoft website when i typed ADO.NET tutorial on google, i finally figure it out. i thought that OleDbDataAdapter is a object that only does execute a OleDbCommand that execute a query string from vb.net,I didn't know its use is completely different than what i have imagined. I completely misunderstood that. thanks for the help!

Collectives™ on Stack Overflow

How to update in MS Access using query in vb.net?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related