locked
Displaying ID column instead of Name Column RRS feed

  • Question

  • Hi,

     

    Here is my setup:

     

    6 Tables (Customer, Employee, Order, Region, Product, Order Detail)

    Customer has two columns (CustomerID, CustomerName)

    Employee has three columns (EmployeeID, EmployeeName, CustomerID)

    Order has three columns (OrderID, OrderName, EmployeeID)

    Region has two columns (RegionID, RegionName)

    Product has two colums (ProductID, ProductName)

    Order detail has six columns (OrderDetailID, OrderID, RegionID, ProductID, QTY, Date Shipped)

     

    Customer and Employee are connected via CustomerID

    Employee and Order are connected via EmployeeID

    Order and Order Detail are connected via OrderID

    Order Detail are connected to both Product and Region via ProductID and RegionID respectively

     

    I've placed 3 comboboxes and 1 datagridview on a form.

    I've setup the dataset for all the tables.

    I dropped customer, employee and order onto the 3 comboboxes respectively, and the Order detail onto the Datagrid view.

     

    Now here is my issue, the 3 comboboxes display the name fields correctly, so I as I go through selecting an item from customer combobox the proper Employee pops-up in the second combobox, as does the order in the 3 combobox, now as data is displayed in the dataview, under the RegionID and Product ID columns, instead of giving me the corresponding RegionName and ProductName, the actually RegionID and ProductID are showing.

     

    How do I fix this?

     

    Thanks

     

    Peter

     

    Friday, October 14, 2011 4:01 PM

Answers

  • Hi Peter,

    Maybe you could create view from your OrderDetails table since each tables is related to each other. Then select a column that you would want to view in a dgv such as RegionName and ProductName.

    So I have here a sample code on how to implement your requirements based on the given table. I hope that it will be helpful to you and serve as guide:

    Imports System.Data.SqlClient
    Public Class Form1
        Private _class As New ClassConnectionString()
    
        Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            populatecbxCustomer()
            populatecbxEmployee()
            populatecbxOrder()
        End Sub
    
        'Fires up Employees, Orders, and OrderDetails value based on the selected Item of cbxCustomer 
        Private Sub cbxCustomer_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cbxCustomer.SelectedIndexChanged
            populateEmplAndOrder()
            populateOrderDetailsToDgv()
        End Sub
    
        'populates the value of cbxEmployee and cbxOrder based on the value of cbxCustomer 
        Private Sub populateEmplAndOrder()
            _class.connectionString()
            Dim str As String = ("Select EmployeeName, OrderName " +
                                "From Cust_Empl_Order_View Where CustomerName = '" + cbxCustomer.Text + "';")
            Dim cmd As New SqlCommand(str, _class.conConnect)
            Dim da As New SqlDataAdapter()
            da.SelectCommand = cmd
            Dim ds As New DataSet("dsCustomer")
            da.Fill(ds)
    
            _class.conConnect.Open()
    
            For Each row As DataRow In ds.Tables(0).Rows
                cbxEmployee.Text = row("EmployeeName").ToString()
                cbxOrder.Text = row("OrderName").ToString()
            Next
        End Sub
    
        'populates order details view to datagridiview based on the value of order name
        Private Sub populateOrderDetailsToDgv()
            _class.connectionString()
            Dim ADAP As New SqlDataAdapter("Select RegionName, ProductName From OrderDetailsView " +
                                            "Where CustomerName = '" + cbxCustomer.Text + "' ", _class.conConnect)
            Dim DS As New DataSet()
            dsClear()
            ADAP.Fill(DS, "OrderDetailsView")
            dgvOrderDetail.DataSource = DS.Tables("OrderDetailsView")
        End Sub
        Private Sub dsClear()
            Dim ds2 As New DataSet()
            dgvOrderDetail.DataSource = ds2
            ds2.Clear()
        End Sub
    
        'populates combobox customer
        Private Sub populatecbxCustomer()
            _class.connectionString()
            Try
                Dim sqlCust As String = "Select CustomerId, CustomerName From Customers;"
                Dim dAdapter As New SqlDataAdapter(sqlCust, _class.conConnect)
                Dim dt As New DataTable()
                dAdapter.Fill(dt)
                cbxCustomer.DisplayMember = "CustomerName"
                cbxCustomer.ValueMember = "CustomerId"
                cbxCustomer.DataSource = dt
            Catch
            End Try
        End Sub
    
        'populates combobox employee
        Private Sub populatecbxEmployee()
            _class.connectionString()
            Try
                Dim sqlEmpl As String = "Select EmployeeId, EmployeeName From Employees;"
                Dim dAdapter As New SqlDataAdapter(sqlEmpl, _class.conConnect)
                Dim dt As New DataTable()
                dAdapter.Fill(dt)
                cbxEmployee.DisplayMember = "EmployeeName"
                cbxEmployee.ValueMember = "EmployeeId"
                cbxEmployee.DataSource = dt
            Catch
            End Try
        End Sub
    
        'populates combobox order
        Private Sub populatecbxOrder()
            _class.connectionString()
            Try
                Dim sqlOrder As String = "Select OrderId, OrderName From Orders;"
                Dim dAdapter As New SqlDataAdapter(sqlOrder, _class.conConnect)
                Dim dt As New DataTable()
                dAdapter.Fill(dt)
                cbxOrder.DisplayMember = "OrderName"
                cbxOrder.ValueMember = "OrderId"
                cbxOrder.DataSource = dt
            Catch
            End Try
        End Sub
    End Class
    
    'Sample Connection string
    Class ClassConnectionString
        Public conConnect As SqlConnection
    
        Public Sub connectionString()
            conConnect = New SqlConnection("Data Source = (local); Database = 'SampleOrder'; Integrated Security = true")
        End Sub
    End Class
    
    

     

    Best Regards.

    Hardz


    Saturday, October 15, 2011 4:07 AM
  • Order detail has six columns (OrderDetailID, OrderID, RegionID, ProductID, QTY, Date Shipped)

    I dropped .........  Order detail onto the Datagrid view.
     

    Why does the RegionName and ProductName not display in the OrderDetails Table properly in a datagridview

    Possibly because the RegionName and ProductName aren't columns in the OrderDetails table?

     

    Monday, October 17, 2011 12:30 AM

