0
protected void loadCandidate()
    {
        con.Open();
        MySqlCommand cmd = new MySqlCommand("select studentID ,name from candidate ", con);
        MySqlDataReader dr = cmd.ExecuteReader();
        if (dr.HasRows == true)
        {
            GridView1.DataSource = dr;
            GridView1.DataBind();
        }
    }

Candidate database

Voter database

I'm using the gridview to display the candidate. I would like to display the gridview based on the faculty(now the gridview are displaying all candidate). When the voter login to thier account, if the voter belong to faculty MCLR, then the gridvire will only display the candidate belong to faculty MCLR, all other candidate belong to other faculty will not be shown.

Kekw Yc
  • 5
  • 4

2 Answers2

0

Keep your database object local to the methods where they are used. Many of them need to be closed and disposed. using blocks take care of this for you.

Don't open your connection until directly before the line with an .Execute... method. There are a limited number of connections available and they should be returned to the pool as quickly as possible.

I had to guess what you login code might look like. I assumed the StudentID is an integer field. Always use parameters to avoid Sql injection. I used .ExecuteScalar() since we are only expecting a single piece of data, the Faculty associated with the student.

In the loadCandidate method I used a DataTable to hold the data because I wasn't sure if a DataReader could be a DataSource for a GridView. In the Select string we use a parameter in the Where clause, the value of which we set in the login.

    private string conStr = "Your connection string";
    private string facultyID = "";
    protected void login()
    {
        using (MySqlConnection con = new MySqlConnection(conStr))
        using (MySqlCommand cmd = new MySqlCommand("Select Faculty From Voter Where StudentID = @ID and Name = @Name;", con))
        {
            cmd.Parameters.AddWithValue("@ID", (int)txtID.Text);
            cmd.Parameters.AddWithValue("@Name", txtName.Text);
            con.Open();
            facultyID = cmd.ExecuteScalar().ToString();
        } //Connection and Command are closed and disposed here.
    }
    protected void loadCandidate()
    {
        DataTable dt = null;

        using (MySqlConnection con = new MySqlConnection(conStr))
        using (MySqlCommand cmd = new MySqlCommand("select studentID ,name from candidate Where Faculty = @FacultyID ", con))
        {
            cmd.Parameters.AddWithValue("@FacultyID", facultyID);
            con.Open();
            dt.Load(cmd.ExecuteReader());
        } //Connection and Command are closed and disposed here.
            GridView1.DataSource = dt;
            GridView1.DataBind();
    }
Mary
  • 14,926
  • 3
  • 18
  • 27
  • It shows txt.Name is not exist in the current content. – Kekw Yc Oct 19 '20 at 06:24
  • Well, I said I had to guess at your login code. I assumed you would a text box for Name and ID. I gave them descriptive names. If the source of the user input is different you will have to change the code. It is not meant to copied and pasted. Understand it and then apply it to your situation. – Mary Oct 19 '20 at 06:27
  • This is not a login page, this is a page called candidateDetails . It will only use the girdview to display the candidate and a button list allow the user to vote for the candidate, I want to display the candidate based on the user faculty (if the users belong to MCLR, only display the candidate belong to MCLR ) – Kekw Yc Oct 19 '20 at 07:05
  • @KekwYc Well, what did you do to login the student? – Mary Oct 19 '20 at 07:43
  • The user will enter id and password to log in. After they log in, there are many pages. However, when they open the candidateDetails page. I would like to display a candidate details based on the user faculty – Kekw Yc Oct 19 '20 at 07:52
  • I understand what you want. Don't you query the Voter database to login the student? What is the primary key of the Voter table? – Mary Oct 19 '20 at 07:55
  • It is very late here. I am not ignoring you. I will be back tomorrow. Must get some sleep. – Mary Oct 19 '20 at 07:56
  • Sorry for the late reply. I'm doing the voting system. The user can log in to their account to view the candidate in the election. I'm using the gridview to display the candidate. In the database which stores the candidate, the candidate comes from many different faculty. What I try to do is, if the user login to their account, when they want to view the candidate, it will only display the candidate which belongs to the same faculty as them, which mean the user from faculty MCCO are only can see the candidate form MCCO in gridview and vote for them. – Kekw Yc Oct 20 '20 at 06:24
-2

if you need voter login time save his faculty. to variable then you use the faculty your variable example save static varible login time his faculty

 protected void loadCandidate()
        {
            con.Open();
            MySqlCommand cmd = new MySqlCommand(""select studentID ,name from Voter as v 
                                                inner join candidate  as c
                                               v.StudentID=c.StudentID where 
                                               v.Faculty='"+yourvariablename+"' " ", con);
            MySqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows == true)
            {
                GridView1.DataSource = dr;
                GridView1.DataBind();
            }
        }
Chameera
  • 192
  • 2
  • 8
  • How can I save the voter faculty to a variable? Maybe using the session? Because when the user logs in they only need to enter studentID and password. – Kekw Yc Oct 19 '20 at 04:03
  • 4
    `v.Faculty='"+yourvariablename+"' " "` **DO NOT DO THIS, UNDER ANY CIRCUMSTANCES** https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection – mjwills Oct 19 '20 at 04:12
  • Any idea of how to show the voter belong to MCLR the candidate belong to faculty MCLR, all other candidate belong to other faculty will not be shown? – Kekw Yc Oct 19 '20 at 06:11