i cant add data from sql to list of string

Question

In this program I try to get data from SQL into a list of strings and show them in a messageBox. The program should start searching when I type one character in textBox and use this in the query as below:

string sql = " SELECT * FROM general WHERE element='" + textBox1.Text + "'  OR element='" + textBox2.Text + "' OR element='" + textBox3.Text + "' OR element='" + textBox4.Text + "'";


        MySqlConnection con = new MySqlConnection("host=localhost;user=mate;password=1234;database=element_database");
        MySqlCommand cmd = new MySqlCommand(sql, con);
        con.Open();


        MySqlDataReader reader = cmd.ExecuteReader();
        string rd;
        rd = reader.ToString();

        int i=0;
        List<string>  item = new List<string>();


        while (reader.Read())
        {
            item.Add(rd["element"].ToString());//i got error in this line


        }

        for (i = 0; i < item.Count;i++ )
        {
            MessageBox.Show(item[i]);

        }

What am I doing wrong?

Use parameters instead + textbox.text +! Probably you Element is null and can't be converted toString. Check the data on the table. — Jaime García Pérez, May 20 '15 at 06:54
[check this][1] [1]: http://stackoverflow.com/questions/8370927/how-do-i-loop-through-rows-with-a-data-reader-in-c I am also learning C# and it helps me understand the looping. — Almas Mahfooz, May 20 '15 at 07:20

Zohar Peled · Accepted Answer · 2015-05-20T07:08:27.473

What are you doing wrong? a bunch of things:

In your question you write you gen an error but don't tell us what it is.
Exceptions has messages for a reason: so that you will be able to know what went wrong.

As to your code:

You are concatenating values into your select statement instead of using parameterized queries. This creates an opening for sql injection attacks.
You are using an SqlConnection outside of a using statement.
You should always use the using statement when dealing with IDisposable objects.
You assume that rd["element"] always have a value.
If it returns as null from the database, you will get a null reference exception when using .ToString() on it. The proper way is to put it's value into a local variable and check if this variable is not null before using the .ToString() method.
You are using rd instead of reader in your code. the rd variable is meaningless, as it only contain the string representation of MySqlDataReader object.

Consider my answer as constructive criticism. Every programmer was a newbie once :-) — Zohar Peled, May 20 '15 at 07:48

score 0 · Answer 2 · answered May 20 '15 at 07:02

0

You have declared rd as string. You probably meant to use the reader object in this loop instead:

while (reader.Read())
    {
        item.Add(reader["element"].ToString());// change "rd" to "reader"
    }

answered May 20 '15 at 07:02

Peter Henell

2,416
1
17
24

Ricky Alexandersson · Answer 3 · 2015-05-20T14:21:51.210

I took the liberty of modifing the SQL to use an IN instead of multiple or statements as well as including the use of parameter into the query and not a string based approach. This should solve your problems.

    string elem1 = "@elem1";
    string elem2 = "@elem2";
    string elem3 = "@elem3";
    string elem4 = "@elem4";
    List<string> parameters = new List<string>{ elem1, elem2, elem3, elem4 };

    string sql = string.Format(" SELECT * FROM general WHERE element IN ({0})", string.Join(',', parameters.ToArray()));

    using(MySqlConnection con = new MySqlConnection("host=localhost;user=mate;password=1234;database=element_database"))
    {
        con.Open();
        MySqlCommand cmd = new MySqlCommand(sql, con);
        cmd.Parameters.AddWithValue(elem1, textBox1.Text);
        cmd.Parameters.AddWithValue(elem2, textBox2.Text);
        cmd.Parameters.AddWithValue(elem3, textBox3.Text);
        cmd.Parameters.AddWithValue(elem4, textBox4.Text);

        MySqlDataReader reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            string message = reader["element"] as string;
            if(!string.IsNullOrEmpty(message))
            {
                MessageBox.Show(message);
            }
        }
    }

invalid expression term string at List<> and invalid expression term 'params' — Jet Mutt, May 20 '15 at 12:17
You are perfectly right, can't name a list to params. My apologize. The post has now been corrected. — Ricky Alexandersson, May 20 '15 at 13:14
that got some Error here 'string.Join(string, params string[])' has some invalid arguments — Jet Mutt, May 20 '15 at 14:03
Oops, my bad. Forgot that string.Join don't take a list as an argument. it needs to be an array. — Ricky Alexandersson, May 20 '15 at 14:21

i cant add data from sql to list of string

3 Answers3