Loop to affect all records in a table

Question

I wanted you to help me on the following, I wanted to execute the code that I’m going to show you below on a button so that I would do it automatically when I click on it.

The program works like this: first I have to insert a code in a textbox to call a record (a doctor in this case) and if that doctor is more than 80 years old I press a button to make a series of changes, what I want is another button, that the program finds all doctors who are over 80 and make such changes.

Public Sub TextBox1_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox1.KeyPress


    If e.KeyChar.ToString = ChrW(Keys.Enter) Then ' Enter para pesquisar
        TextBox1.Focus()
        e.Handled = True

        CarregarGrid() ' carrega a grid só depois de inserir o código do medico
        Label8.Text = DGV1(0, 0).Value

        If Not IsDBNull(DGV1(9, 0).Value) Then

            Dim cd, bd As Date        '  Calcula a idade fazendo a diferença entre o ano actual e o ano da DBO (date of birth)
            bd = (DGV1(9, 0).Value)
            cd = Now

            Dim idade As Integer = Year(cd) - Year(bd)

            If idade > 80 Then
                Button1.Enabled = True
            Else
                Button1.Enabled = False
            End If
        End If
    End If
    Button1.Enabled = True

End Sub

Public Sub btnAlterarDadosMedico(sender As System.Object, e As System.EventArgs) Handles Button1.Click

    TextBox1.Text = DGV1(5, 0).Value ' para ver o codigo do médico

    AlteracoesGrid(DGV1, DGV3, DGV5)

    DesalocarMedicos()

    CarregarGrid() 

The code of methods:

Public Sub CarregarGrid() ' carrega so depois de inserir o codigo do medico

    Form1.DoctorBasicTableAdapter.Fill(Form1.GSM81DataSet.DoctorBasic, Form1.TextBox1.Text.ToString())
    Form1.DoctorEnterpriseIdTableAdapter.Fill(Form1.GSM81DataSet.DoctorEnterpriseId, Form1.DGV1(0, 0).Value)
    Form1.DoctorRepresentativeTableAdapter.Fill(Form1.GSM81DataSet.DoctorRepresentative, Form1.DGV1(0, 0).Value)
    Form1.DoctorsAddressTableAdapter.Fill(Form1.GSM81DataSet.DoctorsAddress, Form1.DGV1(0, 0).Value)
    Form1.DoctorsVisitsTableAdapter.Fill(Form1.GSM81DataSet.DoctorsVisits, Form1.DGV1(0, 0).Value)
    Form1.DoctorEnterpriseDetailsTableAdapter.Fill(Form1.GSM81DataSet.DoctorEnterpriseDetails, Form1.DGV1(0, 0).Value)

End Sub

Public Sub DesalocarMedicos()
    Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection("Data Source=SERVER\SQLEXPRESS;Initial Catalog=GSM81;Persist Security Info=True;User ID=sa;Password=senha")
    Dim cmd, cmd2, cmd3 As New System.Data.SqlClient.SqlCommand

    cmd.CommandType = System.Data.CommandType.Text
    cmd.CommandText = "DELETE FROM DoctorEnterpriseDetails WHERE (EnterpriseId <> 26) AND DoctorId = " & Form1.Label8.Text
    cmd.Connection = sqlConnection1

    cmd2.CommandType = System.Data.CommandType.Text
    cmd2.CommandText = "DELETE FROM DoctorEnterpriseId WHERE (EnterpriseId <> 26) AND DoctorId = " & Form1.Label8.Text
    cmd2.Connection = sqlConnection1

    cmd3.CommandType = System.Data.CommandType.Text
    cmd3.CommandText = "DELETE FROM DoctorRepresentative WHERE (EnterpriseId <> 26) AND DoctorId = " & Form1.Label8.Text
    cmd3.Connection = sqlConnection1

    sqlConnection1.Open()
    cmd.ExecuteNonQuery()
    cmd2.ExecuteNonQuery()
    cmd3.ExecuteNonQuery()
    sqlConnection1.Close()

End Sub

