combining two rows into one in vb.net datagrid

Question

I have one table which contains useridname and role column. A user can have more than one role I nee to put all these values in one datagrid in vb.net but i need to put the role separated by . for ex This data should come into a datagrid in vb.net UserId Name Role U1 ABC AdminSuper AdminHR Thanks Arpita Tripathi

Iftikar Hussain · Answer

Hi You can write the sql query like this create a new SP as CREATE PROCEDURE [dbo].[usp_GetUserAccount] ( @UserId varchar(15) ) AS SET NOCOUNT ON SELECT UserIdName SUBSTRING(Roles0LEN(Roles)) AS Role FROM UserAccount WITH(NOLOCK) CROSS APPLY ( SELECT RoleName+'' FROM UserAccountLinkRoles WITH(NOLOCK) WHERE UserId=@UserId ORDER BY RoleName FOR XML PATH('') )AS E(Roles) WHERE UserId=@UserId SET NOCOUNT OFF in your code using (SqlConnection conn = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand(usp_GetUserAccount conn); command.AddParametersWithValue(@UserIdU1); command.CommandType = CommandType.StoredProcedure; DataSet ds=new DataSet(); SqlDataAdapter da = new SqlDataAdapter(); DataTable dt = new DataTable(); conn.Open(); da.SelectCommand = cmd; da.Fill(dt); dataGridView1.DataSource = dt; conn.Close(); } Regards Iftikar

Jignesh Trivedi · Answer

hi Agreee with above two answer. Also recommend go with Store procedure and prepair required data in your sql result Please refer like it might help you to find comma seperated value http://www.c-sharpcorner.com/UploadFile/ff2f08/comma-separated-value-in-sql-query/

Mahesh Chand · Answer

Looks like Iftikar has already replied it.

Mahesh Chand · Answer

There are two ways to do this. 1. Your store procedure or SQL query should select data from both tables. For your application it does not matter how many tables data is coming from. This is recommended . 2. Some developers do not have control over SQL queries or database. In that case you bring data in two DataTables and then you merge or insert one record from Table 2 into Table 1.