06-05-18, 01:50 AM
كود :
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
'-- DISTINCT جلب السجلات من الجدول رقم 1 بدون تكرار وذلك بإستخدام العبارة ---------------------------------------------------------------------------------------
' matricule فقط جلب الحقل
' النتيجة
' 0001
' 0002
' 0003
Dim matricules As New DataTable
da = New OleDbDataAdapter("SELECT DISTINCT [matricule] FROM Table1", conn)
da.Fill(matricules) ' matricules تعبئة الجدول
' matricule يحتوي على ثلاث سجلات ، كل سجل يحتوي على عامود واحد فقط وهو matricules الآن الجدول
'-----------------------------------------------------------------------------------------
conn.Open() ' لأنه يحتاج إلى فتح الإتصال Me.Insert_New_Row_In_Table2 فتح الإتصال قبل الدول للحلقة لإستخدام الأمر
Dim TempTable As New DataTable
For Each r As DataRow In matricules.Rows
Dim SqlQry As String = "SELECT TOP 1 [matricule],[nom],[prenom],(SELECT [note] From Table1 WHERE [matricule] = '" & r("matricule") & "' AND [mat] = 'S1M01') AS note_mat_A,(SELECT [note] From Table1 WHERE [matricule] = '" & r("matricule") & "' AND [mat] = 'S1M02') AS note_mat_B,(SELECT [note] From Table1 WHERE [matricule] = '" & r("matricule") & "' AND [mat] = 'S1M03') AS note_mat_C From Table1 WHERE [matricule] = '" & r("matricule") & "'"
da = New OleDbDataAdapter(SqlQry, conn)
TempTable.Clear()
da.Fill(TempTable)
If TempTable.Rows.Count > 0 Then
With TempTable.Rows(0)
Me.Insert_New_Row_In_Table2(.Item("matricule"),
.Item("nom"),
.Item("prenom"),
.Item("note_mat_A"),
.Item("note_mat_B"),
.Item("note_mat_C"))
End With
End If
Next
conn.Close() ' إغلاق الأتصال بعد الإنتهاء
Dim Table2 As New DataTable
da = New OleDbDataAdapter("SELECT [matricule], [nom], [prenom], [note_mat_A], [note_mat_B], [note_mat_C] FROM Table2;", conn)
da.Fill(Table2)
DataGridView2.DataSource = Table2
End Sub
Private Sub Insert_New_Row_In_Table2(matricule As String,
nom As String,
prenom As String,
note_mat_A As String,
note_mat_B As String,
note_mat_C As String)
Try
Dim cmd As New OleDbCommand() With {.Connection = conn}
'- DELETE ---------------------------------------------------- ' حذف السجل إذا كان موجود مسبقاً
Dim sqlQry_DELETE As String
sqlQry_DELETE = "DELETE [N°], [matricule], [nom], [prenom], [note_mat_A], [note_mat_B], [note_mat_C] FROM Table2 WHERE [matricule] = '" & matricule & "';"
cmd.CommandText = sqlQry_DELETE
cmd.ExecuteNonQuery()
'- INSERT ---------------------------------------------------- إدراج السجل الجديد من الجدول رقم 1
Dim sqlQry_INSERT As String
sqlQry_INSERT = "INSERT INTO Table2 ( [matricule], [nom], [prenom], [note_mat_A], [note_mat_B], [note_mat_C]) VALUES ('" & matricule & "', '" & nom & "', '" & prenom & "', '" & note_mat_A & "', '" & note_mat_B & "', '" & note_mat_C & "')"
cmd.CommandText = sqlQry_INSERT
cmd.ExecuteNonQuery()
'-------------------------------------------------------------
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Subعند الضغط على الزر تظهر رسالة الخطأ التالية
