Imports System.Data
Imports System.Data.OleDb

Public Class Form1
    Dim ds As New DataSet
    Dim dt As New DataTable("Documents")
    Dim ConStr As String = "Provider=Microsoft.ace.OLEDB.12.0;" & _
"Data Source =" & Application.StartupPath & "\Eleves.accdb"
    Dim Conn As New OleDbConnection(ConStr)
    Dim DataSet1 As New DataSet
    Dim SQLstr As String = "SELECT * FROM Documents"
    Dim m As String
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Static m, i As Integer
        m = 0
        i = 0
        Conn.Open()
        Dim DataAdapter1 As New OleDbDataAdapter(SQLstr, Conn)
        DataAdapter1.Fill(DataSet1, "Documents")
        Conn.Close()
        TextBox5.DataBindings.Add("Text", DataSet1, "Documents.Num")
        TextBox1.DataBindings.Add("Text", DataSet1, "Documents.Document")
        TextBox2.DataBindings.Add("Text", DataSet1, "Documents.DateDoc")

        ChangePosition()
        
        With Me.DataGridView1 ' اضهار سطور بالوان مختلقة في الداتي قريد
            .RowsDefaultCellStyle.BackColor = Color.Bisque
            .AlternatingRowsDefaultCellStyle.BackColor = Color.Cyan
        End With
        Me.DataGridView1.GridColor = Color.Blue  'تلوين خطوط الداتا قريد
        DataGridView1.DataSource = DataSet1
        DataGridView1.DataMember = "Documents"
       
    End Sub

    Public Sub ChangePosition()
        Label11.RightToLeft = Windows.Forms.RightToLeft.Yes
        Label11.Text = "السجل " & Me.BindingContext(DataSet1, "Documents").Position + 1 & _
        " من " & Me.BindingContext(DataSet1, "Documents").Count
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Me.BindingContext(DataSet1, "Documents").Position = 0
        ChangePosition()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.BindingContext(DataSet1, "Documents").Position -= 1
        ChangePosition()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Me.BindingContext(DataSet1, "Documents").Position += 1
        ChangePosition()
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        Me.BindingContext(DataSet1, "Documents").Position = Me.BindingContext(DataSet1, "Documents").Count - 1
        ChangePosition()
    End Sub

    Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click
        If ComboBox2.Text = "" Or TextBox4.Text = "" Then
            MsgBox("يجب تعبئة حقل الاسم , في حال عدم توفر المعلومات حول هذا الحقل يمكنك أن تضع (-) مكان الفراغ")
            Exit Sub
        End If

        Try
            Dim SavInto As New OleDb.OleDbCommand
            Dim ConStr As String = _
                "Provider=Microsoft.ace.OLEDB.12.0;" & _
            "Data Source =" & Application.StartupPath & "\Eleves.accdb"
            Dim Conn As New OleDbConnection(ConStr)
            SavInto.Connection = Conn
            SavInto.CommandType = CommandType.Text
            Conn.Open()
            SavInto.CommandText = "SELECT Max(Num)  FROM Documents"
            Dim lastNum As Integer = 0
            Integer.TryParse(SavInto.ExecuteScalar().ToString(), lastNum)
            lastNum = lastNum + 1
            MessageBox.Show(lastNum.ToString())



            'SavInto.CommandText = "INSERT INTO Documents(Num,Document,DateDoc)" & _
            '"values ('" & TextBox3.Text & "','" & ComboBox2.Text & "','" & TextBox4.Text & "') "
            SavInto.CommandText = "INSERT INTO Documents(Num ,Document,DateDoc)" & _
            "values (" & lastNum & ",'" & ComboBox2.Text & "','" & TextBox4.Text & "')"

            SavInto.ExecuteNonQuery()

            'this code to get last Num value for the inserted record
            'SavInto.CommandText = "Select @@Identity"
            'Dim last_Num = Convert.ToInt32(SavInto.ExecuteScalar())


            Conn.Close()
            MsgBox("تمت عملية الاضافة والحفظ في قاعدة البيانات بنجاح")
        Catch ex As Exception
            MsgBox(ex.Message)
            Exit Sub
        End Try

        Dim frm As New Form1
        Me.Hide()
        frm.Show()
    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click

        If MsgBox("هل تريد حذف السجل الحالي", MsgBoxStyle.OkCancel) = MsgBoxResult.Cancel Then
            Exit Sub
        End If
        Dim SavInto As New OleDb.OleDbCommand
        Dim DataAdapter1 As New OleDbDataAdapter(SQLstr, Conn)
        SavInto.Connection = Conn
        SavInto.CommandType = CommandType.Text
        Conn.Open()
        'SavInto.CommandText = "DELETE FROM Documents WHERE Document ='" & Trim(TextBox1.Text) & "'"
        SavInto.CommandText = "DELETE FROM Documents WHERE Num=" & Trim(TextBox5.Text) & ""

        SavInto.ExecuteNonQuery()

        SavInto.CommandText = "UPDATE Documents SET Num = Num - 1 WHERE Num >" & Trim(TextBox5.Text) & ""
        SavInto.ExecuteNonQuery()

        DataSet1.Clear()
        DataAdapter1.Fill(DataSet1, "Documents")
        Conn.Close()
        MsgBox("تمت عملية حذف السجل")
        ChangePosition()
        '  Dim frm As New Form1
        '  Me.Hide()
        ' frm.Show()
    End Sub

    Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click


        Edit.TextBox1.Text = TextBox5.Text
        Edit.ComboBox1.Text = TextBox1.Text
        Edit.TextBox2.Text = TextBox2.Text

        Edit.Show()
        Me.Hide()
    End Sub



    Private Sub TextBox9_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox9.TextChanged
        Select Case ComboBox1.Text
            Case "عنوان الوثيقة"
                On Error Resume Next
                Dim MyRows() As DataRow = DataSet1.Tables("Documents").Select("Document LIKE '%" & Trim(TextBox9.Text) & "%'")
                Dim FoundRows As DataTable = DataSet1.Tables("Documents").Clone
                Dim dr As DataRow
                For Each dr In MyRows
                    FoundRows.ImportRow(dr)
                Next
                DataGridView1.DataSource = FoundRows
                TextBox1.DataBindings.Add("Text", DataSet1, "Documents.Document")
                TextBox2.DataBindings.Add("Text", DataSet1, "Documents.DateDoc")

            Case "تاريخ الوثيقة"
                On Error Resume Next
                Dim MyRows() As DataRow = DataSet1.Tables("Documents").Select("DateDoc LIKE '%" & Trim(TextBox9.Text) & "%'")
                Dim FoundRows As DataTable = DataSet1.Tables("Documents").Clone
                Dim dr As DataRow
                For Each dr In MyRows
                    FoundRows.ImportRow(dr)
                Next
                DataGridView1.DataSource = FoundRows
                TextBox1.DataBindings.Add("Text", DataSet1, "Documents.Document")
                TextBox2.DataBindings.Add("Text", DataSet1, "Documents.DateDoc")


        End Select
    End Sub



End Class