my program should update and concatinate data in one cell when there is already a row with the same date as today and Patient_ID but it seems like the compiler won't read the 3rd if else statement and jumps to else
Private Sub txtUR3rdMolar_KeyDown(sender As Object, e As KeyEventArgs) Handles txtUR3rdMolar.KeyDown
MySqlConn.Close()
If e.KeyCode = Keys.Enter Then
If txtUR3rdMolar.Text = "" Then
MsgBox("Please fill out this field!", MsgBoxStyle.Exclamation, "Blank Input")
Else
MySqlConn.Open()
Dim query6 As String
Dim reader6 As MySqlDataReader
query6 = "SELECT * FROM teeth WHERE left(Date_,10) = left('" & dtpDate.Value & "',10) AND Patient_ID_Number='" & lblID.Text & "'"
cmd = New MySqlCommand(query6, MySqlConn)
reader6 = cmd.ExecuteReader
'=================================UPDATING TEETH=================================
If reader6.HasRows Then '*******COMPILER NOT READING THIS PART****
Dim i As Integer
With cmd
.Connection = MySqlConn
.CommandText = "UPDATE teeth SET UR3rdMolar =concat('" & txtUR3rdMolar.Text & Environment.NewLine & "',UR3rdMolar) WHERE left(Date_,10) = left('" & dtpDate.Value & "',10) AND Patient_ID_Number='" & lblID.Text & "'"
reader6.Close()
i = .ExecuteNonQuery
End With
If i > 0 Then
MsgBox("Teeth history updated!", MsgBoxStyle.Information, "Success")
'================================STORING AMOUNT=================================
Dim query1 As String
Dim rdr_Amount As MySqlDataReader
query1 = "SELECT * FROM services WHERE Service_Name ='" & txtUR3rdMolar.Text & "'"
cmd = New MySqlCommand(query1, MySqlConn)
rdr_Amount = cmd.ExecuteReader
If rdr_Amount.Read Then
lblAmount.Text = rdr_Amount.Item("Amount")
End If
'=================================SHOWING COLUMN DATA TO dgv=================================
'==============REFRESH DGV====================
Dim DataAdapter2 As New MySqlDataAdapter
Dim DataTable2 As New DataTable
Dim cmd3 As New MySqlCommand
With cmd3
.Connection = MySqlConn
.CommandText = "SELECT UR3rdMolar, Braces, Date_ FROM teeth like '%" & dtpDate.Value & "%' WHERE Patient_ID_Number='" & lblID.Text & "'"
End With
DataAdapter2.SelectCommand = cmd3
DataTable2.Clear()
rdr_Amount.Close()
DataAdapter2.Fill(DataTable2)
dgvUR3rdMolar.DataSource = DataTable2
'=================================SAVING TO BILLING=================================
Dim cmd5 As MySqlCommand = MySqlConn.CreateCommand
cmd5.CommandType = CommandType.Text
cmd5.CommandText = String.Format("INSERT INTO billing_temp (Patient_ID, Fullname, Services, Amount, Date_)" &
"values ('{0}' ,'{1}', '{2}', '{3}', '{4}')",
lblID.Text,
lblFullname.Text,
txtUR3rdMolar.Text,
lblAmount.Text,
dtpDate.Value)
Dim affectedrows2 As Integer = cmd5.ExecuteNonQuery()
If affectedrows2 > 0 Then
'=================================UPDATING FINAL BILL=================================
Dim query9 As String
Dim rdr As MySqlDataReader
query9 = "SELECT * FROM transaction WHERE left(Date_,10) = left('" & dtpDate.Value & "',10) AND Patient_ID=" & lblID.Text
cmd = New MySqlCommand(query9, MySqlConn)
rdr = cmd.ExecuteReader
If rdr.HasRows Then
Dim y As Integer
With cmd
.Connection = MySqlConn
.CommandText = "UPDATE transaction SET Services =concat('" & txtUR3rdMolar.Text & "; " & "',Services) WHERE left(Date_,10) = left('" & dtpDate.Value & "',10) AND Patient_ID=" & lblID.Text
rdr.Close()
y = .ExecuteNonQuery
End With
If y > 0 Then
txtUR3rdMolar.Text = ""
End If
Else
'=================================SAVING TO FINAL BILL=================================
Dim cmd7 As MySqlCommand = MySqlConn.CreateCommand
cmd7.CommandType = CommandType.Text
cmd7.CommandText = String.Format("INSERT INTO transaction (Patient_ID, Fullname, Services, Date_)" &
"values ('{0}' ,'{1}', '{2}', '{3}')",
lblID.Text,
lblFullname.Text,
txtUR3rdMolar.Text,
dtpDate.Value)
rdr.Close()
Dim affectedrows3 As Integer = cmd7.ExecuteNonQuery()
If affectedrows3 > 0 Then
txtUR3rdMolar.Text = ""
End If
End If
End If
End If
Else
'=================================SAVING TO TEETH=================================
Dim cmd4 As MySqlCommand = MySqlConn.CreateCommand
cmd4.CommandType = CommandType.Text
cmd4.CommandText = String.Format("INSERT INTO teeth (Patient_ID_Number, Fullname, Date_, ULCentralIncisor, ULLateralIncisor, ULCanine, UL1stPremolar, UL2ndPremolar, UL1stMolar, UL2ndMolar, UL3rdMolar, URCentralIncisor, URLateralIncisor, URCanine, UR1stPremolar, UR2ndPremolar, UR1stMolar, UR2ndMolar, UR3rdMolar, LLCentralIncisor, LLLateralIncisor, LLCanine, LL1stPremolar, LL2ndPremolar, LL1stMolar, LL2ndMolar, LL3rdMolar, LRCentralIncisor, LRLateralIncisor, LRCanine, LR1stPremolar, LR2ndPremolar, LR1stMolar, LR2ndMolar, LR3rdMolar)" &
"VALUES ('{0}' ,'{1}' ,'{2}' ,'{3}' ,'{4}' ,'{5}' ,'{6}' ,'{7}' ,'{8}' ,'{9}' ,'{10}' ,'{11}' ,'{12}' ,'{13}' ,'{14}' ,'{15}' ,'{16}' ,'{17}' ,'{18}' ,'{19}' ,'{20}' ,'{21}' ,'{22}' ,'{23}' ,'{24}' ,'{25}' ,'{26}' ,'{27}' ,'{28}' ,'{29}' ,'{30}' ,'{31}' ,'{32}' ,'{33}','{34}')",
lblID.Text,
lblFullname.Text,
dtpDate.Value,
txtULCentralIncisor.Text,
txtULLateralIncisor.Text,
txtULCanine.Text,
txtUL1stPremolar.Text,
txtUL2ndPremolar.Text,
txtUL1stMolar.Text,
txtUL2ndMolar.Text,
txtUL3rdMolar.Text,
txtURCentralIncisor.Text,
txtURCentralIncisor.Text,
txtURCanine.Text,
txtUR1stPremolar.Text,
txtUR2ndPremolar.Text,
txtUR1stMolar.Text,
txtUR2ndMolar.Text,
txtUR3rdMolar.Text,
txtLLCentralIncisor.Text,
txtLLLateralIncisor.Text,
txtLLCanine.Text,
txtLL1stPremolar.Text,
txtLL2ndPremolar.Text,
txtLL1stMolar.Text,
txtLL2ndMolar.Text,
txtLL3rdMolar.Text,
txtLRCentralIncisor.Text,
txtLRCentralIncisor.Text,
txtLRCanine.Text,
txtLR1stPremolar.Text,
txtLR2ndPremolar.Text,
txtLR1stMolar.Text,
txtLR2ndMolar.Text,
txtLR3rdMolar.Text)
reader6.Close()
Dim affectedrows1 As Integer = cmd4.ExecuteNonQuery()
If affectedrows1 > 0 Then
MsgBox("Saved!", MsgBoxStyle.Information, "Success")
'================================STORING AMOUNT=================================
Dim query1 As String
Dim rdr_Amount As MySqlDataReader
query1 = "SELECT * FROM services WHERE Service_Name ='" & txtUR3rdMolar.Text & "'"
cmd = New MySqlCommand(query1, MySqlConn)
rdr_Amount = cmd.ExecuteReader
If rdr_Amount.Read Then
lblAmount.Text = rdr_Amount.Item("Amount")
End If
'==============REFRESH DGV====================
Dim DataAdapter2 As New MySqlDataAdapter
Dim DataTable2 As New DataTable
Dim cmd3 As New MySqlCommand
With cmd3
.Connection = MySqlConn
.CommandText = "SELECT UR3rdMolar, Braces, Date_ FROM teeth Where left(Date_,10) = left('" & dtpDate.Value & "',10) and Patient_ID_Number='" & lblID.Text & "'"
End With
DataAdapter2.SelectCommand = cmd3
DataTable2.Clear()
rdr_Amount.Close()
DataAdapter2.Fill(DataTable2)
dgvUR3rdMolar.DataSource = DataTable2
'=================================SAVING TO BILLING=================================
Dim cmd5 As MySqlCommand = MySqlConn.CreateCommand
cmd5.CommandType = CommandType.Text
cmd5.CommandText = String.Format("INSERT INTO billing_temp (Patient_ID, Fullname, Services, Amount, Date_)" &
"values ('{0}' ,'{1}', '{2}', '{3}', '{4}')",
lblID.Text,
lblFullname.Text,
txtUR3rdMolar.Text,
lblAmount.Text,
dtpDate.Value)
Dim affectedrows2 As Integer = cmd5.ExecuteNonQuery()
If affectedrows2 > 0 Then
'=================================UPDATING FINAL BILL=================================
Dim query8 As String
Dim rdr1 As MySqlDataReader
query8 = "SELECT * FROM transaction WHERE left(Date_,10) = left('" & dtpDate.Value & "',10) AND Patient_ID=" & lblID.Text
cmd = New MySqlCommand(query8, MySqlConn)
rdr1 = cmd.ExecuteReader
If rdr1.HasRows Then
Dim y As Integer
With cmd
.Connection = MySqlConn
.CommandText = "UPDATE transaction SET Services =concat('" & txtUR3rdMolar.Text & "; " & "',Services) WHERE left(Date_,10) = left('" & dtpDate.Value & "',10) AND Patient_ID=" & lblID.Text
rdr1.Close()
y = .ExecuteNonQuery
End With
If y > 0 Then
txtUR3rdMolar.Text = ""
End If
Else
'=================================SAVING TO FINAL BILL=================================
Dim cmd7 As MySqlCommand = MySqlConn.CreateCommand
cmd7.CommandType = CommandType.Text
cmd7.CommandText = String.Format("INSERT INTO transaction (Patient_ID, Fullname, Services, Date_)" &
"values ('{0}' ,'{1}', '{2}', '{3}')",
lblID.Text,
lblFullname.Text,
txtUR3rdMolar.Text,
dtpDate.Value)
rdr1.Close()
Dim affectedrows3 As Integer = cmd7.ExecuteNonQuery()
If affectedrows3 > 0 Then
txtUR3rdMolar.Text = ""
End If
End If
End If
Else
MsgBox("failed to update record!", MsgBoxStyle.Information, "failed")
End If
End If
MySqlConn.Close()
End If
End If
End Sub
Aucun commentaire:
Enregistrer un commentaire