ACCESS Database - Check if User is in DB if not then alert

J Vs

7y762Windows Forms

Please see my script and help. IT HAS TO BE ACCESS DB and NOT SQL.

private void tableqry() {
try {
connection.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
string query = "select *," +
"(Course1 + Course2 + Course3 + Course4+ Course5 + Course6 + Course7 + Course8 + Course9 + Course10) / 10 AS AvgMark " +
"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(),
course3Text = reader["Course3"].ToString(), course4Text = reader["Course4"].ToString(),
course5Text = reader["Course5"].ToString(), course6Text = reader["Course6"].ToString(),
course7Text = reader["Course7"].ToString(), course8Text = reader["Course8"].ToString(),
course9Text = reader["Course9"].ToString(), course10Text = reader["Course10"].ToString();
tbmulti.Text = "Course1: " + course1Text + Environment.NewLine + "Course2: " + course2Text +
Environment.NewLine + "Course3: " + course3Text + Environment.NewLine + "Course4: " + course4Text +
Environment.NewLine + "Course5: " + course5Text + Environment.NewLine + "Course6: " + course6Text +
Environment.NewLine + "Course7: " + course7Text + Environment.NewLine + "Course8: " + course8Text +
Environment.NewLine + "Course9: " + course9Text + Environment.NewLine + "Course10: " + course10Text;
int[] marks = new int[10] {
Convert.ToInt32(reader["Course1"]), Convert.ToInt32(reader["Course2"]),
Convert.ToInt32(reader["Course3"]), Convert.ToInt32(reader["Course4"]),
Convert.ToInt32(reader["Course5"]), Convert.ToInt32(reader["Course6"]),
Convert.ToInt32(reader["Course7"]), Convert.ToInt32(reader["Course8"]),
Convert.ToInt32(reader["Course9"]), Convert.ToInt32(reader["Course10"])
};
int avgmarks = sumAverageElements(marks, marks.Length);
if (avgmarks <= 40) {
tbrate.Text = "Fail";
} else if (avgmarks <= 59) {
tbrate.Text = "Pass";
} else if (avgmarks <= 79) {
tbrate.Text = "Good";
} else if (avgmarks <= 100) {
tbrate.Text = "Excellent";
}
tbhighmark.Text = marks.Max().ToString();
tblowmark.Text = marks.Min().ToString();
tbCourseAvg.Text = avgmarks.ToString();
}
connection.Close();
} catch (Exception ex) {
MessageBox.Show("Error " + ex);
}
}

3 Replies

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

Rajanikant Hawaldar7y ago
Accepted answer
Go through below code.
private void tableqry()
        {

            try
            {
                connection.Open();
                OleDbCommand command = new OleDbCommand();
                command.Connection = connection;
                string query = "select *," +
                 "(Course1 + Course2 + Course3 + Course4+ Course5 + Course6 + Course7 + Course8 + Course9 + Course10) / 10 AS AvgMark " +
                 "from Students WHERE Firstname LIKE '" + tbsearchid.Text + "' OR " + "ID1 like '" + tbsearchid.Text + "'";
                command.CommandText = query;
                OleDbDataReader reader = command.ExecuteReader();
                bool IsUserAvailableInDb = reader.HasRows;
                while (reader.Read())
                {

                    tbFname.Text = reader["Firstname"].ToString();
                    tbLname.Text = reader["Lastname"].ToString();

                    string course1Text = reader["Course1"].ToString(), course2Text = reader["Course2"].ToString(),
                     course3Text = reader["Course3"].ToString(), course4Text = reader["Course4"].ToString(),
                     course5Text = reader["Course5"].ToString(), course6Text = reader["Course6"].ToString(),
                     course7Text = reader["Course7"].ToString(), course8Text = reader["Course8"].ToString(),
                     course9Text = reader["Course9"].ToString(), course10Text = reader["Course10"].ToString();

                    tbmulti.Text = "Course1: " + course1Text + Environment.NewLine + "Course2: " + course2Text +
                     Environment.NewLine + "Course3: " + course3Text + Environment.NewLine + "Course4: " + course4Text +
                     Environment.NewLine + "Course5: " + course5Text + Environment.NewLine + "Course6: " + course6Text +
                     Environment.NewLine + "Course7: " + course7Text + Environment.NewLine + "Course8: " + course8Text +
                     Environment.NewLine + "Course9: " + course9Text + Environment.NewLine + "Course10: " + course10Text;

                    int[] marks = new int[10] {
                    Convert.ToInt32(reader["Course1"]), Convert.ToInt32(reader["Course2"]),
                     Convert.ToInt32(reader["Course3"]), Convert.ToInt32(reader["Course4"]),
                     Convert.ToInt32(reader["Course5"]), Convert.ToInt32(reader["Course6"]),
                     Convert.ToInt32(reader["Course7"]), Convert.ToInt32(reader["Course8"]),
                     Convert.ToInt32(reader["Course9"]), Convert.ToInt32(reader["Course10"])
                   };

                    int avgmarks = sumAverageElements(marks, marks.Length);

                    if (avgmarks <= 40)
                    {
                        tbrate.Text = "Fail";
                    }
                    else if (avgmarks <= 59)
                    {
                        tbrate.Text = "Pass";
                    }
                    else if (avgmarks <= 79)
                    {
                        tbrate.Text = "Good";
                    }
                    else if (avgmarks <= 100)
                    {
                        tbrate.Text = "Excellent";
                    }

                    tbhighmark.Text = marks.Max().ToString();
                    tblowmark.Text = marks.Min().ToString();
                    tbCourseAvg.Text = avgmarks.ToString();
                }
                connection.Close();
                if(IsUserAvailableInDb == false)
                {
                    MessageBox.Show("User not exist");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error    " + ex);

            }
        }
J Vs7y ago
Here you go.
I need to find out if a user is in my ACcess DB, if he is then his results should populate in the windows form.

if he is not in the form then it should create an alert/
OleDbConnection connection = new OleDbConnection();
               connection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Admin\Documents\RateMyStudent\RateMyStudent\Students.mdb;
               Persist Security Info = False";
Rajanikant Hawaldar7y ago
Please post your connection string and mention all your requirements properly, also refer this
https://www.c-sharpcorner.com/UploadFile/c56872/connecting-C-Sharp-application-to-ms-access-database/