Public Sub GravarAddress(DGV5)
    Dim myConnectionString As SqlConnection = New SqlConnection("Data Source=SERVER\SQLEXPRESS;Initial Catalog=GSM81;user=sa;password=senha")
    Dim myCommand As String
    Dim cmd As SqlCommand
    Dim MailAdd As Integer
    Dim PlaceT As Integer


    For i = 0 To DGV5.Rows.Count - 2
        'verificar se o campo Mailling Address é nulo ou não
        If IsDBNull(DGV5(11, i).Value) Then
            MailAdd = 0
        ElseIf DGV5(11, i).Value = True Then
            MailAdd = -1
        Else
            MailAdd = 0
        End If

        'verificar se o campo Place Type é nulo ou não
        If IsDBNull(DGV5(10, i).Value) Then
            PlaceT = 0
        ElseIf DGV5(10, i).Value = True Then
            PlaceT = -1
        Else
            PlaceT = 0
        End If

        myCommand = "update doctorsaddress set " & _
            "Address = '" & DGV5(6, i).Value & "', " & _
            "PostalCode = " & DGV5(7, i).Value & ", " & _
            "PlaceTypeId = " & DGV5(8, i).Value & ", " & _
            "Schedule = '" & DGV5(9, i).Value & "', " & _
            "Placeofvisit = " & PlaceT & ", " & _
            "MailingAddress = " & MailAdd & " " & _
            "where addressid = " & DGV5(5, i).Value

        cmd = New SqlCommand(myCommand, myConnectionString)

        cmd.Connection.Open()   'abrir connecção
        cmd.ExecuteNonQuery()   'escreve na BD
        cmd.Connection.Close()  'fecha a conecção

    Next i
End Sub


Public Sub GravarEnterpriseDetails(DGV3)
    Dim myConnectionString As SqlConnection = New SqlConnection("Data Source=SERVER\SQLEXPRESS;Initial Catalog=GSM81;user=sa;password=senha")
    Dim myCommand As String
    Dim cmd As SqlCommand
    Dim i As Integer

    For i = 0 To DGV3.Rows.Count - 2


        myCommand = "UPDATE DoctorEnterpriseDetails SET " & _
              "RankId = " & DGV3(5, i).Value & ", " & _
              "GroupId = " & DGV3(6, i).Value & ", " & _
              "PrescribingPotential = " & DGV3(9, i).Value & ", " & _
              "ClinicalProfileId = " & DGV3(23, i).Value & ", " & _
              "Telephone = " & DGV3(3, i).Value & ", " & _
              "Mobile = " & DGV3(4, i).Value & ", " & _
              "PlaceOfVisit = " & DGV3(7, i).Value & ", " & _
              "HoursAvailable = " & DGV3(8, i).Value & ", " & _
              "Speciality1 = " & DGV3(10, i).Value & ", " & _
              "Speciality2 = " & DGV3(11, i).Value & ", " & _
              "PostalCodeId = " & DGV3(12, i).Value & ", " & _
              "Observation = '" & DGV3(13, i).Value & "', " & _
              "MailingAddress = " & DGV3(18, i).Value & ", " & _
              "CableTv = " & DGV3(19, i).Value & ", " & _
              "ClinicName = '" & DGV3(20, i).Value & "', " & _
               "Zone = '" & DGV3(21, i).Value & "', " & _
               "Schedule = '" & DGV3(22, i).Value & "' " & _
                "WHERE (EnterpriseId = 26)"

        cmd = New SqlCommand(myCommand, myConnectionString)
        cmd.Connection.Open()   'abrir connecção
        cmd.ExecuteNonQuery()   'escreve na BD
        cmd.Connection.Close()  'fecha a conexão

    Next i


End Sub

