كود :
Imports System.Data.OleDb
Imports Microsoft.Office.Interop
Public Class Form1
Dim PROC
Dim path As String
Dim conn2 As New OleDbConnection("PROVIDER=MICROSOFT.ACE.OLEDB.12.0;DATA SOURCE=" & My.Settings.db & ";")
Dim conn As New OleDbConnection("PROVIDER=MICROSOFT.ACE.OLEDB.12.0;DATA SOURCE=" & My.Settings.path & "; EXTENDED PROPERTIES=EXCEL 12.0;")
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Try
SkinCrafter1.LoadSkinFromFile("skins\Arcus.skf")
SkinCrafter1.ApplySkin()
If My.Settings.db = "" Then
MsgBox(" لا يوجد قاعدة بيانات متصلة ", MsgBoxStyle.MsgBoxRight & MsgBoxStyle.Information, "مدراء المدارس")
Exit Sub
End If
If IO.File.Exists(My.Settings.db) Then
If conn2.State = ConnectionState.Open Then conn2.Close()
conn2.Open()
Dim cmd As New OleDbCommand("select * from Leaders where SchName is not null ", conn2)
Dim dt As New DataTable
dt.Clear()
dt.Load(cmd.ExecuteReader)
If dt.Rows.Count > 0 Then
DataGridView2.DataSource = dt
For s As Integer = 0 To dt.Rows.Count - 1
ComboBox1.Items.Add(dt.Rows(s).Item("SchName"))
Next
Label7.Text = My.Settings.mdarsCount
Label15.Text = My.Settings.edart
Else
My.Settings.edart = ""
My.Settings.mdarsCount = ""
End If
conn2.Close()
Else
MsgBox(" مسار قاعدة البيانات خاطيء ")
Button2_Click(Nothing, Nothing)
End If
Catch ex As Exception
'MsgBox(ex.Message)
Exit Sub
End Try
End Sub
Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
If conn2.State = ConnectionState.Open Then conn2.Close()
conn2.Open()
Dim cmd As New OleDbCommand("select * from Leaders where SchName=@SchName", conn2)
cmd.Parameters.Add("SchName", OleDbType.VarChar).Value = ComboBox1.Text
Dim dt As New DataTable
dt.Clear()
dt.Load(cmd.ExecuteReader)
Dim h As Integer = ComboBox1.SelectedIndex
DataGridView2.Rows(h).Selected = True
DataGridView2.FirstDisplayedScrollingRowIndex = DataGridView2.Rows(h).Index
DataGridView2.Rows.IndexOf(DataGridView2.CurrentRow)
Label1.Text = dt.Rows(0).Item("SchWz")
Label2.Text = dt.Rows(0).Item("SchName")
Label3.Text = dt.Rows(0).Item("leaderName")
Label4.Text = dt.Rows(0).Item("mobile").ToString
Label5.Text = dt.Rows(0).Item("tel").ToString
Label14.Text = dt.Rows(0).Item("mktb")
conn2.Close()
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
conn.Close()
conn2.Close()
'conn3.Close()
Me.Close()
End Sub
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
Try
ofd.Filter = "Excel Files(*.xlsx)|*.xlsx|All files (*.*)|*.*"
If ofd.ShowDialog = Windows.Forms.DialogResult.OK Then
'My.Settings.url = ofd.FileName
If ofd.SafeFileName = "LI_HierarchySchoolAdmins.xlsx" Then
Dim conn3 As New OleDbConnection("PROVIDER=MICROSOFT.ACE.OLEDB.12.0;DATA SOURCE=" & ofd.FileName & "; EXTENDED PROPERTIES=EXCEL 12.0;")
If conn3.State = ConnectionState.Open Then conn3.Close()
conn3.Open()
Label16.Text = "فضلا انتظر .."
Dim cmd As New OleDbCommand("select * from [LI_HierarchySchoolAdmins$] where f14 is not null", conn3)
Dim dt As New DataTable
dt.Clear()
dt.Load(cmd.ExecuteReader)
conn3.Close()
For f As Integer = 1 To dt.Rows.Count - 1
If dt.Rows(f).Item("f15").ToString = "" Then
dt.Rows(f).Item("f15") = dt.Rows(f - 1).Item("f15").ToString
Else
Dim ms As String = dt.Rows(f).Item("f15").ToString
End If
Next
DataGridView1.DataSource = dt
DataGridView1.Update()
For s As Integer = 1 To dt.Rows.Count - 1
ComboBox1.Items.Add(dt.Rows(s).Item("f13"))
Next
'===========نسخ قاعدة البيانات الجديدة الى القاعدة في المسار الصحيح ======
Dim cmd3 As New OleDbCommand("DELETE * from Leaders", conn2)
If conn2.State = ConnectionState.Open Then conn2.Close()
conn2.Open()
cmd3.ExecuteNonQuery()
conn2.Close()
Timer1.Enabled = True
Dim cont As Integer = dt.Rows.Count - 1
ProgressBar1.Maximum = cont
ProgressBar1.Minimum = 0
ProgressBar1.Value = 0
If conn2.State = ConnectionState.Open Then conn2.Close()
conn2.Open()
For i = 1 To dt.Rows.Count - 1
Dim cmd2 As New OleDbCommand("INSERT INTO Leaders(SchWz,SchName,leaderName,mobile,tel,mktb) values(@SchWz,@SchName,@leaderName,@mobile,@tel,@mktb)", conn2)
cmd2.Parameters.AddWithValue("@SchWz", DataGridView1.Rows(i).Cells("f14").Value)
cmd2.Parameters.AddWithValue("@SchName", DataGridView1.Rows(i).Cells("f13").Value)
cmd2.Parameters.AddWithValue("@leaderName", DataGridView1.Rows(i).Cells("f5").Value)
cmd2.Parameters.AddWithValue("@mobile", DataGridView1.Rows(i).Cells("f4").Value)
cmd2.Parameters.AddWithValue("@tel", DataGridView1.Rows(i).Cells("f3").Value)
cmd2.Parameters.AddWithValue("@mktb", DataGridView1.Rows(i).Cells("f15").Value)
cmd2.ExecuteNonQuery()
PROC = Int((i / cont) * 100)
ProgressBar1.Value = i
ProgressBar1.Value = i - 1
Label16.Text = PROC & " %"
Next
conn2.Close()
My.Settings.edart = dt.Rows(1).Item("f18").ToString
My.Settings.mdarsCount = dt.Rows.Count - 1
Label15.Text = My.Settings.edart
Label7.Text = My.Settings.mdarsCount
'--------------------------------
Dim cmd5 As New OleDbCommand("select * from Leaders where SchName is not null ", conn2)
If conn2.State = ConnectionState.Open Then conn2.Close()
conn2.Open()
Dim dt5 As New DataTable
dt5.Clear()
dt5.Load(cmd5.ExecuteReader)
DataGridView2.DataSource = dt5
DataGridView2.Refresh()
For s As Integer = 0 To dt5.Rows.Count - 1
ComboBox1.Items.Add(dt5.Rows(s).Item("SchName"))
Next
Label7.Text = My.Settings.mdarsCount
Label15.Text = My.Settings.edart
conn2.Close()
'----------------------------------
MsgBox("تم تحديث البيانات بنجاح", MsgBoxStyle.OkOnly & MsgBoxStyle.Information, "تنبيه")
Label16.Text = ""
'=================
Else
MsgBox(" قاعدة البيانات غير متطابقة ", MsgBoxStyle.OkOnly & MsgBoxStyle.Information, "مدراء المدارس")
End If
End If
Catch ex As Exception
MsgBox(ex.Message)
'Exit Sub
Finally
conn2.Close()
End Try
End Sub
Private Sub Form1_FormClosed(sender As Object, e As FormClosedEventArgs) Handles Me.FormClosed
conn.Close()
End Sub
Private Sub DataGridView2_CellMouseClick(sender As Object, e As DataGridViewCellMouseEventArgs) Handles DataGridView2.CellMouseClick
Dim i As Integer = DataGridView2.Rows.IndexOf(DataGridView2.CurrentRow)
Label1.Text = DataGridView2.Rows(i).Cells("SchWz").Value.ToString
Label2.Text = DataGridView2.Rows(i).Cells("SchName").Value.ToString
Label3.Text = DataGridView2.Rows(i).Cells("leaderName").Value.ToString
Label4.Text = DataGridView2.Rows(i).Cells("mobile").Value.ToString
Label5.Text = DataGridView2.Rows(i).Cells("tel").Value.ToString
Label14.Text = DataGridView2.Rows(i).Cells("mktb").Value.ToString
End Sub
Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
If ProgressBar1.Maximum Then
Timer1.Enabled = False
ProgressBar1.Value = 0
End If
End Sub
End Class