Hello, I have a question about taking data from database using multiple queries, and returning it to the asp.net c# code, and populate the resulting dataset to a single gridview.
I will show how the database tables look like.
My tables are :-
1) tbl_user [userid, username, email, phone]
2) tbl_skills [skillid, skillname]
3) tbl_qualification [qualification_id, qualification_name]
4) tbl_user_skill [userid, skill_id]
5) tbl_user_qualifiaction [userid, qualification_id]
I require that, based on userid, I need to create a comma separated list of skills, a comma separated list of qualifications, count of skills, count of qualifications, userid, username, email and phone.
For that I created the following stored procedure:-
ALTER PROCEDURE [dbo].[sp_loaduserdetailsingridview]
@flag int=0,
@userid int=0,
@skillnames varchar(max)=null,
@skillcount int=0,
@qualnames varchar(max)=null,
@qualcount int=0,
@qualid int=0,
@skillid int=0
as
if(@flag=0)
begin
select @skillnames=coalesce(@skillnames + ', ','') + skill_name from tbl_skill inner join
tbl_user_skill on tbl_skill.skill_id=tbl_user_skill.skill_id where tbl_user_skill.userid=@userid
select @qualnames=coalesce(@qualnames + ', ','') + qualification_name from tbl_qualification inner join
tbl_user_qualification on tbl_qualification.qualification_id=tbl_user_qualification.qualification_id
where tbl_user_qualification.userid=@userid
select @skillcount=count(skill_id) from tbl_user_skill where userid=@userid
select @qualcount=count(qualification_id) from tbl_user_qualification where userid=@userid
select @skillnames as skillnames, @skillcount as skillcount, @qualnames as qualnames, @qualcount as qualcount
select username, email, hone from tbl_user where userid=@userid
end
I require the last two select queries o be returned to the asp.net c# code, and populate them in a single gridview.
The gridview should look like the following format:-
[username, skillnames, skillcount, qualnames, qualcount, email, phone]
I tried using a dataset and append the data using a single dataset, but failed to work. When I use multiple dataset, some fields in the gridview will be populated, others will be bank, and some fields are duplicated.
How to solve this.