เช่นต้องการ While ข้อมูลจาก Table หนึ่ง เสร็จแล้วใน Loop While ต้องการ Select ข้อมูลจาก Table อื่นมาอีก
จะต้อง ใช้ตัวแปร Connect คนละตัวกัน เช่น
- Public Class Reports
- Dim myConnection As New MySqlConnection
- Private myConnectionString As String
- Dim myConnection1 As New MySqlConnection
- Private myConnectionString1 As String
- Dim myDataset As New DataSet
- Dim myReader As MySqlDataReader
- Dim myCommand As New MySqlCommand
- Dim SQL As String
- Dim myAdapter As New MySqlDataAdapter
- Dim myDataset1 As New DataSet
- Dim myReader1 As MySqlDataReader
- Dim myCommand1 As New MySqlCommand
- Dim SQL1 As String
- Dim myAdapter1 As New MySqlDataAdapter
- Private Sub Reports_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
- myConnectionString = "server=xxx;" _
- & "user id=xx;" _
- & "password=xx;" _
- & "Allow Zero DateTime=True;" _
- & "charset=tis620;"
- myConnectionString1 = "server=xxx;" _
- & "user id=xx;" _
- & "password=xx;" _
- & "Allow Zero DateTime=True;" _
- & "charset=tis620;"
- //Button1.Click
- myConnection.ConnectionString = myConnectionString
- myConnection.Open()
- myCommand.Connection = myConnection
- SQL = "SELECT BudgetNo, ROUND(BudgetNo, 0) AS NoSort, Department, BudgetDetail, Qty, FromLastYear, BudgetAmount, Year, BudgetUsed, PlanDate, CreateDate, Unit, UseQty, DocumentBudget FROM sci_rp.RPBudget " _
- & " WHERE RPBudget.Department ='" & DepartCode & "'" _
- & " AND RPBudget.Year = '" & DateStartYear & "'" _
- & " ORDER BY Department, NoSort, length(BudgetNo) ASC"
- '& " ORDER BY Department, length(BudgetNo), BudgetNo ASC"
- myCommand.CommandText = SQL
- myAdapter.SelectCommand = myCommand
- myReader = myCommand.ExecuteReader
- While myReader.Read()
- myConnection1.ConnectionString = myConnectionString1
- myConnection1.Open()
- myCommand1.Connection = myConnection1
- SQL1 = "SELECT RPID, Description, EndDate, Qty, Stock, In_price, Status, ApproveDate FROM sci_rp.RPLines " _
- & " WHERE RPLines.BudgetNo = '" & BudgetNo & "'" _
- & " AND (RPLines.Status = 'Purchased'" _
- & " OR RPLines.Status = 'Approve')" _
- & " AND RPLines.ApproveDate <= '" & DateSQL & "'" _
- & " ORDER BY RPLineId ASC"
- myCommand1.CommandText = SQL1
- myAdapter1.SelectCommand = myCommand1
- myReader1 = myCommand1.ExecuteReader
- While myReader1.Read()
- End While
- End While
No comments:
Post a Comment