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;
}
}