How can I add a new DB row and then edit it using a GridView control?

Question

In my application I am using a grid view control.

I am creating a new empty row in my grid view control using the following code:

string co = ConfigurationSettings.AppSettings["MyLogin"];
            SqlConnection connection = new SqlConnection(co);
            connection.Open();                
string sql = "insert into TeamMember_table "
          + "values('','','','','','','','','','','','')";                
SqlDataAdapter dp = new SqlDataAdapter(sql, connection);                
DataSet ds = new DataSet();                
dp.Fill(ds);                
gridview1.DataBind();               
connection.Close();

The empty row was added successfully, but my problem is I want to enter values into this empty row and save this value to a table when I click my save button.

What is the best way to do this?

Answer 1

The first argument to a SqlDataAdapter is the SELECT statement to use; your code is adding a new database row every time the data is queried (which sounds very, very wrong), plus I anticipate the query itself won't really work due to the confusion over the INSERT vs SELECT.

You should really just be doing a SELECT from TeamMember_table, and then perhaps adding a row the the local data-table afterwards:

string sql = "SELECT * FROM TeamMember_table";
SqlDataAdapter dp = new SqlDataAdapter(sql, connection);                
DataSet ds = new DataSet();   
dp.Fill(ds);
DataTable table = ds.Tables[0];
table.Rows.Add("", "", ..., "")

Personally, I wouldn't add a fake row unless it was absolutely essential to the binding; I can't advise on that, I'm afraid... but you'll need to be careful that you don't add empty rows accidentally. Perhaps check on the post-back (assuming ASP.NET), and only save if it has something useful in it.

Answer 2

You will probably want to use events like RowDataBoudn and RowDataDelete to impliment these behaviors. For example:

protected void Page_Load(object sender, EventArgs e) {
    GridView1.RowDataBound += GridView1_RowDataBound;
    GridView1.RowUpdated += GridView1_RowUpdated;
    if (!IsPostBack) {
        string co = ConfigurationManager.ConnectionStrings["MyConnectionString"];
        using (SqlConnection connection = new SqlConnection(co)) {
            connection.Open();
            string sql = "select ....";
            SqlDataAdapter dp = new SqlDataAdapter(sql, connection);
            DataSet ds = new DataSet();
            dp.Fill(ds);

            GridView1.DataSource = ds.Tables["MyTable"];
            GridView1.DataKeyNames = new string[] { "Id" };
            GridView1.DataBind();
        }                
    }
}

void GridView1_RowUpdated(object sender, GridViewUpdatedEventArgs e) {
    throw new NotImplementedException();
}

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e) {
    if (e.Row.RowType == DataControlRowType.DataRow) {
        Label l = (Label)e.Row.FindControl("Label1");
        l.Text = "some text value";
    }
}