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
====================================================
bigmoxyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Smart_ManCommented:
can you provide more details about teh tables and what do you want to do with them ?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bigmoxyAuthor Commented:
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
0
bigmoxyAuthor Commented:
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
0
Smart_ManCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.