Displaying ID column instead of Name Column

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

 

Answer 1

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

Answer 2

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

Answer 3

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?

 

Answer 4

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

Answer 5

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

 

 

Answer 6

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.