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">

<title>Untitled Page</title>

</head>

<body>

<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