ashish007 says:
public DataSet showfrndstory(int id)
{
Sqlcon();
string qry= "With cte AS("
+ "SELECT ROW_NUMBER() OVER(PARTITION BY c.u_id ORDER BY c.u_id DESC) AS RowNum,a.to_id, c.*, a.from_id, b.id as 'bid', 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=10 and a.request_status='AC'"
+ " UNION "
+ " select ROW_NUMBER() OVER(PARTITION BY c.u_id ORDER BY c.u_id DESC) AS RowNum, a.to_id,c.*, a.from_id, b.id, 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=10 and a.request_status='AC'"
+")"
+ " SELECT * FROM cte WHERE RowNum <= 1";
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;
}
}
You didn't pass the id to the query. Specify the id condition in the query.
Example:
public DataSet showfrndstory(int id)
{
Sqlcon();
string qry = "With cte AS("
+ "SELECT ROW_NUMBER() OVER(PARTITION BY c.u_id ORDER BY c.u_id DESC) AS RowNum,a.to_id, c.*, a.from_id, b.id as 'bid', 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=10 and a.request_status='AC'"
+ " UNION "
+ " select ROW_NUMBER() OVER(PARTITION BY c.u_id ORDER BY c.u_id DESC) AS RowNum, a.to_id,c.*, a.from_id, b.id, 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=10 and a.request_status='AC'"
+ ")"
+ " SELECT * FROM cte WHERE RowNum <= 1 AND bid = " + id;
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;
}
}