How to add values to listbox from another listbox using SQL query?

Question

User clicks a btnAdd and it transfers items to a listBox1. Now I want to create a query that creates a loop from the listBox1 to SELECT FROM a table from SQL and add the result items to listBox2

I have this sample code but it's not working. Can someone help me?

public void add()
{
    var con = new DBConnection();
    try
    {
        for (int i = 0; i < listBServices.Items.Count; i++)
        {
            SqlCommand cmd = new SqlCommand("SELECT price FROM price WHERE service = '" +
                listBServices.Items.ToString() + "';", con.Connection);
            SqlDataReader rd = cmd.ExecuteReader();

            while (rd.Read())
            {
                int price = rd.GetInt32(0);
                listBPrice.Items.Add(price.ToString());
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

I get this exeption:

Olivier Jacot-Descombes · Accepted Answer · 2019-02-10 15:44:20Z

1

listBServices.Items.ToString() results in the string "System.Windows.Forms.ListBox+ObjectCollection". You must use

SqlCommand cmd = new SqlCommand("SELECT price FROM price WHERE service = '" + 
                                listBServices.Items[i] + "'",
                                con.Connection);

But using string concatenation is not a good idea. Use parameters instead.

SqlCommand cmd = new SqlCommand("SELECT price FROM price WHERE service = @svc",
                                con.Connection);
cmd.Parameters.Add("@svc", SqlDbType.NVarChar).Value = listBServices.Items[i];

answered Feb 10, 2019 at 15:44

Olivier Jacot-Descombes

114k14 gold badges149 silver badges202 bronze badges

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

2 Comments

vicserna1997 Over a year ago

Hi thanks for your answer. But unfortunately I get the Specified cast is not valid exception.

Olivier Jacot-Descombes Over a year ago

The type of the parameter must be the same as the type of the column in the table. E.g. if the column is an int (probably a service id), then you must use SqlDbType.Int and you must assign the parameter value as int. If the listbox contains a service name, this will not work. If it contains a service object, then cast it to this type and get the id: cmd.Parameters.Add("@svc", SqlDbType.Int).Value = ((ServiceClass)listBServices.Items[i]).ServiceID;. To add service objects directly to the ListBox, override ToString in the class to let it display the service name.

abestrad · Accepted Answer · 2019-02-10 15:30:34Z

Check your connection and use the Using code block to close the connection automatically.

 string str = "Data Source=(local);Initial Catalog=Northwind;"
        + "Integrated Security=SSPI";
 string queryString =
        "SELECT price FROM price WHERE service ... ";

    using (SqlConnection connection =
               new SqlConnection(connectionString))
    {
        SqlCommand command =
            new SqlCommand(queryString, connection);
        connection.Open();

        SqlDataReader reader = command.ExecuteReader();

        // Call Read before accessing data.
        while (reader.Read())
        {
            ReadSingleRow((IDataRecord)reader);
        }

        // Call Close when done reading.
        reader.Close();
    }

MSL · Accepted Answer · 2019-02-10 15:38:21Z

You should close the reader after while

public void add()
{
    var con = new DBConnection();
    try
    {
        for (int i = 0; i < listBServices.Items.Count; i++)
        {
            SqlCommand cmd = new SqlCommand("SELECT price FROM price WHERE service = '" +
                listBServices.Items.ToString() + "';", con.Connection);
            SqlDataReader rd = cmd.ExecuteReader();

            while (rd.Read())
            {
                int price = rd.GetInt32(0);
                listBPrice.Items.Add(price.ToString());
            }

            rd.Close();
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

Collectives™ on Stack Overflow

How to add values to listbox from another listbox using SQL query?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related