Click here to Skip to main content
15,911,646 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi All

Please give me a solution.

I have One DataGridView in which One Cell i want to display multiple records that is save in SQL table and records are assigned to Same ID

Like

we assigned multiple classes and sections to one teacher. when teachers data is displaying in datagrid view then in teaching classes should be in one cell .
i have two tables one for teacher basic info master and other for classes that is taught by perticular teacher id .
i am using class objects to get all data and filtering all that data using List .

i m getting only last saved value in cell . and i need to display all related id values in one cell seperated by comma .

please help !

Regards !




please help how to fill one cell with multiple records or multiple rows with same ID ????
Posted
Updated 13-Mar-12 6:19am
v3
Comments
Shahin Khorshidnia 13-Mar-12 15:14pm    
If you say: "I want to do so and so, what's the solution?"
The experts answer: "You can do such and such" then you say: "What?! ... How can I perform your solution? ... Will you give me a sample? At last, can you do my job?" :D
If you want to solve your problem, you must try "yourself", then if you encounter a problem again, compose your code and specific the problem. Then the experts can help you with great pleasure ;)
gauravupadhyay 17-Mar-12 13:49pm    
nice comment
but solution was unreachable and thats why i posted in this cp forum ...

If the data of Tearchers and Classes tables is read into the corresponding DataTables in the program, which may be preferable as these tables can be used for other purpose also, then a DataTable can be created with TeacherId in one column and all classes corresponding to each TeacherId in the Classes Column of the corresponding row as shown below:
C#
void Main()
{
    //Note: The following data is given here only to run the sameple code
    //in the actual program this data will be read from the DataBase
    DataTable teachers = new DataTable();
    teachers.Columns.Add("TeacherId",typeof(string),null);
    teachers.Columns.Add("TeacherName",typeof(string),null);
    teachers.Rows.Add("T1","Teacher1");
    teachers.Rows.Add("T2","Teacher2");
    
    DataTable classes = new DataTable();
    classes.Columns.Add("ClassId",typeof(string),null);
    classes.Columns.Add("TeacherId",typeof(string),null);
    classes.Rows.Add("C1","T1");
    classes.Rows.Add("C1","T2");
    classes.Rows.Add("C4","T1");
    classes.Rows.Add("C6","T2");
    classes.Rows.Add("C7","T1");
    classes.Rows.Add("C8","T1");
    //Note: The above data is given here only to run the sameple code
    //in the actual program this data will be read from the DataBase
    
    DataTable teacherClasses = new DataTable();
    teacherClasses.Columns.Add("TeacherId",typeof(string),null);
    teacherClasses.Columns.Add("Classes",typeof(string),null);
    DataTable teacherClasses2 = teacherClasses.Clone();
    	
    //Using LINQ
    teachers.AsEnumerable().Select (t => {
    	string teacherId = t.Field<string>("TeacherId");
    	string allClassIds = classes.AsEnumerable().Where (c => string.Equals(
            c.Field<string>("TeacherId"),teacherId, StringComparison.InvariantCulture))
            .Aggregate (string.Empty,(string allClasses, DataRow c) => 
                 allClasses + (string.IsNullOrEmpty(allClasses) ? string.Empty :
        	 ", ") + c.Field<string>("ClassId"));
        	teacherClasses.Rows.Add(teacherId,allClassIds);
    	return t;		
    }).Count();
    //Note: Count is called to execute the query, without this the query will not
    //be executed due to deferred execution model of LINQ.
    
    //Using DataTable Select method
    foreach(DataRow tRow in teachers.Rows){
    	string teacherId= tRow["TeacherId"].ToString();
    	DataRow[] allClassRows = classes.Select(
        	string.Format("TeacherId='{0}'",teacherId),
        	string.Empty);
    	string allClassIds = string.Empty;
    	foreach(DataRow row in allClassRows)
            allClassIds += (string.IsNullOrEmpty(allClassIds) ?
       		"" : ", ") + row["ClassId"].ToString();
    	teacherClasses2.Rows.Add(teacherId,allClassIds);
    }
}

//Contents of teacherClasses Table will be
//TeacherId Classes 
//T1 		C1, C4, C7, C8 
//T2 		C1, C6 </string></string></string>


Then the teacherClasses can be assigned to the DataSource property of DataGridview.
 
Share this answer
 
v2
Probably you need to use STUFF[^] and FOR XML[^] clause.

Becouse i don't know your database structure, i'll try to explain on an example. Let say, you have table: Classes, where are stored ClassID.
SQL
DECLARE @cols NVARCHAR(200)

SET @cols = STUFF((SELECT ',' + CONVERT(NVARCHAR,[ClassID])
					FROM [Classes] AS O
					ORDER BY ',' + CONVERT(NVARCHAR,[ClassID])
			FOR XML PATH('')),1,2,'') + ' '

SELECT @cols AS [AllClassID]

The above command will return data as follow: 1,2,3,4,5,

You need to change sql command to work properly for you.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900