I wanted you to help me in the next one, I wanted to execute the code that I will show you under a button so that it would automatically do it when I clicked on it.
The program works as follows: first I have to insert a code in a textbox to call a record (a doctor in this case) and if the doctor is older than 80 years, I press a button to make a series of changes, which I intend that in another button, the program will find 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 method code:
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
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'm 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 not do .. I think I can do with one of the while but I'm missing the logic. If you need more information just say so.