[السيد الجوهري]

كيف احول هذا الكود من ربط  ب اكسس الي sql

كود :

Imports System.Data.OleDb

Public Class Form3
   Dim sql As String
   Dim Conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data source=|DataDirectory|\Treedb.accdb;")
   Dim ds As New DataSet
   Dim dt As New DataTable
   Dim da As New OleDbDataAdapter
   Dim dv As New DataView
   Dim cmd As New OleDbCommand
   Dim nodeAuthor As TreeNode
   Dim nodeTitle As TreeNode
   Dim isupdate As Boolean
   Private Sub CreateTree(ByVal TV As TreeView)
       sql = "SELECT RootID, RootName, ParentID, RootLevel, RootID & ' : ' & RootName as MyRoot FROM tblRoots Order By RootID"
       ds = New DataSet
       dt = New DataTable
       da = New OleDbDataAdapter(sql, Conn)
       da.Fill(ds, "tblRoots")
       dv = New DataView(ds.Tables("tblRoots"))
       dt = dv.ToTable

       Dim MaxLevel1 As Integer = CInt(dt.Compute("MAX(RootLevel)", ""))

       Dim i, j As Integer

       For i = 0 To MaxLevel1
           Dim Rows1() As DataRow = dt.Select("RootLevel = " & i)
           For j = 0 To Rows1.Count - 1
               Dim ID1 As String = Rows1(j).Item("RootID").ToString
               Dim Name1 As String = Rows1(j).Item("RootName").ToString
               Dim Parent1 As String = Rows1(j).Item("ParentID").ToString
               Dim FullID As String = Rows1(j).Item("MyRoot").ToString

               If Parent1 = "-1" Then
                   TV.Nodes.Add(ID1, Name1)
               Else
                   Dim TreeNodes1() As TreeNode = TV.Nodes.Find(Parent1, True)
                   If TreeNodes1.Length > 0 Then
                       TreeNodes1(0).Nodes.Add(ID1, FullID)
                   End If
               End If
           Next
       Next
   End Sub

   Private Sub Button2_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button2.Click
       TV.Nodes.Clear()
       CreateTree(TV)
       TV.ExpandAll()
       TV.SelectedNode = TV.Nodes(0)
       For Each ctrl As Control In Me.GroupBox1.Controls
           If TypeOf ctrl Is TextBox Then
               ctrl.Text = ""
           End If
       Next
       isupdate = True
   End Sub

   Private Sub جديدToolStripMenuItem_Click(ByVal sender As Object, ByVal e As EventArgs) Handles جديدToolStripMenuItem.Click, btnNew.Click
       Dim str As String = ""
       Dim NameStr As String = ""
       If TV.Nodes.Count > 0 Then
           If TV.SelectedNode.Text = "دليل الحسابات" Then
               txtParentAcc.Text = "0"
               txtParentName.Text = "دليل الحسابات"
               Exit Sub
           End If
           For i As Integer = 0 To TV.SelectedNode.Text.Length - 1
               If TV.SelectedNode.Text.ToCharArray(i, 1) = " " Then
                   If TV.SelectedNode.Text.ToCharArray(i + 1, 1) = ":" Then
                       Exit For
                   End If
               End If
               str += TV.SelectedNode.Text.ToCharArray(i, 1)
               NameStr = TV.SelectedNode.Text.ToString.Substring(str.Length + 2, TV.SelectedNode.Text.ToString.Length - str.Length - 2)
           Next
           txtAccID.Text = ""
           txtParentAcc.Text = str
           txtParentName.Text = NameStr
           txtAccName.Text = ""
           sql = "SELECT Top 1 RootID, RootName, ParentID, RootLevel FROM tblRoots WHERE ParentID=" & CInt(txtParentAcc.Text) & " Order By RootID Desc"
           ds = New DataSet
           dt = New DataTable
           da = New OleDbDataAdapter(sql, Conn)
           da.Fill(ds, "tblRoots")
           dv = New DataView(ds.Tables("tblRoots"))
           dt = dv.ToTable
           If dt.Rows.Count > 0 Then
               txtAccID.Text = dt.Rows(0)(0) + 1
               txtLevel.Text = dt.Rows(0)(3)
           Else
               txtAccID.Text = txtParentAcc.Text & "1"
               sql = "SELECT Top 1 RootID, RootName, ParentID, RootLevel FROM tblRoots WHERE RootID=" & CInt(txtParentAcc.Text) & " Order By RootID Desc"
               ds = New DataSet
               dt = New DataTable
               da = New OleDbDataAdapter(sql, Conn)
               da.Fill(ds, "tblRoots")
               dv = New DataView(ds.Tables("tblRoots"))
               dt = dv.ToTable
               txtLevel.Text = dt.Rows(0)(3)
           End If
       End If
       isupdate = False
   End Sub

   Private Sub TV_NodeMouseClick(ByVal sender As Object, ByVal e As TreeNodeMouseClickEventArgs) Handles TV.NodeMouseClick
       If e.Button = Windows.Forms.MouseButtons.Right Then
           TV.SelectedNode = e.Node
       End If
   End Sub


   Private Sub TV_AfterSelect(ByVal sender As Object, ByVal e As TreeViewEventArgs) Handles TV.AfterSelect
       If TV.Focused Then
           Dim str As String = ""
           Dim NameStr As String = ""
           If TV.SelectedNode.Text = "دليل الحسابات" Then
               Exit Sub
           End If
           For i As Integer = 0 To TV.SelectedNode.Text.Length - 1
               If TV.SelectedNode.Text.ToCharArray(i, 1) = " " Then
                   If TV.SelectedNode.Text.ToCharArray(i + 1, 1) = ":" Then
                       Exit For
                   End If
               End If
               str += TV.SelectedNode.Text.ToCharArray(i, 1)
               NameStr = TV.SelectedNode.Text.ToString.Substring(str.Length + 2, TV.SelectedNode.Text.ToString.Length - str.Length - 2)
           Next
           txtAccID.Text = str
           txtAccName.Text = NameStr
       End If
   End Sub


   Private Sub تعديلToolStripMenuItem_Click(ByVal sender As Object, ByVal e As EventArgs) Handles تعديلToolStripMenuItem.Click, bntUpdate.Click
       If txtAccID.Text = "" Then
           Exit Sub
       End If
       If isupdate = True Then
           cmd = New OleDbCommand("UPDATE tblRoots SET  RootName=@RootName WHERE RootId=" & CInt(txtAccID.Text), Conn)
           With cmd.Parameters
               .AddWithValue("@RootName", txtAccName.Text)
               .AddWithValue("@ParentID", txtParentAcc.Text)
               .AddWithValue("@RootLevel", txtLevel.Text)
           End With
           Conn.Open()
           cmd.ExecuteNonQuery()
           Conn.Close()
           Button2_Click(Nothing, Nothing)
       Else
           cmd = New OleDbCommand("INSERT INTO tblRoots VALUES(@RootID, @RootName, @ParentID, @RootLevel)", Conn)
           With cmd.Parameters
               .AddWithValue("@RootID", txtAccID.Text)
               .AddWithValue("@RootName", txtAccName.Text)
               .AddWithValue("@ParentID", txtParentAcc.Text)
               .AddWithValue("@RootLevel", txtLevel.Text)
           End With
           Conn.Open()
           cmd.ExecuteNonQuery()
           Conn.Close()
           Button2_Click(Nothing, Nothing)
       End If

   End Sub

   Private Sub حذفToolStripMenuItem_Click(ByVal sender As Object, ByVal e As EventArgs) Handles حذفToolStripMenuItem.Click, btnDelete.Click
       If txtAccID.Text = "" Then
           Exit Sub
       End If
       If isupdate = True Then
           If TV.SelectedNode.Text = "دليل الحسابات" Then
               Exit Sub
           End If
           If MsgBox("هل تريد حذف الحساب؟", MsgBoxStyle.YesNo, "دليل الحسابات") = MsgBoxResult.Yes Then
               cmd = New OleDbCommand("DELETE FROM tblRoots WHERE ParentID=" & CInt(txtAccID.Text), Conn)
               Conn.Open()
               cmd.ExecuteNonQuery()
               Conn.Close()


               cmd = New OleDbCommand("DELETE FROM tblRoots WHERE RootId=" & CInt(txtAccID.Text), Conn)
               Conn.Open()
               cmd.ExecuteNonQuery()
               Conn.Close()
               Button2_Click(Nothing, Nothing)
           End If
       End If

   End Sub

   Private Sub الغاءToolStripMenuItem_Click(ByVal sender As Object, ByVal e As EventArgs) Handles الغاءToolStripMenuItem.Click, btnCancel.Click
       Button2_Click(Nothing, Nothing)
   End Sub

   Private Sub خروجToolStripMenuItem_Click(ByVal sender As Object, ByVal e As EventArgs) Handles خروجToolStripMenuItem.Click, btnClose.Click
       If MsgBox("هل تريد الخروج", MsgBoxStyle.YesNo, "دليل الحسابات") = MsgBoxResult.Yes Then
           End
       End If
   End Sub

   Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
       Button2_Click(Nothing, Nothing)
       



   End Sub


End Class

[جميل علي]

ضع موضوعك في قسم
أسئلة vb.net