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?
 
Smart_ManConnect With a Mentor Commented:
can you provide more details about teh tables and what do you want to do with them ?
0
 
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
All Courses

From novice to tech pro — start learning today.