Public Sub GravarDoctorBasic(DGV1)

    Dim myConnectionString As SqlConnection = New SqlConnection("Data Source=SERVER\SQLEXPRESS;Initial Catalog=GSM81;user=sa;password=senha")
    Dim myCommand As String
    Dim cmd As SqlCommand
    Dim i As Integer
    Dim act As Integer


    If IsDBNull(DGV1(19, i).Value) Then
        act = 0
    ElseIf DGV1(19, i).Value = True Then
        act = -1
    Else
        act = 0
    End If

    myCommand = "UPDATE DoctorBasic SET " & _
    "NOofClinics = " & DGV1(13, 0).Value & ", " & _
    "Lusov = '" & DGV1(20, 0).Value & "', " & _
    "FirstName = '" & DGV1(6, 0).Value & "', " & _
    "LastName = '" & DGV1(7, 0).Value & "', " & _
    "FullName = '" & DGV1(8, 0).Value & "', " & _
     "DOB = " & DGV1(9, 0).Value & ", " & _
     "GraduateDate = " & DGV1(10, 0).Value & ", " & _
    "PlaceofGraduation = '" & DGV1(11, 0).Value & "', " & _
     "LevelofPractice = " & DGV1(12, 0).Value & ", " & _
     "OtherPositions = '" & DGV1(14, 0).Value & "', " & _
     "Emailid = '" & DGV1(15, 0).Value & "', " & _
     "Degree = '" & DGV1(16, 0).Value & "', " & _
     "Observation = '" & DGV1(17, 0).Value & "', " & _
     "LanguageId = " & DGV1(18, 0).Value & ", " & _
     "Active = " & act & " " & _
    "WHERE DoctorId = " & DGV1(0, 0).Value


    cmd = New SqlCommand(myCommand, myConnectionString)
    MsgBox(myCommand)

    cmd.Connection.Open()
    cmd.ExecuteNonQuery()
    cmd.Connection.Close()

End Sub

Public Sub AlteracoesGrid(DGV1, DGV3, DGV5)

    Dim i As Integer
    Dim j As Integer

    ' para preencher a coluna do potencial de prescrição (id = 8 para 5), RankId(48 para Selecção)
    'Perfil clinico = 8, GroupId = 8 (Categoria - Médicos) 



    For i = 0 To DGV3.Rows.Count - 1
        Select Case DGV3(2, i).Value
            Case 26
                DGV3(9, i).Value = 8
                DGV3(5, i).Value = 48
                DGV3(23, i).Value = 8
                DGV3(6, i).Value = 84

        End Select

    Next
    GravarEnterpriseDetails(DGV3)


    'guardar na coluna Lusov (observações)
    Dim Obs As String
    Obs = Trim(DGV1(20, 0).Value.ToString)
    For j = 0 To DGV5.Rows.Count - 2

        If Obs = "" Then

            Obs = checknull(DGV5(6, j).Value.ToString) & " - " & checknull(DGV5(7, j).Value.ToString) & " - " & checknull(DGV5(8, j).Value.ToString)
        Else
            Obs = Obs & Chr(13) & Chr(10) & checknull(DGV5(6, j).Value.ToString) & " - " & checknull(DGV5(7, j).Value.ToString) & " - " & checknull(DGV5(8, j).Value.ToString)

        End If
    Next

    DGV1(20, 0).Value = Obs
    DGV1(13, 0).Value = 0   'para nr de clinicas
    GravarDoctorBasic(DGV1)


    'para a morada 

    For j = 0 To DGV5.Rows.Count - 2
        DGV5(6, j).Value = "NÃO DEFINIDO" ' para a morada
        DGV5(7, j).Value = 421705           'para o codigo postal
        DGV5(8, j).Value = 0            'para o tipo local
        DGV5(9, j).Value = ""   ' para o horário
        DGV5(10, j).Value = True   ' visto para o LocalVisita


    Next
    GravarAddress(DGV5)

End Sub

The code I started to make for the other button was this

    Dim myConnectionString As SqlConnection = New SqlConnection("Data Source=******;Initial Catalog=GSM81;user=sa;password=senha")
    Dim myCommand As String
    Dim cmd As SqlCommand


    TextBox1.Text = DGV1(5, 0).Value
    CarregarGrid()
    btnAlterarDadosMedico(sender, e)



    myCommand = "SELECT Code FROM DoctorBasic WHERE CONVERT(int,ROUND(DATEDIFF(hour,DOB,GETDATE())/8766.0,0)) > 80 "

    cmd = New SqlCommand(myCommand, myConnectionString)
    MsgBox(myCommand)
    cmd.Connection.Open()
    cmd.ExecuteNonQuery()
    cmd.Connection.Close()

I am missing a loop here and this to do the same for all the rows of the table where the doctors are but I need help because I can’t do it . I think I can do with one of the while but I’m missing the logic. If you need more information just say.