Click here to Skip to main content
15,311,151 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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

To read from table OrdersAssigned and to check this account names inside the table Profiles and get result for

listItems(i).Icon = orderPicFromString
listItems(i).Icon2 = orderPicFromString2
listItems(i).OrderTitle = orderTitleString
listItems(i).OrderTitle2 = orderTitleString2

So from OrdersAssigned will take account names:
orderfrom
orderreceiver
and will get information from this account from table Profiles and return from there results
userPosition
userPicture


What I have tried:

I had added my code how i do it but its not showing correct it display same UserPosition to all users and same picture to all users, everything else that is readed from OrdersAssigned is correct display
Posted
Updated 20-Dec-21 11:45am

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;"
   
You need a JOIN for each table: SQL Joins[^]
   
Comments
diablo22 20-Dec-21 12:35pm
   
need more information than than to manage it, this will not solve fetch information between userPosition and userPicture to display in GenerateDynamicUserControl()

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900