Nitish Ranjan Nitish Ranjan - 1 month ago 9
C# Question

Filter Data from Database

I want to filtter data from access database and show it to datagridview. Given below is the data from database.

ADMISSIONNO STUDENTNAME STUDENTCLASS STUDENTSECTION DUE CLASSID
111 nitish 6 a 1477 33
112 ravi 6 a 1400 33
113 raj 7 b 5000 35
111 nitish 6 a 1200 33
112 ravi 6 a 900 33
113 raj 7 b 1000 35
111 nitish 6 a 500 33
112 ravi 6 a 300 33
113 raj 7 b 600 35


i want a query to filtter the above data as when user select the CLASSID(33),
output should be like among the same ADMISSIONNO which is the lowest due, display that row. The output should be

ADMISSIONNO STUDENTNAME STUDENTCLASS STUDENTSECTION DUE
111 nitish 6 a 500
112 ravi 6 a 300


the query which i am using is:

OleDbCommand command1 = new OleDbCommand();
command1.Connection = connection;
string clas = "SELECT FeeData.ADMISSIONNO, FeeData.STUDENTNAME, FeeData.DUE, FeeData.STUDENTCLASS, FeeData.STUDENTSECTION, FeeData.CLASSID FROM FeeData WHERE(((FeeData.DUE) > '0') AND((FeeData.CLASSID) = "+ClassID+")) ";
command1.CommandText = clas;
OleDbDataReader reader = command1.ExecuteReader();
while (reader.Read())
{
//read the data
}


After running this query the data which i am getting is:

ADMISSIONNO STUDENTNAME STUDENTCLASS STUDENTSECTION DUE CLASSID
111 nitish 6 a 1477 33
112 ravi 6 a 1400 33
111 nitish 6 a 1200 33
112 ravi 6 a 900 33
111 nitish 6 a 500 33
112 ravi 6 a 300 33


I guess query is not correct. Please help me out!!

Answer Source

You need GROUP BY and MIN on your fields to group records according to all your conditions and then select the record with the minimum value.

string clas = @"SELECT f.ADMISSIONNO, f.STUDENTNAME, f.STUDENTCLASS,
                       f.STUDENTSECTION, f.CLASSID, MIN(f.DUE)
               FROM FeeData AS f 
               WHERE f.DUE > 0 AND f.CLASSID = @id
               GROUP BY f.ADMISSIONNO, f.STUDENTNAME, f.STUDENTCLASS,
                        f.STUDENTSECTION, f.CLASSID";

Notice also that you NEVER concatenate strings to create an sql command but always use a parameterized query

command1.CommandText = clas;
command1.Parameters.Add("@id", OleDbType.Integer).Value = ClassID;
OleDbDataReader reader = command1.ExecuteReader();