Hello,
I am setting up a datagrid in a Vb6 application, its datasource is an ADO recordset that I am creating
from a SQL query. For example, consider the following table layout in an Access database:
tblClothes
ClothesID: 1 Jacket: 2 Pants: 3 Shirt: 2
ClothesID: 2 Jacket: 1 Pants: 2 Shirt: 3
The three clothing fields store an integer value that relates back to the primary key on the color table:
tblColor
ColorID: 1 ColorName: Red
ColorID: 2 ColorName: Black
ColorID: 3 ColorName: Yellow
Now lets say I want to create a recordset that includes the information above but is displayed as below when
the datagrid is set to use the ADO recordset as its datasource:
dgClothes
ClothesID Jacket Pants Shirt
1 Black Yellow Black
2 Red Black Yellow
The database shows the relationships and the form view in Access displays the relationships correctly (the fields for each clothing type has a drop down that displays all of the color names.) However, I am not sure how to get a SQL query to return ColorName three times and have it correspond to the clothing type, is it possible to do this with SQL? It seems strange to do a JOIN on three fields when they all return the same field "ColorName." It doesn't seem like that would work...am I thinking about
this in the correct way? Or should I build a couple of recordsets, one for clothes and one for colors, build an array out of colors and then build another recordset linking the two together? That seems to negate the benefit of the relationships in the db... I am hoping to have this datagrid be a bound control so that the user can edit the fields and update the db as needed. I get the
feeling that I am not conceptualizing this correctly...
Thank you for any assistance.
I am setting up a datagrid in a Vb6 application, its datasource is an ADO recordset that I am creating
from a SQL query. For example, consider the following table layout in an Access database:
tblClothes
ClothesID: 1 Jacket: 2 Pants: 3 Shirt: 2
ClothesID: 2 Jacket: 1 Pants: 2 Shirt: 3
The three clothing fields store an integer value that relates back to the primary key on the color table:
tblColor
ColorID: 1 ColorName: Red
ColorID: 2 ColorName: Black
ColorID: 3 ColorName: Yellow
Now lets say I want to create a recordset that includes the information above but is displayed as below when
the datagrid is set to use the ADO recordset as its datasource:
dgClothes
ClothesID Jacket Pants Shirt
1 Black Yellow Black
2 Red Black Yellow
The database shows the relationships and the form view in Access displays the relationships correctly (the fields for each clothing type has a drop down that displays all of the color names.) However, I am not sure how to get a SQL query to return ColorName three times and have it correspond to the clothing type, is it possible to do this with SQL? It seems strange to do a JOIN on three fields when they all return the same field "ColorName." It doesn't seem like that would work...am I thinking about
this in the correct way? Or should I build a couple of recordsets, one for clothes and one for colors, build an array out of colors and then build another recordset linking the two together? That seems to negate the benefit of the relationships in the db... I am hoping to have this datagrid be a bound control so that the user can edit the fields and update the db as needed. I get the
feeling that I am not conceptualizing this correctly...
Thank you for any assistance.