我如何轉換此代碼以從兩個表中讀取資訊。
Private Sub GenerateDynamicUserControl()
FlowLayoutPanel1.Controls.Clear()
Dim dt As DataTable = New ClassBLL().GetItems()
If dt IsNot Nothing Then
If dt.Rows.Count > 0 Then
Dim listItems As ListItem() = New ListItem(dt.Rows.Count - 1) {}
For i As Integer = 0 To 1 - 1
For Each row As DataRow In dt.Rows
Dim listItem As New ListItem()
listItems(i) = listItem
'Dim ms As New MemoryStream(CType(row("userPic"), Byte()))
listItems(i).Width = FlowLayoutPanel1.Width - 30
listItems(i).Icon = orderPicFromString
listItems(i).Icon2 = orderPicFromString2
listItems(i).OrderFrom = row("orderfrom").ToString()
listItems(i).OrderTitle = orderTitleString
listItems(i).OrderReceiver = row("orderreceiver").ToString()
listItems(i).OrderTitle2 = orderTitleString2
'listItems(i).ButtonBackground = orderButtonBackString
listItems(i).ButtonText = row("orderstatus").ToString()
listItems(i).OrderDate = row("orderdate")
listItems(i).IDOrder = row("orderid").ToString()
If listItems(i).ButtonText = "Accepted" Then
listItems(i).ButtonBackground = Color.FromArgb(26, 168, 92)
ElseIf listItems(i).ButtonText = "Declined" Then
listItems(i).ButtonBackground = Color.FromArgb(246, 50, 90)
ElseIf listItems(i).ButtonText = "Proceed" Then
listItems(i).ButtonBackground = Color.FromArgb(255, 174, 33)
ElseIf listItems(i).ButtonText = "Waiting" Then
listItems(i).ButtonBackground = Color.FromArgb(53, 121, 255)
Else
listItems(i).ButtonBackground = Color.FromArgb(91, 146, 255)
End If
FlowLayoutPanel1.Controls.Add(listItems(i))
Next
Next
End If
End If
End Sub
因此，讓我從 RED 中的有關此資訊開始，我需要從另一個名為“組態檔”的表中獲取此資訊
listItems(i).Icon = orderPicFromString
listItems(i).Icon2 = orderPicFromString2
listItems(i).OrderTitle = orderTitleString
listItems(i).OrderTitle2 = orderTitleString2
所以這個欄位我需要從表“組態檔”中讀取它們
所以接下來是你看到的呼叫 Class GetItems 的代碼：
Public Function GetItems() As DataTable
Try
Dim objdal As New ClassDAL()
Return objdal.ReadItemsTable()
Catch e As Exception
Dim result As DialogResult = MessageBox.Show(e.Message.ToString())
Return Nothing
End Try
End Function
Public Function ReadItemsTable() As DataTable
Using cons As New OleDbConnection(ServerStatus)
Using cmd As New OleDbCommand()
cmd.Connection = cons
cmd.CommandText = "SELECT * FROM OrdersAssigned ORDER BY ID ASC"
cons.Open()
Using sda As New OleDbDataAdapter(cmd)
Dim dt As New DataTable()
sda.Fill(dt)
Return dt
End Using
End Using
End Using
End Function
所以這是顯示 row("name") 結果的主要功能然后我嘗試像這樣創建：
'Declare Strings for OrderDisplay
Public orderFromString As String
Public orderTitleString As String
Public orderReceiveString As String
Public orderTitleString2 As String
Public orderButtonBackString As Color
Public orderButtonTextString As String
Public orderDateString As Date
Public orderIDString As String
Public orderPicFromString As Image
Public orderPicFromString2 As Image
'Get Accounts Name
Public orderAccountFrom As String
Public orderAccountTo As String
Public Sub GetUserPictureFrom()
Using conn As New OleDbConnection(ServerStatus)
conn.Open()
Dim sql As String = "Select userPicture From Profiles where userAccount=@GetLogin"
Using cmd As New OleDbCommand(sql, conn)
cmd.Parameters.AddWithValue("@GetLogin", orderAccountFrom)
Dim imageData As Byte() = DirectCast(cmd.ExecuteScalar(), Byte())
If imageData IsNot Nothing Then
Using stream As New MemoryStream(imageData)
Dim backgroundImage As Image = Image.FromStream(stream)
orderPicFromString = backgroundImage
End Using
End If
End Using
End Using
End Sub
Public Sub GetUserPictureTo()
Using conn As New OleDbConnection(ServerStatus)
conn.Open()
Dim sql As String = "Select userPicture From Profiles where userAccount=@GetLogin"
Using cmd As New OleDbCommand(sql, conn)
cmd.Parameters.AddWithValue("@GetLogin", orderAccountTo)
Dim imageData As Byte() = DirectCast(cmd.ExecuteScalar(), Byte())
If imageData IsNot Nothing Then
Using stream As New MemoryStream(imageData)
Dim backgroundImage As Image = Image.FromStream(stream)
orderPicFromString2 = backgroundImage
End Using
End If
End Using
End Using
End Sub
Public Sub GetOrdersDisplay()
Using cons As New OleDbConnection(ServerStatus)
Using cmd As New OleDbCommand()
cmd.Connection = cons
cmd.CommandText = "SELECT * FROM OrdersAssigned ORDER BY ID ASC"
cons.Open()
Using rdr As OleDbDataReader = cmd.ExecuteReader()
While rdr.Read()
orderAccountFrom = rdr("orderacc").ToString
orderAccountTo = rdr("orderreceiveracc").ToString
End While
End Using
cmd.CommandText = "Select userPosition From Profiles where userAccount = @GetUser"
cmd.Parameters.Clear()
cmd.Parameters.AddWithValue("@GetUser", orderAccountFrom)
Using rds As OleDbDataReader = cmd.ExecuteReader()
While rds.Read()
orderTitleString = rds("userPosition").ToString
End While
End Using
cmd.CommandText = "Select userPosition From Profiles where userAccount = @ToUser"
cmd.Parameters.Clear()
cmd.Parameters.AddWithValue("@ToUser", orderAccountTo).ToString()
Using rdx As OleDbDataReader = cmd.ExecuteReader()
While rdx.Read()
orderTitleString2 = rdx("userPosition").ToString
End While
End Using
End Using
End Using
GetUserPictureFrom()
GetUserPictureTo()
End Sub
它需要做的是：
orderAccountFrom = rdr("orderacc").ToString
orderAccountTo = rdr("orderreceiveracc").ToString
will get the accounts from table OrdersAssigned and search with them in table Profiles to get results userPosition What means this
userx in Profiles has userPosition = Boss usery in Profiles has userPosition = Worker i want to fetch this information. The same goes for the pictures each user has his own picture.
How can i manage to fix this issue so to get correct data? How can i combine all of this inside the function ReadItemsTable() it would be better to be in one place everything so
listItems(i).Icon = orderPicFromString
listItems(i).Icon2 = orderPicFromString2
listItems(i).OrderTitle = orderTitleString
listItems(i).OrderTitle2 = orderTitleString2
to work correct to get information
uj5u.com熱心網友回復：
cmd.CommandText = "SELECT OrdersAssigned.*,
ProfilesFrom.userPosition AS UserPositionFrom,
ProfilesFrom.userPicture AS UserPictureFrom,
ProfilesTo.userPosition AS UserPositionTo,
ProfilesTo.userPicture AS UserPictureTo
FROM
(OrdersAssigned
LEFT OUTER JOIN Profiles AS ProfilesFrom ON OrdersAssigned.orderacc = ProfilesFrom.userAccount)
LEFT OUTER JOIN Profiles AS ProfilesTo ON OrdersAssigned.orderreceiveracc = ProfilesTo.userAccount
ORDER BY
OrdersAssigned.ID ASC;"
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/390813.html
上一篇：了解Numba性能差異