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(strConnect ion)
Dim daMakes As New OleDbDataAdapter(strSQLMak e, conn)
Dim daModels As New OleDbDataAdapter(strSQLMod el, conn)
Dim daVehicles As New OleDbDataAdapter(strSQLVeh icle, conn)
Dim ds As New DataSet()
daMakes.Fill(ds, "VehicleMakes")
daModels.Fill(ds, "VehicleModels")
daVehicles.Fill(ds, "Vehicles")
ds.Tables("Vehicles").Colu mns.Add("f ldCameraPh oto", GetType(System.String))
ds.Relations.Add("Make_Mod el", _
ds.Tables("VehicleMakes"). Columns("t fkVehicleM ake"), _
ds.Tables("VehicleModels") .Columns(" tfkVehicle Make"))
ds.Relations(0).Nested = True
========================== ========== ========== ======
ds.Relations.Add("Model_Ve hicle", _
ds.Tables("VehicleModels") .Columns(" fld15Model "), _
ds.Tables("Vehicles").Colu mns("fld15 Model"))
ds.Relations(1).Nested = True
========================== ========== ========== ======
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:
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(strConnect
Dim daMakes As New OleDbDataAdapter(strSQLMak
Dim daModels As New OleDbDataAdapter(strSQLMod
Dim daVehicles As New OleDbDataAdapter(strSQLVeh
Dim ds As New DataSet()
daMakes.Fill(ds, "VehicleMakes")
daModels.Fill(ds, "VehicleModels")
daVehicles.Fill(ds, "Vehicles")
ds.Tables("Vehicles").Colu
ds.Relations.Add("Make_Mod
ds.Tables("VehicleMakes").
ds.Tables("VehicleModels")
ds.Relations(0).Nested = True
==========================
ds.Relations.Add("Model_Ve
ds.Tables("VehicleModels")
ds.Tables("Vehicles").Colu
ds.Relations(1).Nested = True
==========================
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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( "tfkVehicl eMake"), _
ds.Tables("VehicleModels") .Columns(" fld15Model ")}
Dim dcVehiclesKey As DataColumn() = _
{ds.Tables("Vehicles").Col umns("tfkV ehicleMake "), _
ds.Tables("Vehicles").Colu mns("fld15 Model")}
ds.Relations.Add("Model_Ve hicle", _
dcVehicleModelsKey, _
dcVehiclesKey)
Thanks again for responding!
Tim
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"
ds.Tables("VehicleModels")
Dim dcVehiclesKey As DataColumn() = _
{ds.Tables("Vehicles").Col
ds.Tables("Vehicles").Colu
ds.Relations.Add("Model_Ve
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.
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.
ASKER
Thank you!
Tim