Link to home
Start Free TrialLog in
Avatar of bigmoxy
bigmoxy

asked on

Desparate! How do I get around "column doesn't have unique values" when adding dataset relationship?

I am creating a nested repeater against a 1 table Access db (no choice to change that). My problem is that when I run the page, I get an error adding column relationships. The message is - System.ArgumentException: These columns don't currently have unique values and is pointing to the Model_Vehicle relationship.

I'm puzzled how this works because tfkVehicleMake is unique on the VehicleMakes table but not unique on the VehicleModels table. Why then, can I create the Make_Model relationship?

Unfortunately fld15Model is not unique on the VehicleModels table because in my case Chevrolet and GMC trucks share model names in some cases (or at least according to the data I have). How can I get around this? I tried setting Unique=False however then I got this error on fld15Model - System.Data.DataException: A child row has multiple parents.

        Dim strSQLMake As String = "SELECT DISTINCT fld15VehicleType," & _
            " tfkVehicleMake " & _
            " FROM tbl15Vehicles" & _
            " WHERE fld15VehicleType LIKE 'Pass%'"
        Dim strSQLModel As String = "SELECT DISTINCT tfkVehicleMake," & _
            " fld15Model" & _
            " FROM tbl15Vehicles" & _
            " WHERE fld15VehicleType LIKE 'Pass%'"
        Dim strSQLVehicle As String = "SELECT DISTINCT tfkVehicleMake," & _
            " fld15Model, fld15Year, fld15ExteriorColor, fld15Mileage," & _
            " fld15AskingPrice, fld15StockNum" & _
            " FROM tbl15Vehicles" & _
            " WHERE fld15VehicleType LIKE 'Pass%'" & _
            " ORDER BY fld15Model"

        Dim conn As New OleDbConnection(strConnection)
        Dim daMakes As New OleDbDataAdapter(strSQLMake, conn)
        Dim daModels As New OleDbDataAdapter(strSQLModel, conn)
        Dim daVehicles As New OleDbDataAdapter(strSQLVehicle, conn)

        Dim ds As New DataSet()

        daMakes.Fill(ds, "VehicleMakes")
        daModels.Fill(ds, "VehicleModels")
        daVehicles.Fill(ds, "Vehicles")

        ds.Tables("Vehicles").Columns.Add("fldCameraPhoto", GetType(System.String))

        ds.Relations.Add("Make_Model", _
            ds.Tables("VehicleMakes").Columns("tfkVehicleMake"), _
            ds.Tables("VehicleModels").Columns("tfkVehicleMake"))
        ds.Relations(0).Nested = True
====================================================
        ds.Relations.Add("Model_Vehicle", _
            ds.Tables("VehicleModels").Columns("fld15Model"), _
            ds.Tables("Vehicles").Columns("fld15Model"))
        ds.Relations(1).Nested = True
====================================================
ASKER CERTIFIED SOLUTION
Avatar of Smart_Man
Smart_Man
Flag of Egypt image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bigmoxy
bigmoxy

ASKER

I'm converting a site from static pages that are updated daily to a dynamic one that uses this ugly Access db. Currently I am working on this page - http://www.cargovango.com/passenger.htm. Some of the pages are by make (e.g. GM, Ford, etc.), others like this one are by category. Passenger Vans are my search criteria. With only one table I can't perform relational queries. I need the result to be grouped by model within make.

Thank you!
Tim
Avatar of bigmoxy

ASKER

I'm giving points and an "A" to everyone who responds to my questions. Quite often I am stumped and even the fact that someone expresses interest keeps me motivated.

I figured out a solution. I googled ds.Relations.Add and came across a link to Multiple Key Relations - http://www.thescripts.com/forum/thread380109.html.

I created two column keys for the VehicleModels and Vehicles tables and that gave me the desired display results. Here is my code:

        Dim dcVehicleModelsKey As DataColumn() = _
            {ds.Tables("VehicleModels").Columns("tfkVehicleMake"), _
            ds.Tables("VehicleModels").Columns("fld15Model")}
        Dim dcVehiclesKey As DataColumn() = _
            {ds.Tables("Vehicles").Columns("tfkVehicleMake"), _
            ds.Tables("Vehicles").Columns("fld15Model")}
        ds.Relations.Add("Model_Vehicle", _
            dcVehicleModelsKey, _
            dcVehiclesKey)

Thanks again for responding!
Tim
that is great. thank you so much.

i know what you are talking about. specially in developing. sometime all what you need is to talk to someone. it feels like your brains grow :).

that is why i asked for the issue from the begining.

hope you better EE experince next time.