how to select the maximum value from multiple columns c#

J Vs

I have a Access DB with multiple columns,

Mark1	Mark2	Mark3	Mark4	Mark5
99	87	78	78	79

I need to get the max value and display it in a textbox

then I need to get the avg value and display it in a text box

here is my code:please help with the part as bold

connection.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
string query = "select* from Students WHERE Firstname LIKE '" + tbsearchid.Text + "' OR ID1 like '" + tbsearchid.Text + "' ";
command.CommandText = query;
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read()) {
tbFname.Text = reader["Firstname"].ToString();
tbLname.Text = reader["Lastname"].ToString();
string course1Text = reader["Course1"].ToString(), course2Text = reader["Course2"].ToString();
tbmulti.Text = "Course1: " + course1Text + Environment.NewLine + "Course2: " + course2Text;
help here >> tbhighmark.Text = max Value;
help here >> tbCourseAvg.Text = tbCourseAvg value;
}
connection.Close();

12 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajanikant Hawaldar7y ago
Accepted answer
@Jaco, go through error free
  //Fill Marks1 to 5
int[] marks = new int[10] { Convert.ToInt32(reader["Mark1"]), Convert.ToInt32(reader["Mark2"]), Convert.ToInt32(reader["Mark3"]), Convert.ToInt32(reader["Mark4"]), Convert.ToInt32(reader["Mark5"]), Convert.ToInt32(reader["Mark6"]), Convert.ToInt32(reader["Mark7"]), Convert.ToInt32(reader["Mark8"]), Convert.ToInt32(reader["Mark9"]), Convert.ToInt32(reader["Mark10"]) };
txtMaxMarks.text = marks.Max().ToString();// you got max marks here
int avgmarks = sumAverageElements(marks, marks.Length);// you avg marks here
txtAvgMarks.Text = avgmarks.ToString();
J Vs7y ago
@Rajanikant - thank you I tried your script but is also gives errors?
Rajanikant Hawaldar7y ago
@Jaco based on No. of columns query will become bigger you may feel complex this is the reason i gave solution using c# without changing your select * from query,

you posted syntex error in query these also you not change Amit query you keeping / 5

suggest you check properly.

Thanks
J Vs7y ago
@Amit - thank you for your help

Isn't there an easier way for Access? I have 10 columns to filter through for Course Marks?
Amit Mohanty7y ago
Sql Server :
string query = "select *,(SELECT MAX(MaxMark) FROM (VALUES (Mark1),(Mark2)," +
                "(Mark3),(Mark4),(Mark5)) AS MaxValue(MaxMark)) AS MaxMark," +
                "(Mark1 + Mark2 + Mark3 + Mark4 + Mark5) / 5 AS AvgMark " +
                "from Students WHERE Firstname LIKE '" + tbsearchid.Text + "' OR " +
                "ID1 like '" + tbsearchid.Text + "' ";
MS Access:
string query = "SELECT *, iif(iif(iif(iif(Mark1 > Mark2, Mark1, Mark2) > Mark3, " +
                "iif(Mark1 > Mark2, Mark1 , Mark2), Mark3)  > Mark4, iif(iif(Mark1 > Mark2, Mark1, Mark2) " +
                "> Mark3, iif(Mark1 > Mark2, Mark1 , Mark2), Mark3) ,Mark4 ) > Mark5,iif(iif(iif(Mark1 > " +
                "Mark2, Mark1, Mark2) > Mark3, iif(Mark1 > Mark2, Mark1 , Mark2), Mark3)  > Mark4, " +
                "iif(iif(Mark1 > Mark2, Mark1, Mark2) > Mark3, iif(Mark1 > Mark2, Mark1 , Mark2), Mark3), Mark4 ), Mark5) AS MaxMark, " +
                "(Mark1 + Mark2 + Mark3) / 3 AS AvgMark FROM Students WHERE Firstname LIKE '" + tbsearchid.Text + "' OR " +
                "ID1 like '" + tbsearchid.Text + "' ";
J Vs7y ago
so here is what i have now -
still not working for some reason..
Amit Mohanty7y ago
Hey use the below query to find max mark.
string query = "select *,(SELECT MAX(MaxMark) FROM (VALUES (Mark1),(Mark2)," +
                "(Mark3),(Mark4),(Mark5)) AS MaxValue(MaxMark)) AS MaxMark," +
                "(Mark1 + Mark2 + Mark3 + Mark4 + Mark5) / 5 AS AvgMark " +
                "from Students WHERE Firstname LIKE '" + tbsearchid.Text + "' OR " +
                "ID1 like '" + tbsearchid.Text + "' ";
tbhighmark.Text = reader["MaxMark"].ToString();
tbCourseAvg.Text = reader["AvgMark"].ToString();
Rajanikant Hawaldar7y ago
Hi Jaco,

Modified your code as per your requirement have a look
connection.Open();
           OleDbCommand command = new OleDbCommand();
           command.Connection = connection;
           string query = "select* from Students WHERE Firstname LIKE '" + tbsearchid.Text + "' OR ID1 like '" + tbsearchid.Text + "' ";
           command.CommandText = query;


           OleDbDataReader reader = command.ExecuteReader();
           while (reader.Read())
           {
               tbFname.Text = reader["Firstname"].ToString();
               tbLname.Text = reader["Lastname"].ToString();

               string course1Text = reader["Course1"].ToString(), course2Text = reader["Course2"].ToString();
               tbmulti.Text = "Course1: " + course1Text + Environment.NewLine + "Course2: " + course2Text;
              //Fill Marks1 to 5
               int[] marks = new int[5] { reader["Mark1"], reader["Mark2"], reader["Mark3"], reader["Mark4"], reader["Mark5"] };
               txtMaxMarks.text = marks.Max();// you got max marks here
               int avgmarks = sumAverageElements(marks, marks.Length);// you avg marks here

               txtAvgMarks.Text = avgmarks.ToString();
           }
           connection.Close();
Introduced one method to find Avg marks and i made this method as static you can remove if required.
public static int sumAverageElements(int[] arr, int size)
{

    int sum = 0;
    int average = 0;
    for (int i = 0; i < size; i++)
    {
        sum += arr[i];
    }
    average = sum / size;
    return average;
}
https://www.sanfoundry.com/csharp-program-average-array/
J Vs7y ago
can you please elborate on the code to be used?
Rajanikant Hawaldar7y ago
marks1 to marks4 columns are in student table right? if there just fill them to int [ ] marks=new marks[4] then using that link which i sent, there is a logic to find avg marks using array using methods using below inbuilt method you can find max number refer below thread which i forgot to send you to find Max marks using simple arrays https://stackoverflow.com/questions/13755007/c-sharp-find-highest-array-value-and-index
J Vs7y ago
This doesn't even link or search the Accessdb for the values?
Rajanikant Hawaldar7y ago
Hi Jaco, refer this thread, pass marks1 to marks4 https://stackoverflow.com/questions/12949907/how-can-i-return-the-sum-average-of-an-int-array