All replies

  • Hi Peter,

    Maybe you could create view from your OrderDetails table since each tables is related to each other. Then select a column that you would want to view in a dgv such as RegionName and ProductName.

    So I have here a sample code on how to implement your requirements based on the given table. I hope that it will be helpful to you and serve as guide:

    Imports System.Data.SqlClient
    Public Class Form1
        Private _class As New ClassConnectionString()
    
        Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            populatecbxCustomer()
            populatecbxEmployee()
            populatecbxOrder()
        End Sub
    
        'Fires up Employees, Orders, and OrderDetails value based on the selected Item of cbxCustomer 
        Private Sub cbxCustomer_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cbxCustomer.SelectedIndexChanged
            populateEmplAndOrder()
            populateOrderDetailsToDgv()
        End Sub
    
        'populates the value of cbxEmployee and cbxOrder based on the value of cbxCustomer 
        Private Sub populateEmplAndOrder()
            _class.connectionString()
            Dim str As String = ("Select EmployeeName, OrderName " +
                                "From Cust_Empl_Order_View Where CustomerName = '" + cbxCustomer.Text + "';")
            Dim cmd As New SqlCommand(str, _class.conConnect)
            Dim da As New SqlDataAdapter()
            da.SelectCommand = cmd
            Dim ds As New DataSet("dsCustomer")
            da.Fill(ds)
    
            _class.conConnect.Open()
    
            For Each row As DataRow In ds.Tables(0).Rows
                cbxEmployee.Text = row("EmployeeName").ToString()
                cbxOrder.Text = row("OrderName").ToString()
            Next
        End Sub
    
        'populates order details view to datagridiview based on the value of order name
        Private Sub populateOrderDetailsToDgv()
            _class.connectionString()
            Dim ADAP As New SqlDataAdapter("Select RegionName, ProductName From OrderDetailsView " +
                                            "Where CustomerName = '" + cbxCustomer.Text + "' ", _class.conConnect)
            Dim DS As New DataSet()
            dsClear()
            ADAP.Fill(DS, "OrderDetailsView")
            dgvOrderDetail.DataSource = DS.Tables("OrderDetailsView")
        End Sub
        Private Sub dsClear()
            Dim ds2 As New DataSet()
            dgvOrderDetail.DataSource = ds2
            ds2.Clear()
        End Sub
    
        'populates combobox customer
        Private Sub populatecbxCustomer()
            _class.connectionString()
            Try
                Dim sqlCust As String = "Select CustomerId, CustomerName From Customers;"
                Dim dAdapter As New SqlDataAdapter(sqlCust, _class.conConnect)
                Dim dt As New DataTable()
                dAdapter.Fill(dt)
                cbxCustomer.DisplayMember = "CustomerName"
                cbxCustomer.ValueMember = "CustomerId"
                cbxCustomer.DataSource = dt
            Catch
            End Try
        End Sub
    
        'populates combobox employee
        Private Sub populatecbxEmployee()
            _class.connectionString()
            Try
                Dim sqlEmpl As String = "Select EmployeeId, EmployeeName From Employees;"
                Dim dAdapter As New SqlDataAdapter(sqlEmpl, _class.conConnect)
                Dim dt As New DataTable()
                dAdapter.Fill(dt)
                cbxEmployee.DisplayMember = "EmployeeName"
                cbxEmployee.ValueMember = "EmployeeId"
                cbxEmployee.DataSource = dt
            Catch
            End Try
        End Sub
    
        'populates combobox order
        Private Sub populatecbxOrder()
            _class.connectionString()
            Try
                Dim sqlOrder As String = "Select OrderId, OrderName From Orders;"
                Dim dAdapter As New SqlDataAdapter(sqlOrder, _class.conConnect)
                Dim dt As New DataTable()
                dAdapter.Fill(dt)
                cbxOrder.DisplayMember = "OrderName"
                cbxOrder.ValueMember = "OrderId"
                cbxOrder.DataSource = dt
            Catch
            End Try
        End Sub
    End Class
    
    'Sample Connection string
    Class ClassConnectionString
        Public conConnect As SqlConnection
    
        Public Sub connectionString()
            conConnect = New SqlConnection("Data Source = (local); Database = 'SampleOrder'; Integrated Security = true")
        End Sub
    End Class
    
    

     

    Best Regards.

    Hardz


    Saturday, October 15, 2011 4:07 AM
  • Hardz,

     

    Thanks for the reply.

     

    Why does the RegionName and ProductName not display in the OrderDetails Table properly in a datagridview, and only the RegionID and ProductID. There is a Foreign Key relation setup between the Region Table and the Product Table respectively. The other foreign key setups don;t have this issue.

     

    Thanks

    Sunday, October 16, 2011 11:32 PM
  • Order detail has six columns (OrderDetailID, OrderID, RegionID, ProductID, QTY, Date Shipped)

    I dropped .........  Order detail onto the Datagrid view.
     

    Why does the RegionName and ProductName not display in the OrderDetails Table properly in a datagridview

    Possibly because the RegionName and ProductName aren't columns in the OrderDetails table?

     

    Monday, October 17, 2011 12:30 AM
  • So should I switch the Foreign Key?

    Currently it's Region (Table) RegionID (Column), OrderDetail (Table) RegionID (Column).

    Should I switch to Region (Table) RegionName (Column) , OrderDetail (Table) RegionName (Column)?

    or should I add RegionName & ProductName columns to the OrderDetails Table.

     

    Thanks

     

    Peter

    Monday, October 17, 2011 2:41 PM
  • Switching the keys will have no effect.

    If you want to display RegionName and ProductName then you have to have columns for them in the OrderDetail table in the DataSet (not in the database).  You do that in the dataset designer.

    You can then select the values for those columns using Joins in your select statement.

    I would do that as follows:

    Remove the line which fills the OrderDetail table in the form load handler.

    In the dataset designer press F7 and paste in the following above what is already there:

     

    Imports System.Data.SqlServerCe
    Namespace PeterFreemanDataSetTableAdapters
        Partial Class OrderDetailTableAdapter
            Public Function FillByOrderID(ByVal Table As PeterFreemanDataSet.OrderDetailDataTable, ByVal OrderID As Integer) As Integer
                Dim SelectString As String = "SELECT * FROM OrderDetail " & _
                                                          "INNER JOIN Product ON OrderDetail.ProductID = Product.ProductID " & _
                                                          "INNER JOIN Region ON OrderDetail.RegionId = Region.RegionID " & _
                                                          "WHERE OrderID=@OrderID"
                Adapter.SelectCommand = New SqlCeCommand(SelectString, Connection)
                Adapter.SelectCommand.Parameters.Add(New SqlCeParameter("OrderID", OrderID))
                If ClearBeforeFill Then
                    Table.Clear()
                End If
                Return Adapter.Fill(Table)
            End Function
        End Class
    End Namespace
    
    

     

    Add code to call this function when the combobox selected value changes:

     

        Private Sub ComboBox3_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox3.SelectedValueChanged
            OrderDetailTableAdapter.FillByOrderID(PeterFreemanDataSet.OrderDetail, CInt(ComboBox3.SelectedValue))
        End Sub
    
    

     

     


    • Edited by Dave299 Monday, October 17, 2011 9:34 PM
    Monday, October 17, 2011 4:22 PM
  • Hi Peter,

    How about your issue now?

    Is Dave's suggestion helpful?

    Do you have any updates?

    Best regards,


    Mike Feng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, October 20, 2011 2:52 AM
    Moderator