VB : VB Select ข้อมูลซ้อนกัน พร้อมกัน 2 Table

VB : VB Select ข้อมูลซ้อนกัน พร้อมกัน 2 Table

เช่นต้องการ While ข้อมูลจาก Table หนึ่ง เสร็จแล้วใน Loop While ต้องการ Select ข้อมูลจาก Table อื่นมาอีก

จะต้อง ใช้ตัวแปร Connect คนละตัวกัน เช่น

1. Public Class Reports
2.     Dim myConnection As New MySqlConnection
3.     Private myConnectionString As String
4.  
5.     Dim myConnection1 As New MySqlConnection
6.     Private myConnectionString1 As String
7.  
8.     Dim myDataset As New DataSet
9.     Dim myReader As MySqlDataReader
10.     Dim myCommand As New MySqlCommand
11.     Dim SQL As String
12.     Dim myAdapter As New MySqlDataAdapter
13.  
14.     Dim myDataset1 As New DataSet
15.     Dim myReader1 As MySqlDataReader
16.     Dim myCommand1 As New MySqlCommand
17.     Dim SQL1 As String
18.     Dim myAdapter1 As New MySqlDataAdapter
19.  
20.   Private Sub Reports_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
21.         myConnectionString = "server=xxx;" _
22.         & "user id=xx;" _
23.         & "password=xx;" _
24.         & "Allow Zero DateTime=True;" _
25.         & "charset=tis620;"
26.  
27.         myConnectionString1 = "server=xxx;" _
28.         & "user id=xx;" _
29.         & "password=xx;" _
30.         & "Allow Zero DateTime=True;" _
31.         & "charset=tis620;"
32.  
33. //Button1.Click
34.             myConnection.ConnectionString = myConnectionString
35.             myConnection.Open()
36.             myCommand.Connection = myConnection
37.  
38.             SQL = "SELECT BudgetNo, ROUND(BudgetNo, 0) AS NoSort, Department, BudgetDetail, Qty, FromLastYear, BudgetAmount, Year, BudgetUsed, PlanDate, CreateDate, Unit, UseQty, DocumentBudget FROM sci_rp.RPBudget " _
39.             & " WHERE RPBudget.Department ='" & DepartCode & "'" _
40.             & " AND RPBudget.Year = '" & DateStartYear & "'" _
41.             & " ORDER BY Department, NoSort, length(BudgetNo) ASC"
42.             '& " ORDER BY Department, length(BudgetNo), BudgetNo ASC"
43.  
44.             myCommand.CommandText = SQL
45.             myAdapter.SelectCommand = myCommand
46.             myReader = myCommand.ExecuteReader
47.  
48.             While myReader.Read()
49.  
50.                 myConnection1.ConnectionString = myConnectionString1
51.                 myConnection1.Open()
52.                 myCommand1.Connection = myConnection1
53.  
54.                 SQL1 = "SELECT RPID, Description, EndDate, Qty, Stock, In_price, Status, ApproveDate FROM sci_rp.RPLines " _
55.                 & " WHERE RPLines.BudgetNo = '" & BudgetNo & "'" _
56.                 & " AND (RPLines.Status = 'Purchased'" _
57.                 & " OR RPLines.Status = 'Approve')" _
58.                 & " AND RPLines.ApproveDate <= '" & DateSQL & "'" _
59.                 & " ORDER BY RPLineId ASC"
60.                 myCommand1.CommandText = SQL1
61.                 myAdapter1.SelectCommand = myCommand1
62.                 myReader1 = myCommand1.ExecuteReader
63.                 While myReader1.Read()
64.  
65.  
66.             End While
67.  
68.             End While