combining two rows into one in vb.net datagrid

Arpita Tripathi

I have one table which contains userid,name 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 Admin,Super Admin,HR

Thanks

Arpita Tripathi

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Trivedi13y ago
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 Chand13y ago
Looks like Iftikar has already replied it.
Mahesh Chand13y ago
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.
Iftikar Hussain13y ago
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
UserId,Name,
SUBSTRING(Roles,0,LEN(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("@UserId","U1");
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