CODEWITHSUNDEEP
vb.netms-access
Aldrin Dino
Aldrin Dino

Reputation: 57

How to fix my update query for my program in vb.net with MS access

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)
                cmd.ExecuteNonQuery()


                '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)
                cmd.ExecuteNonQuery()

            End If

            dbcon.Close()
        Catch ex As Exception

            MessageBox.Show(ex.ToString)

        End Try

    End Sub

"Swab" Table

|PatientNo|FirstName|isActive|DateOfSpecimenCollection| NextSwab |
     1       Aldrin    TRUE          15/07/20           22/07/20
     2       Joshua    FALSE         15/07/20             NULL
     3       James     TRUE          15/07/20           22/07/20

My Program

enter image description here Note that the AutoID is a primary key for PatientNo

What I want to do is that when a Active Patient is Cleared it will update his status from the Swab database to Swab.isActive = 'FALSE' and his Swab.NextSwab = 'NULL', however if a Active Patient is Positive then it will update his status from the Swab database to Swab.DateOfSpecimenCollection = DateAndTimePicker1.Value, Swab.NextSwab = DateAndTimePicker1.Value + 7days.

For example Aldrin exists in the record created June 30, 2020. If he entered to the system with a Patient Status Positive then he will retain his isActive = TRUE in the system and his DateOfSpecimenCollection = DateAndTimePicker1.Value and NextSwab = DateAndTimePicker1.Value + 7Days will be updated. However if he was cleared then his isActive will be false and his NextSwab would display nothing or NULL .

(This is a test program, that's why the table above is short).

Upvotes: 0

Views: 55

Answers (1)

June7
June7

Reputation: 21370

Presume isActive is a Yes/No field. Don't put True or False between apostrophes. True and False are constants that represent -1 and 0. Could use 0 or -1 instead in SQL. Also don't put Null between apostrophes if you really want field to be null, otherwise will save word "Null". Use apostrophe delimiters only if field is a text type.

query = "UPDATE Swab SET isActive = FALSE, NextSwab = NULL WHERE PatientNo = '" & PatientNo & "'"

Upvotes: 2

Related Questions