Imports System.Data
Imports System.Data.SqlClient
Public Class Treatment
Private StrConnString As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=D:\NeeNa&NaNee's Project\ClinicProject\clinic.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
Private Sub Treatment_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'ClinicDataSet.doctor' table. You can move, or remove it, as needed.
Me.DoctorTableAdapter.Fill(Me.ClinicDataSet.doctor)
Dim pid As String
pid = Patient.MaskedTextBox1.Text
Maskedtxtpid.Text = pid
Dim objConn As SqlConnection
Dim strSQL As String
objConn = New SqlConnection(strConnString)
objConn.Open()
Dim dtAdapter As SqlDataAdapter
Dim dt As New DataTable
strSQL = "SELECT * FROM patient WHERE p_id = '" & Maskedtxtpid.Text & "'"
dtAdapter = New SqlDataAdapter(strSQL, objConn)
dtAdapter.Fill(dt)
If dt.Rows.Count > 0 Then
Me.txtp_fname.Text = dt.Rows(0)("p_name")
Me.txtp_lname.Text = dt.Rows(0)("p_sur")
End If
Me.btnedit.Enabled = False
Me.btndelete.Enabled = False
Me.btnsave.Enabled = True
Me.btnclose.Enabled = True
Me.btnclear.Enabled = True
Me.btnprescrip.Enabled = False
runtid()
SetSearchDoc()
showdata()
End Sub
Private Sub runtid()
Dim objConn As New SqlConnection(StrConnString)
Dim da As SqlDataAdapter
Dim ds As New DataSet
Dim dt As New DataTable
Dim sqlShowdata As String = "SELECT * from treat Order By t_id desc"
objConn.Open()
da = New SqlDataAdapter(sqlShowdata, objConn)
da.Fill(ds, "t_id")
dt = ds.Tables("t_id")
If dt.Rows.Count = 0 Then
txtidtreat.Text = 1
Else
txtidtreat.Text = dt.Rows.Count + 1
End If
End Sub
Private Sub SetSearchDoc()
Dim objConn As New SqlConnection(StrConnString)
Dim objCmd As New SqlCommand
Dim dr As SqlDataReader
Dim dt As DataTable
Dim docidlist As New AutoCompleteStringCollection
With objConn
If .State = ConnectionState.Open Then .Close()
.ConnectionString = StrConnString
.Open()
End With
Dim sqldocid As String
sqldocid = "SELECT * FROM doctor"
Me.Cursor = Cursors.WaitCursor
With objCmd
.CommandText = sqldocid
.CommandType = CommandType.Text
.Connection = objConn
dr = .ExecuteReader()
If dr.HasRows Then
dt = New DataTable()
dt.Load(dr)
dr.Close()
For Each drw As DataRow In dt.Rows
docidlist.Add(drw.Item("doc_id").ToString())
Next
Else
dr.Close()
End If
End With
Me.Cursor = Cursors.Default
With txtdocid
.AutoCompleteSource = AutoCompleteSource.CustomSource
.AutoCompleteCustomSource = docidlist
.AutoCompleteMode = AutoCompleteMode.SuggestAppend
End With
End Sub
Private Sub btnsearchdoc_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsearchdoc.Click
Dim sqlSelectQuery As String = "Select * From doctor WHERE doc_id ='" & txtdocid.Text & "'"
Dim sqlReader As SqlDataReader
Dim objConn As New SqlConnection(StrConnString)
Dim objCmd As New SqlCommand
objCmd = New SqlCommand(sqlSelectQuery, objConn)
objConn.Open()
sqlReader = objCmd.ExecuteReader()
sqlReader.Read()
Me.txtdocid.Text = sqlReader.Item("doc_id")
Me.txtgender.Text = sqlReader.Item("gender")
Me.txtd_fname.Text = sqlReader.Item("doc_name")
Me.txtd_lname.Text = sqlReader.Item("doc_sur")
End Sub
Private Sub showdata()
Dim sqlShowdata As String = "select * from view_treat"
Dim objConn As New SqlConnection(StrConnString)
Dim objCmd As New SqlCommand(sqlShowdata, objConn)
Dim da As SqlDataAdapter
Dim ds As New DataSet
objConn.Open()
da = New SqlDataAdapter(sqlShowdata, objConn)
da.Fill(ds, "view_treat")
With DataGridView1
.DataSource = ds.Tables("view_treat")
.Columns(0).HeaderText = "รหัสการรักษา"
.Columns(1).HeaderText = "เลขที่บัตรประชาชนผู้ป่วย"
.Columns(2).HeaderText = "ชื่อผู้ป่วย"
.Columns(3).HeaderText = "นามสกุล"
.Columns(4).HeaderText = "วันที่เข้ารับการรักษา"
.Columns(5).HeaderText = "อาการที่เข้ามารักษา"
.Columns(6).HeaderText = "อาการที่รักษาจริง"
.Columns(7).HeaderText = "คำวินิจฉัย"
.Columns(8).HeaderText = "ค่ารักษา"
.Columns(9).HeaderText = "รวมค่ายา"
.Columns(10).HeaderText = "รวมทั้งสิ้น"
.Columns(11).HeaderText = "รหัสแพทย์"
.Columns(12).HeaderText = "คำนำหน้าชื่อ"
.Columns(13).HeaderText = "ชื่อแพทย์"
.Columns(14).HeaderText = "นามสกุล"
End With
End Sub
Private Sub txtprice_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtprice.TextChanged
Dim a, b, sum As Integer
If Me.txtprice.Text = "0" Then
Me.txtsum.Text = Me.txtpricedrug.Text
Else
a = CInt(Me.txtprice.Text)
b = CInt(Me.txtpricedrug.Text)
sum = a + b
Me.txtsum.Text = CStr(sum)
End If
End Sub
Private Sub txtpricedrug_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtpricedrug.TextChanged
Dim a, b, sum As Integer
If Me.txtpricedrug.Text = "0" Then
Me.txtsum.Text = Me.txtprice.Text
Else
a = CInt(Me.txtprice.Text)
b = CInt(Me.txtpricedrug.Text)
sum = a + b
Me.txtsum.Text = CStr(sum)
End If
End Sub
Public Sub ReadMyData(ByVal myConnString As String)
Dim sqlSelectQuery As String = "Select * From treat"
Dim objConn As New SqlConnection(StrConnString)
Dim objCmd As New SqlCommand(sqlSelectQuery, objConn)
Dim sqlReader As SqlDataReader
objConn.Open()
sqlReader = objCmd.ExecuteReader()
sqlReader.Read()
End Sub
Public Sub InsertMydata(ByVal myConnString As String)
If Me.txtdocid.Text = "" Or Me.txtsymptoms.Text = "" Or Me.txttreatTrue.Text = "" Or Me.txtdiagnose.Text = "" Then
MessageBox.Show("คุณป้อนข้อมูลไม่ครบ!", "ผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
Dim sqlInsert As String = "INSERT INTO treat(t_id,p_id,t_date,symptoms,treatTrue,diagnose,treatment,sum_drug,total_price,doc_id)VALUES('" & Me.txtidtreat.Text & "','" & Me.Maskedtxtpid.Text & "','" & Me.DateTimePicker1.Value & "','" & Me.txtsymptoms.Text & "','" & Me.txttreatTrue.Text & "','" & Me.txtdiagnose.Text & "','" & Me.txtprice.Text & "','" & Me.txtpricedrug.Text & "','" & Me.txtsum.Text & "','" & Me.txtdocid.Text & "')"
Dim objConn As New SqlConnection(StrConnString)
Dim objCmd As New SqlCommand(sqlInsert, objConn)
objConn.Open()
Try
objCmd.ExecuteNonQuery()
MessageBox.Show("บันทึกข้อมูลการรักษาเรียบร้อย", "บันทึกข้อมูลผู้ป่วย", MessageBoxButtons.OK, MessageBoxIcon.Information)
Me.btnprescrip.Enabled = True
Me.btnsave.Enabled = False
Me.btnedit.Enabled = True
Me.btndelete.Enabled = True
Me.btnclear.Enabled = True
showdata()
Catch ex As Exception
MessageBox.Show("Record Cannot Insert : Error (" & ex.Message & ")")
'MessageBox.Show("ไม่สามารถบันทึกข้อมูลการรักษาได้", "ผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
objConn.Close()
End If
End Sub
Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
InsertMydata(StrConnString)
End Sub
Private Sub btnclear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnclear.Click
Me.txtidtreat.Clear()
Me.DateTimePicker1.Value = Now
Me.Maskedtxtpid.Clear()
Me.txtsymptoms.Clear()
Me.txttreatTrue.Clear()
Me.txtdiagnose.Clear()
Me.txtprice.Text = "0"
Me.txtpricedrug.Text = "0"
Me.txtsum.Text = "0"
Me.btnsave.Enabled = True
Me.btnclose.Enabled = True
End Sub
Me.txtidtreat.Enabled = False
Me.Maskedtxtpid.Enabled = False
Me.btnsave.Enabled = False
Me.btnclear.Enabled = True
Me.btndelete.Enabled = True
Me.btnedit.Enabled = True
Me.btnprescrip.Enabled = True
End With
End Sub
Private Sub btnedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnedit.Click
Dim sqlUpdate As String = "UPDATE treat Set t_date = '" & Me.DateTimePicker1.Value & "', symptoms = '" & Me.txtsymptoms.Text & "', treatTrue = '" & Me.txttreatTrue.Text & "', diagnose = '" & Me.txtdiagnose.Text & "', treatment = '" & Me.txtprice.Text & "', sum_drug = '" & Me.txtpricedrug.Text & "' , total_price = '" & Me.txtsum.Text & "', doc_id = '" & Me.txtdocid.Text & "' WHERE t_id = '" & Me.txtidtreat.Text & "'"
Dim objConn As New SqlConnection(StrConnString)
Dim objCmd As New SqlCommand(sqlUpdate, objConn)
objConn.Open()
objCmd.ExecuteNonQuery()
objConn.Close()
MessageBox.Show("แก้ไขข้อมูลผู้ป่วยเรียบร้อยค่ะ", "แก้ไขข้อมูล", MessageBoxButtons.OK, MessageBoxIcon.Information)
Me.btnsave.Enabled = False
Me.btnclear.Enabled = True
Me.btnclose.Enabled = True
Me.btndelete.Enabled = True
showdata()
End Sub
Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
Dim sqlDelete As String = ("Delete From treat where t_id ='" & Me.txtidtreat.Text & "'")
Dim objConn As New SqlConnection(StrConnString)
Dim objCmd As New SqlCommand(sqlDelete, objConn)
Me.txtidtreat.Text = ""
Me.Maskedtxtpid.Text = ""
'Me.DateTimePicker1.Text = ""
Me.txtsymptoms.Text = ""
Me.txttreatTrue.Text = ""
Me.txtdiagnose.Text = ""
Me.txtprice.Text = "0"
Me.txtpricedrug.Text = "0"
Me.txtsum.Text = "0"
Me.txtdocid.Text = ""
Me.txtgender.Text = ""
Me.txtd_fname.Text = ""
Me.txtd_lname.Text = ""
objConn.Open()
objCmd.ExecuteNonQuery()
objConn.Close()
'showdata()
objConn.Open()
Try
objCmd.ExecuteNonQuery()
MessageBox.Show("คุณต้องการลบข้อมูลใช่หรือไม่?", "ลบข้อมูล", MessageBoxButtons.OKCancel, MessageBoxIcon.Question)
'showdata()
Me.Close()
Catch ex As Exception
MessageBox.Show("ไม่สามารถลบข้อมูลการรักษาได้ เนื่องจากมีข้อมูลถูกใช้งานอยู่", "ผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
objConn.Close()
End Sub
Private Sub btnagain_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnagain.Click
Meet.Show(Maskedtxtpid)
End Sub
Private Sub btnclose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnclose.Click
Patient.MaskedTextBox1.Text = Maskedtxtpid.Text
Me.Close()
End Sub
Private Sub btnprescrip_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnprescrip.Click
Prescription.Show(txtidtreat)
Me.btnsave.Enabled = False
Me.btnedit.Enabled = True
Me.btnclear.Enabled = False
Me.btnclose.Enabled = False
Me.btndelete.Enabled = False
End Sub
End Class
พอรันแล้วมันขึ้นว่า Record can not insert Error (the conversion of a varchar data type to a datetime data type resulted in an out-of-range value)
ในฐานข้อมูลSQL Server จูนกำหนดให้ t_date เป็นข้อมูลชนิดdatetimeค่ะ
Tag : .NET, Ms SQL Server 2008, Win (Windows App), VS 2010 (.NET 4.x)
Load balance : Server 02
