CRUD Operations using DataGridView, DataTable and DataAdapter - Cannot add new row to DataGridView

I'm trying to insert new records into the source table from the C# interface grid view.... But when I retrieve the records with the buttonclick code shown below... Im getting records in the gridview but no option for inserting new records(screen shot attached).. where as i can update the reocrds from the grid view.

Is there any option or property for enabling the insertion option in the gridview ?

Buttonclickcode :

private void RetrieveRules_button_Click(object sender, EventArgs e)
{
    this.dataGridView.DataSource = null;
    this.dataGridView.Rows.Clear();


    SqlCommand cmd1 = con.CreateCommand();
    cmd1.CommandType = CommandType.Text;
    cmd1.CommandText = @" Select TOP 1 * FROM " + schemaName + "[ERSBusinessLogic] ORDER BY ERSBusinessLogic_ID     DESC";
    con.Open();
    cmd1.ExecuteNonQuery();
    DataTable dt = new DataTable();
    SqlDataAdapter DA = new SqlDataAdapter(cmd1);
    DA.Fill(dt);
    dataGridView.DataSource = dt;
    con.Close();

}

Thanks

Solution 1:

CRUD Operations using DataGridView, DataTable and DataAdapter

To let the user add, delete or edit rows with DataGridView:

1. Set AllowUserToAddRows property to true or in DataGridView Tasks, check Enable Adding
2. Set AllowUserToDeleteRows property to true or in DataGridView Tasks, check Enable Deleting
3. Set ReadOnly property to false or in DataGridView Tasks, check Enable Editing

To let the user save changes using a SqlDataAdapter:

1. Create a SqlDataAdapter using a select statement and a connection string.
2. You should have valid InsertCommand, DeleteCommand and UpdateCommand for your data adapter. Create valid commands using a SqlCommandBuilder.
3. Load data to a DataTable using data adapter.
4. Set data table as DataSource of DataGridView
5. Save changes when you need using SqlDataAdapter.Update by passing data table to the method.

Code

DataTable table;
SqlDataAdapter adapter;
private void Form1_Load(object sender, EventArgs e)
{
    //Create adapter
    var connection = @"your connection string";
    var command = "SELECT * FROM Table1";
    adapter = new SqlDataAdapter(command, connection);

    //Create Insert, Update and Delete commands
    var builder = new SqlCommandBuilder(adapter);

    //Load data
    table = new DataTable();
    adapter.Fill(table);

    //Bind the grid to data
    this.dataGridView1.DataSource = table;

    //Enable add, delete and edit
    this.dataGridView1.AllowUserToAddRows = true;
    this.dataGridView1.AllowUserToDeleteRows = true;
    this.dataGridView1.ReadOnly = false;
}

private void saveButton_Click(object sender, EventArgs e)
{
    //Save Data
    adapter.Update(table);
}

private void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
    adapter.Dispose();
}

Note

• You don't need that ExecuteNonQuery. You only need a connection string and a command text. Then you can create a data adapter. Then you even don't need to manage opening and closing the connection, data adapter manages it.
• When you load data using SELECT TOP 1 *, if you add data and save, you can't see updates next time you load the data because you load only a single record.

Solution 2:

Code is using ExecuteNonQuery, it returns no of rows affected not the results your query returns.

Use ExecuteReader instead.

var reader = cmd1.ExecuteReader();
DataTable dt = new DataTable();
dtLoad(reader);

Is there any option or property for enabling the insertion option in the gridview

dataGridView1.ReadOnly = false;
dataGridView1.EditMode = DataGridViewEditMode.EditOnF2; //if you want to edit on F2.