In this blog we will know how to display student have stdnt_activity 5,10
Here is the
scenario
I have one table student, i.e
stdnt_id, stdnt_name, stdnt_activity
i have values in student table
like this.
stdnt_id stdnt_name stdnt_activity
1 kiran 5
1 kiran 10
1 kiran 15
2 sachin 5
2 sachin 10
3 venkat 5
3 venkat 10
3 venkat 15
4 kumar 5
4 kumar 10
5 naveen 5
5 naveen 10
Here stdnt_activity 5 mean ->
pending, 10 -> In progress, 15 means -> closed
So, i want a query to display
the values of student table based on condition.
The condition is for example:
The student kiran having
stdnt_activity 5,10,15 where as sachin having stdnt_activity 5,10.
I need a query do display all
student records in a table which is having stdnt_activity 5,10 and not 15.
If student have stdnt_activity
5,10,15.we don't need to display to the user.
If student have stdnt_activity
5,10 then we need to display the values.
By above example i only need to
display the values of sachin, kumar, naveen (these student's doesn't have 15).
While student's kiran, venkat
will have 5,10,15.So these records need to be ignored (no need to display).
Solution: -
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Student_have_activity_5_and_10._Default" %>
<!DOCTYPE html
PUBLIC "-//W3C//DTD
XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form
id="form1"
runat="server">
<div>
<asp:GridView ID="GridView1"
runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using
System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
namespace
Student_have_activity_5_and_10
{
public partial
class _Default
: System.Web.UI.Page
{
string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlCommand com;
string str;
DataSet ds;
SqlDataAdapter sqlda;
protected void Page_Load(object
sender, EventArgs e)
{
if (!IsPostBack)
{
bindgrid();
}
}
void bindgrid()
{
SqlConnection con = new SqlConnection(connStr);
con.Open();
str = "SELECT * FROM
student e where not exists(select stdnt_id from student where stdnt_activity=
15 and stdnt_name= e.stdnt_name)";
com = new SqlCommand(str, con);
sqlda = new SqlDataAdapter(com);
ds = new DataSet();
sqlda.Fill(ds, "t1");
GridView1.DataSource = ds;
GridView1.DataMember = "t1";
GridView1.DataBind();
con.Close();
}
}
}
Thanks