Datagridview only adding 1 row

Question

Im trying to add all my users accounts to a gridview but when using the foreach code it is only adding the last value of the datagridview. Is there a way to do all of them?

public DataTable GetResultsTable(string Username)
{
    using (SqlDatabaseClient client = SqlDatabaseManager.GetClient())
    {
        DataRow row = client.ExecuteQueryRow("SELECT * FROM users WHERE username = '" + Username + "'");
        DataTable table = new DataTable();
        table.Columns.Add("Username".ToString());
        table.Columns.Add("Motto".ToString());
        table.Columns.Add("Email".ToString());
        table.Columns.Add("Homeroom".ToString());
        table.Columns.Add("Health".ToString());
        table.Columns.Add("Energy".ToString());
        table.Columns.Add("Age".ToString());
        DataRow dr = table.NewRow();
        dr["Username"] = "" + row["username"] + "";
        dr["Motto"] = "" + row["motto"] + "";
        dr["Email"] = "" + row["mail"] + "";
        dr["Homeroom"] = "" + row["home_room"] + "";
        dr["Health"] = "" + row["health"] + "";
        dr["Energy"] = "" + row["energy"] + "";
        dr["Age"] = "" + row["age"] + "";
        table.Rows.Add(dr);
        return table;
    }
}
SqlDatabaseManager.Initialize();
  using (SqlDatabaseClient client  = SqlDatabaseManager.GetClient())
  foreach (DataRow row2 in client.ExecuteQueryTable("SELECT * FROM users").Rows)
  {
      dataGridView1.DataSource = GetResultsTable((string)row2["username"]);
  }

Parameterize your queries. This is a big no-no: `"SELECT * FROM users WHERE username = '" + Username + "'"`. See here for why: http://stackoverflow.com/questions/5468425/how-do-parameterized-queries-help-against-sql-injection — Derek W, Dec 15 '13 at 15:58

score 0 · Answer 1 · answered Dec 15 '13 at 15:55

0

The DataSource property of a DataGridView object shouldn't be one row, but many. So you can change your code to create a list first and apply this as the DataSource, OR you use the following method:

dataGridView1.Rows.Add(GetResultsTable(row2["username"].ToString());

In the foreach loop.

Hope it helps you out :).

answered Dec 15 '13 at 15:55

Stefan Koenen

2,289
2
20
32

I have a error when using your code. is there any simple fix? An unhandled exception of type 'System.InvalidOperationException' occurred in System.Windows.Forms.dll Additional information: No row can be added to a DataGridView control that does not have columns. Columns must be added first. – Fernando Dec 15 '13 at 16:00

score 0 · Answer 2 · answered Dec 15 '13 at 16:08

you can use one method to load the data

public DataTable fillDataTable()
{
    // select all the columns with expected column name, here I only added 3 columns 
    string query = "SELECT username as Username, motto as Motto, mail as Email FROM users;

    using(SqlConnection sqlConn = new SqlConnection(conSTR))
    using(SqlCommand cmd = new SqlCommand(query, sqlConn))
    {
        sqlConn.Open();
        DataTable dt = new DataTable();
        dt.Load(cmd.ExecuteReader());
        return dt;
    }
}

set the grid DataSource like below

dataGridView1.DataSource =fillDataTable();

Datagridview only adding 1 row

2 Answers2