CODEWITHSUNDEEP
c#sqldatagridviewmultiple-tablesrecords
don
don

Reputation: 597

Display multiple records in one row of Datagridview

The code I used to display the Inventory table is given below.

con = conn.getConnected();
datadap.SelectCommand = new SqlCommand("SELECT Inventory.inv_ID AS ID, ItemType.it_type AS Type, Inventory.inv_qty AS [Qty],Inventory.inv_part_no AS [Part No], CONVERT(DECIMAL(10,2),Inventory.inv_whole_price) AS [Discount Price], CONVERT(DECIMAL(10,2),Inventory.inv_retail_price) AS [Selling Price], Inventory.inv_profit_percent AS [Percent] FROM Inventory INNER JOIN Country ON Inventory.count_ID = Country.count_ID INNER JOIN Class ON Inventory.class_ID = Class.class_ID INNER JOIN Dealer ON Inventory.deal_ID = Dealer.deal_ID INNER JOIN ItemType ON Inventory.it_ID = ItemType.it_ID", con);
dataset.Clear();
datadap.Fill(dataset);
dgInventory.DataSource = dataset.Tables[0];

The results set "Inventory" is

Inv_ID  Type           Qty  Code    Cost    Sell    Percent
3       Spockets Rear   1   1000    100.00  150.00  33.33   
4       Seat Cover      1   1000    10.00   12.00   16.67   
5       Brake Cable     1   5000    20.00   24.00   2       
6       Seat Cover      1   4400    400.00  411.00  1           
8       Spockets Rear   1   1503    1522.00 1252.00 15      
9       Seat Cover      1   1522    152.00  180.00  12      
10      Seat Cover      1   1522    152.00  180.00  12      
11      Seat Cover      1   1522    152.00  180.00  12  
12      Lights          1   5623    123.00  160.00  10      
13      Brake Shoe      1           90.00   100.00  10    
14      Brake Shoe      1           90.00   100.00  10   
15      Sprockets Front 1   100     2000.00 2200.00 9.0     
16      Spockets Rear   3   1001    615.00  800.00  23.125

The table InventoryMake Table has Inventory ID as the foreign key. InventoryMake table looks like

ID  Model_ID   Inv_ID
1     2         15
7     3         15
8     5         14
9     4         14

What I need to do is to show the corresponding Model_ID's for the foreign key Inv_ID in one row in Inventory Table as shown below.

Inv_ID  Type           Model_ID   Qty  Code    Cost    Sell    Percent
3       Spockets Rear              1   1000   100.00    150.00  33.33   
4       Seat Cover                 1   1000   10.00      12.00  16.67   
5       Brake Cable                1   5000   20.00      24.00  2       
6       Seat Cover                 1   4400   400.00    411.00  1           
8       Spockets Rear              1   1503   1522.00   1252.00 15      
9       Seat Cover                 1   1522   152.00    180.00  12      
10      Seat Cover                 1   1522   152.00    180.00  12      
11      Seat Cover                 1   1522   152.00    180.00  12  
12      Lights                     1   5623   123.00    160.00  10      
13      Brake Shoe                 1          90.00     100.00  10    
14      Brake Shoe        5,4      1          90.00     100.00  10   
15      Sprockets Front   2,3      1    100   2000.00   2200.00 9.0     
16      Sprockets Rear             3    1001  615.00    800.00  23.125

I need your help to figure out how to display several records in one row,as shown in the above table. Thanks. PS: I'm using C# and MSSQL 2008

Upvotes: 0

Views: 733

Answers (1)

HansP
HansP

Reputation: 111

If the data is stored in the database, you could retrieve as part of your query. Selecting the Model_ID as a comma separated string.

SELECT GROUP_CONCAT(Model_ID) 
FROM InventoryMake as IM,Inventory as I
WHERE I.Inv_ID = IM.Inv_ID
GROUP BY I.Inv_ID

Upvotes: 1

Related Questions