populating GridView with sql data

Question

I need to select from my users table the username of the user that has the roleID that I will have to get from the dropdownlist. The data are not appearing in the GridView. Can't see what's wrong, help me please. Already tried 2 ways

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
   {
       SqlConnection con = new SqlConnection(cs);
       con.Open();
       SqlCommand cmd = new SqlCommand("select username from tblUser where roleID like '" + DropDownList1.SelectedValue + "'", con);

       SqlDataAdapter da = new SqlDataAdapter(cmd);
       DataTable dt = new DataTable();
       da.Fill(dt);
       GridView2.DataSource = dt;
       GridView2.DataBind();
       con.Close();
   }

protected void Button2_Click(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection(cs);
    con.Open();
    SqlCommand cmd = new SqlCommand("select username from tblUser where roleID like '" + DropDownList1.SelectedValue + "'", con);

    SqlDataReader reader = cmd.ExecuteReader();

    GridView2.DataSource = reader;
    GridView2.DataBind();
    con.Close();
}

Have you tried to debug? If you directly run the query by hard-coding the roleID, do you get any results? — shree.pat18, Dec 05 '14 at 03:36
I can't see anywhere connection string to the database... Simple SQL command is not enough. — Yasskier, Dec 05 '14 at 03:36
@shree.pat18 yes. still don't get anything :( Kris you mean? SimonMᶜKenzie yes it has. — missellorin, Dec 05 '14 at 03:39
@Fel, did you set a breakpoint in your code to make sure it reaches your code? Do you get an exception? I know for sure you need to pass the connection string in to your `Sqlcommand`. You also need to use parameterized queries to avoid SQL Injection attacks. — mason, Dec 05 '14 at 04:24

score 2 · Accepted Answer · answered Dec 18 '14 at 07:23

Okay, so this one worked for me. And you also must check the sources. Like what happened to my GridView, it says AutoGenerateColumns = false, I removed it. And it all worked!

protected void Button2_Click(object sender, EventArgs e)
{
    string cs = ConfigurationManager.ConnectionStrings["roleDB"].ConnectionString;
    SqlConnection con = new SqlConnection(cs);
    con.Open();
    SqlCommand cmd = con.CreateCommand();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "select username from tblUser where roleID like '" + DropDownList1.SelectedValue + "'";
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);
    GridView2.DataSource = dt;
    GridView2.DataBind();
    con.Close();
}

I'm sorry but I'm going to have to correct your answer here Fel. Although the answer on how to use a DataGrid is great, your code is vulnerable to SQL injection. You're concatenating a query string with user input (DropDownList1 value). For more info check: https://www.owasp.org/index.php/SQL_Injection — Ruben, Dec 21 '15 at 10:25

populating GridView with sql data

1 Answers1

Linked