How to retrieve data from database based on selected value in combo box?

Question

Im new to c# here. I have a comboBox codes that enable user to choose month and dates. When user click cmdSend button, the program will retrieve the month & date form comboBox and call dbConnect.Select class function to do the select mysql statement.

private void cmdSend_Click(object sender, System.EventArgs e)
    {
        List<string>[] list;
        list = dbConnect.Select(month_list.Text, year_list.Text);

        printer_info.Rows.Clear();
        for (int i = 0; i < list[0].Count; i++)
        {
            int number = printer_info.Rows.Add();
            printer_info.Rows[number].Cells[0].Value = list[0][i];
            printer_info.Rows[number].Cells[1].Value = list[1][i];
            printer_info.Rows[number].Cells[2].Value = list[2][i];
            printer_info.Rows[number].Cells[3].Value = list[3][i];
        }
    }

the retrieve database class:

public List<string>[] Select(string month,string year)
    {
        string query = "SELECT * FROM page_counter where month ='" + month + "' AND year ='" + year + "' ;";

        //Create a list to store the result
        List<string>[] list = new List<string>[4];
        list[0] = new List<string>();
        list[1] = new List<string>();
        list[2] = new List<string>();
        list[3] = new List<string>();

        //Open connection
        if (this.OpenConnection() == true)
        {
            //Create Command
            MySqlCommand cmd = new MySqlCommand(query, connection);
            //Create a data reader and Execute the command
            MySqlDataReader dataReader = cmd.ExecuteReader();

            //Read the data and store them in the list
            while (dataReader.Read())
            {
                list[0].Add(dataReader["id"].ToString() + "");
                list[1].Add(dataReader["month"].ToString() + "");
                list[2].Add(dataReader["year"].ToString() + "");
                list[3].Add(dataReader["page_count"].ToString() + "");
            }

            //close Data Reader
            dataReader.Close();

            //close Connection
            this.CloseConnection();

            //return list to be displayed
            return list;
        }
        else
        {
            return list;
        }
    }

However this code does not work, can someone advise me please?

EDITED:

string query = "SELECT * FROM page_counter where month = @month  AND year = @year;";
//Open connection
if (this.OpenConnection() == true)
{
//Create Command
 MySqlCommand cmd = new MySqlCommand(query, connection);
 cmd.Parameters.AddWithValue("@month",month);
 cmd.Parameters.AddWithValue("@year",year );

//Create a data reader and Execute the command
    MySqlDataReader dataReader = cmd.ExecuteReader();

//Read the data and store them in the list
    while (dataReader.Read())
    {
        list[0].Add(dataReader["id"].ToString() + "");
        list[1].Add(dataReader["month"].ToString() + "");
        list[2].Add(dataReader["year"].ToString() + "");
        list[3].Add(dataReader["page_count"].ToString() + "");
    }  

    //close Data Reader
    dataReader.Close();

I have edited the code as suggested,However I have an error on the AddWithValue, it say : does not contain a definition for AddWithValue and no extension method AddWithValue, I have added the Data.MySqlClient reference but still remain the same. Please advise.

is it asp.net or winform??

Saghir A. Khatri
– Saghir A. Khatri

2014-02-27 03:50:32 +00:00
Commented Feb 27, 2014 at 3:50 — Saghir A. Khatri
– Saghir A. Khatri, Commented Feb 27, 2014 at 3:50
@SaghirA.Khatri It is winform c#.

Ren
– Ren

2014-02-27 05:20:22 +00:00
Commented Feb 27, 2014 at 5:20 — Ren
– Ren, Commented Feb 27, 2014 at 5:20

Sudhakar Tillapudi · Accepted Answer · 2014-02-27 05:41:03Z

1

Problem 1 : You need to use SelectedItem property of the combobox to get the selected item from it.

Solution 1:

Replace This:

list = dbConnect.Select(month_list.Text, year_list.Text);

With This:

list = dbConnect.Select(month_list.SelectedItem.ToString(),        
                         year_list.SelectedItem.ToString());

Problem 2:

i beilve that your Month and Year columns in your Database are INT columns.if they are INT columns you dont need to enclose the month and year parameter values within single quotes.

Solution 2:

Try This:

string query = "SELECT * FROM page_counter where month =  
                   " + month + " AND year =" + year + " ;";

Suggestion : Your query is open to sql injection attacks i'd suggest to use Parameterised queries to avoid them.

Try This with Parameterised queries:

string query = "SELECT * FROM page_counter where month = @month  AND year = @year;";
//Open connection
if (this.OpenConnection() == true)
{
  //Create Command
  MySqlCommand cmd = new MySqlCommand(query, connection);
  cmd.Parameters.AddWithValue("@month",month);
  cmd.Parameters.AddWithValue("@year",year );

       //Remaining same

        //Create a data reader and Execute the command
        MySqlDataReader dataReader = cmd.ExecuteReader();

        //Read the data and store them in the list
        while (dataReader.Read())
        {
            list[0].Add(dataReader["id"].ToString() + "");
            list[1].Add(dataReader["month"].ToString() + "");
            list[2].Add(dataReader["year"].ToString() + "");
            list[3].Add(dataReader["page_count"].ToString() + "");
        }

        //close Data Reader
        dataReader.Close();

edited Feb 27, 2014 at 5:41

answered Feb 27, 2014 at 3:58

Sudhakar Tillapudi

26.3k5 gold badges43 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ren Over a year ago

Why I have an error on AddWithValue? and should I remain the MysqlDataReader line?

Sudhakar Tillapudi Over a year ago

@Ren; yes you have to keep the remaning same, check my edited answer.

Ren Over a year ago

How about the AddWithValue error?any idea why i receive the error?

Sudhakar Tillapudi Over a year ago

@Ren: i don't have an idea about which error you are talking about.could you please edit your question with error?

Ren Over a year ago

It say'MySql.Data.MySqlClient.MySqlParameterCollection' does not contain a definition for 'AddWithValue' and no extension method 'AddWithValue' accepting a first argument of type 'MySql.Data.MySqlClient.MySqlParameterCollection' could be found (are you missing a using directive or an assembly reference?)

Collectives™ on Stack Overflow

How to retrieve data from database based on selected value in combo box?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related