CODEWITHSUNDEEP
c#sqlsql-server-express
novak100
novak100

Reputation: 1469

Read column from Sql and populate to listbox

I'm having problem with my sql query.

SqlConnection conn = new SqlConnection();
        conn.ConnectionString = "Data Source=.\\SQLExpress;" + "Trusted_Connection=True;" + "User Instance=True;" + "AttachDbFilename=|DataDirectory|\\fbi.mdf;";
        string sqlQuery4 = "SELECT Car FROM tbl1 JOIN tbl2 ON (tbl1.userID = tbl2.userID) WHERE tbl2.username='Bob'";
        SqlCommand cmd4 = new SqlCommand(sqlQuery4, conn);
        conn.Open();
        SqlDataReader rd = cmd4.ExecuteReader();
        rd.Read();
        ddl1.Items.Add(rd.GetValue(0).ToString());
        conn.Close();

So it should return all cars from tbl1 that belongs to Bob. Query only return one string and put it into Listbox "ddl1" while it should return at least 3 of them. Any ideas?

Upvotes: 0

Views: 8373

Answers (3)

Developer
Developer

Reputation: 3057

try below code:

SqlConnection conn = new SqlConnection();
        conn.ConnectionString = "Data Source=.\\SQLExpress;" + "Trusted_Connection=True;" + "User Instance=True;" + "AttachDbFilename=|DataDirectory|\\fbi.mdf;";
        string sqlQuery4 = "SELECT Car FROM tbl1 JOIN tbl2 ON (tbl1.userID = tbl2.userID) WHERE tbl2.username='Bob'";
        SqlCommand cmd4 = new SqlCommand(sqlQuery4, conn);
        conn.Open();
        SqlDataReader rd = cmd4.ExecuteReader();


                ddl1.DataSource = rd;
                ddl1..DataTextField = "columnname"; //your column name
                ddl1.DataValueField = "columnname";
                ddl1.DataBind();

                rd.Close();
                conn.Close();

Upvotes: 0

MikeSmithDev
MikeSmithDev

Reputation: 15797

Novak, not sure what your issue was with Curt's solution, as it is correct. Your complete statement should look like:

string connectionString= "Data Source=.\\SQLExpress;Trusted_Connection=True;User Instance=True;AttachDbFilename=|DataDirectory|\\fbi.mdf;";
string query = "SELECT Car FROM tbl1 JOIN tbl2 ON (tbl1.userID = tbl2.userID) WHERE tbl2.username='Bob'";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    using (SqlCommand command = new SqlCommand(query, connection))
    {
        connection.Open();
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                ddl1.Items.Add(reader.GetValue(0).ToString());
            }
        }
    }
}

Upvotes: 0

Curtis
Curtis

Reputation: 103388

You need to loop through the reader:

while (rd.Read()){
   ddl1.Items.Add(rd.GetValue(0).ToString());
}

Upvotes: 3

Related Questions