Display first record from each group in DataList using C# in ASP.Net

Last Reply 28 days ago By dharmendr

Posted one month ago

One user (ram) upload photo display image slider ok. sham and  sam two  friend. sham upload 7 photo and  sam upload 10 photo. i want output sham and sam only 1 photo show using DataList. After that sham photo click all photo show using image slider.  This code has been problem sham and sam all photo show only 1 photo show

        public DataSet showfrndstory(int id)
        {
            Sqlcon();
            
            string qry = " select a.to_id, a.from_id, b.*, b.id as 'bid',c.file_path, c.u_id as 'cuid' from tblfrequst a "
            + " join tbluserinfo b on a.to_id=b.id"
            + " join story c on c.u_id=b.id"
            + " where a.from_id=" + id + " and a.request_status='AC'"
            + " UNION"
            + " select a.to_id, a.from_id, b.*, b.id,c.file_path, c.u_id from tblfrequst a "
            + " join tbluserinfo b on a.from_id=b.id "
            + "join story c on c.u_id=b.id"
            + " where a.to_id=" + id + " and a.request_status='AC'";

            SqlDataAdapter adp = new SqlDataAdapter(qry, con);
         
            DataSet ds = new DataSet();
            adp.Fill(ds);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds;
            }
            else
            {
                return null;
            }
        }

 

Posted 28 days ago

Chedck the query.

create table #tbluserinfo(id int ,First_Name varchar(100), EMAIL VARCHAR(40))
create table #tblfrequst(f_id int,to_id int, from_id int,request_status VARCHAR(10))
create table #STORY (id int , U_id int,file_path VARCHAR(100))
 
insert into #tbluserinfo values(10,'SHIVANI','SHIVANIGARG@GMAIL.COM')
insert into #tbluserinfo values(6,'Sanjay','sanjay@GMAIL.COM')
insert into #tbluserinfo values(5,'Mohit','Mohitbatta@gmail.COM')
insert into #tbluserinfo values(9,'RAMAN','ramanbansal@gmail.com')
 			
insert into #tblfrequst values(1,5,10,'AC')
insert into #tblfrequst values(3,9,10,'AC') 			
insert into #tblfrequst values(4,10,6,'AC')
 			
INSERT INTO #STORY VALUES(1,5,'~/Story/WP_20140331_004.jpg')
INSERT INTO #STORY VALUES(2,5,'~/Story/WP_20140413_001.jpg') 			
INSERT INTO #STORY VALUES(3,5,'~/Story/WP_20140331_020.jpg')
INSERT INTO #STORY VALUES(4,5,'~/Story/WP_20140408_007.jpg')
INSERT INTO #STORY VALUES(5,6,'~/Story/20171022_191900.jpg') 			
INSERT INTO #STORY VALUES(6,6,'~/Story/20171025_174032.jpg') 			
INSERT INTO #STORY VALUES(7,6,'~/Story/20171027_205850.jpg') 			
INSERT INTO #STORY VALUES(8,6,'~/Story/20171101_110004.jpg') 			
INSERT INTO #STORY VALUES(9,6,'~/Story/20171101_110004.jpg') 			
INSERT INTO #STORY VALUES(10,10,'~/Story/20150417_190539.jpg') 			
INSERT INTO #STORY VALUES(11,10,'~/Story/20150503_095829.jpg') 			
INSERT INTO #STORY VALUES(12,10,'~/Story/20150417_190419.jpg') 			
INSERT INTO #STORY VALUES(13,9,'~/Story/DSC00636.JPG') 			
INSERT INTO #STORY VALUES(14,9,'~/Story/DSC00637.JPG') 			
INSERT INTO #STORY VALUES(15,9,'~/Story/DSC00638.JPG')

DECLARE @Id INT 
SET @Id = 5

SELECT TOP 1 * 
FROM 
(
    select a.to_id, a.from_id, b.*, b.id as 'bid',c.file_path, c.u_id as 'cuid' 
    from #tblfrequst a 
    join #tbluserinfo b on a.to_id=b.id
    join #story c on c.u_id=b.id
    where a.request_status='AC'
    UNION
    select a.to_id, a.from_id, b.*, b.id,c.file_path, c.u_id 
	from #tblfrequst a
    join #tbluserinfo b on a.from_id=b.id 
    join #story c on c.u_id=b.id
    where a.request_status='AC'
)story
WHERE id=@Id
ORDER BY ROW_NUMBER() OVER (PARTITION BY cuid ORDER BY cuid DESC)
DROP TABLE #tbluserinfo
DROP TABLE #tblfrequst
DROP TABLE #STORY