Is my logic correct? I'm trying to get data from combobox(dropdownlist) and use that data to search and display information in checkedlistbox

Question

I keep getting this error whenever I do the search: Invalid column name 'datafromcombobox'

I tried hardcode the data in my SQL query and it works. Any idea what's wrong with my code?

Here's the code:

        string selectedMedication = cboMedicationType.SelectedItem.ToString();

        string strMedications = "SELECT medicationName FROM MEDICATION WHERE medicationType= (" + selectedMedication + ")";
        SqlCommand cmdMedications = new SqlCommand(strMedications, connection);

        connection.Open();
        SqlDataReader readMedications = cmdMedications.ExecuteReader();

        while (readMedications.Read())
        {
            string medicationVar = readMedications["medicationName"].ToString();
            clbMedication.Items.Add(medicationVar, true);

        }
        readMedications.Close();
        connection.Close();

score 0 · Accepted Answer · answered Jan 27 '14 at 13:26

0

I think you just forgot the quotes in your query. This should work :

string strMedications = "SELECT medicationName FROM MEDICATION WHERE medicationType=   ('" + selectedMedication + "')";

answered Jan 27 '14 at 13:26

MathB.Fr

292
1
6

score 0 · Answer 2 · edited May 23 '17 at 12:28

I would do it this way:

using (SqlCommand cmdMedications = new SqlCommand(strMedications, connection))
{
    string strMedications = "SELECT medicationName FROM MEDICATION WHERE medicationType = @selectedMedication;
    command.Parameters.AddWithValue("selectedMedication", cboMedicationType.SelectedItem.ToString());
    connection.Open();
    using (SqlDataReader readMedications = cmdMedications.ExecuteReader())
    {
        while (readMedications.Read())
        {
            string medicationVar = readMedications["medicationName"].ToString();
            clbMedication.Items.Add(medicationVar, true);
        }
    }
    connection.Close();
}

I changed several things:

Always use parameterized queries. If you don't, you can be a victim of SQL Injection. I changed that in your code.
It's usually better to use using in your code (see this article), because then you don't need to close or dispose objects--the using statements take care of that.
As for your error, it's probably an error in your query, but my code should take care of that.

Let me know if I can clear up something else. I hope this helps!

P.S. I mostly typed this without debugging so there might be some small things you might have to tweak, but I thing you should get the gist of it.

Is my logic correct? I'm trying to get data from combobox(dropdownlist) and use that data to search and display information in checkedlistbox

2 Answers2