CODEWITHSUNDEEP
c#sqlstring
Jet Mutt
Jet Mutt

Reputation: 1

i cant add data from sql to list of string

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?

Upvotes: -1

Views: 128

Answers (3)

Ricky Alexandersson
Ricky Alexandersson

Reputation: 94

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);
            }
        }
    }

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82524

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.

Upvotes: 1

Peter Henell
Peter Henell

Reputation: 2466

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"
    }

Upvotes: 0

Related Questions