Read from two tables information-CodePudding

How can i convert this code to read information from the two tables.

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

So let me start with information about this that is in RED , i need to get this information from another table that is called "Profiles"

listItems(i).Icon = orderPicFromString

listItems(i).Icon2 = orderPicFromString2

listItems(i).OrderTitle = orderTitleString

listItems(i).OrderTitle2 = orderTitleString2

So this fields i need to read them from table "Profiles"

So next is as you see the code up calls 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

So this is main function to display the results from row("name") Then i try to create like this:

'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

What it needs to do is:

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

CodePudding user response：

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;"