Imports System.Data
Imports System.Data.OleDb
Public Class FrmPatien
Dim CRow As Integer
Dim pnd As BindingManagerBase
Dim pnd1 As BindingManagerBase
'تعريف Command
Dim Cmd As New OleDb.OleDbCommand
Dim SqlStr As String
Dim SqlStr1 As String
'تعريف قاعدة البيانات ومكانها لفتحها على اساس انها موجوده تحت الدليل
'bin\debug
Dim ConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" & _
Application.StartupPath & "\Laboratory.mdb"
'فتج قاعدة البيانات
Dim Conn As New OleDbConnection(ConStr)
Dim ds1 As New DataSet
Dim ds2 As New DataSet
Private Sub FrmPatien_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
bind()
End Sub
Private Sub bind()
Conn.Open()
ds1.Clear()
ds2.Clear()
SqlStr = "select *from Patient order by Patient_No asc"
SqlStr1 = "select *from d order by Patient_No asc"
Dim Dr As New OleDbDataAdapter(SqlStr, Conn)
Dim Dr1 As New OleDbDataAdapter(SqlStr1, Conn)
Dr.Fill(ds1, "Patient")
Dr1.Fill(ds2, "d")
Conn.Close()

pnd = Me.BindingContext(ds1, "Patient")
pnd1 = Me.BindingContext(ds2, "d")
Conn.Close()
End Sub


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
'تحميل ال command بقاعدة البيانات بعد فتحها
Cmd.Connection = Conn
'تعريف الtype ب text لتنفيذ عبارة Sql
Cmd.CommandType = CommandType.Text
'جملة Sql لتسجيل البيانات داخل قاعدة البيانات
SqlStr = "INSERT INTO Patient(Patient_No,Patient_Name,Patient_Analysis_con ,Patient_Entry_Date)" & _
"values (" & TextBox1.Text & ",'" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "') "
Cmd.CommandText = SqlStr
'فتح الأتصال
Conn.Open()
'التسجيل فى قواعد البيانات
Cmd.ExecuteNonQuery()
Conn.Close()
MsgBox("تمت عملية الحفظ في قاعدة البيانات بنجاح")
Catch ex As Exception
MsgBox(ex.Message)
End Try
bind()
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Cmd.Connection = Conn
Cmd.CommandType = CommandType.Text
SqlStr = "UPDATE Patient SET Patient_Name = '" & Trim(TextBox2.Text) & _
"' , Patient_Analysis_con = '" & Trim(TextBox3.Text) & _
"' , Patient_Entry_Date= '" & Trim(TextBox4.Text) & _
"' WHERE Patient_No = " & Trim(TextBox1.Text) & " "
Cmd.CommandText = SqlStr
Conn.Open()
Cmd.ExecuteNonQuery()
Conn.Close()
MsgBox("تمت عملية التعديل في قاعدة البيانات بنجاح")
bind()
End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim ds As New DataSet
'بحث برقم المريض 
SqlStr = "select *from Patient where Patient_No = " & Trim(TextBox1.Text) & " "
'بحث بأسم المريض 
'لو أردتى تفعيلها
'SqlStr = "select *from Patient where Patient_Name = '" & Trim(TextBox2.Text) & "'"
'بحث بالتاريخ 
'لو أردتى تفعيله
'SqlStr = "select *from Patient where Patient_Entry_Date = '" & Trim(TextBox4.Text) & "'"
Conn.Open()
Dim Dr As New OleDbDataAdapter(SqlStr, Conn)
Dr.Fill(ds, "Patient")
Conn.Close()
With ds.Tables("Patient").Rows(0)
TextBox2.Text = .Item("Patient_Name")
TextBox3.Text = .Item("Patient_Analysis_con")
TextBox4.Text = .Item("Patient_Entry_Date")
End With
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
If MsgBox("هل تريد حذف السجل الحالي", MsgBoxStyle.OkCancel) = MsgBoxResult.Cancel Then
Exit Sub
End If
Cmd.Connection = Conn
Cmd.CommandType = CommandType.Text
Cmd.CommandText = "DELETE FROM Patient WHERE Patient_No = " & Trim(TextBox1.Text) & ""
Conn.Open()
Cmd.ExecuteNonQuery()
Conn.Close()
MsgBox("تمت عملية الحذف من قاعدة البيانات بنجاح")
bind()
End Sub

Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
pnd.Position = 0
CRow = pnd.Position
FillTxt()
ChangePosition()
End Sub
Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
pnd.Position -= 1
CRow = pnd.Position
FillTxt()
ChangePosition()
End Sub
Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
pnd.Position += 1
CRow = pnd.Position
FillTxt()
ChangePosition()
End Sub
Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click
pnd.Position = pnd.Count - 1
CRow = pnd.Position
FillTxt()
ChangePosition()
End Sub
Public Sub ChangePosition()
Label5.RightToLeft = Windows.Forms.RightToLeft.Yes
Label5.Text = "السجل " & Me.BindingContext(ds1, "Patient").Position & _
" من " & Me.BindingContext(ds1, "Patient").Count - 1
End Sub
Private Sub FillTxt()
With ds1.Tables("Patient").Rows(CRow)
TextBox1.Text = .Item("Patient_No")
TextBox2.Text = .Item("Patient_Name")
TextBox3.Text = .Item("Patient_Analysis_con")
TextBox4.Text = .Item("Patient_Entry_Date")
End With
End Sub
Private Sub Button9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button9.Click
Dim frm As New FrmView
frm.ShowDialog()
End Sub
End Class