Crud Operations Using Datagridview, Datatable and Dataadapter - Cannot Add New Row to Datagridview

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

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.

How to INSERT added rows and UPDATE edited ones in a DataGridView?

When you insert an object in DataBase, generally your object don't have yet an Id. The object to be update must have their Id's existing yet. Now you should see if the object has an Id or Not. If the Id is Zero (or does not exist) then you insert it, otherwise you update it.

CRUD operations in Datagridview bound to SQL Database

Well, I'm not able do something like that
dataGridView1.Rows.Add(selected_row); It throws an exception. And I
don't know how to insert a row in my database. – user1651521 1 hour
ago

You can't add rows directly to the datagrid for a grid that is databound. You have to add the new row to the bound table.

So if you are using the same code as that example, you would add a new record to the DataTable that is the datasource of your bindingsource object. So something like this:

private void addButton_Click(object sender, System.EventArgs e)
{
    //I'm assuming your datatable is a member level variable 
    //otherwise you could get it through the grid
    //have the datatable send you back a new row
    DataRow newRow = table.NewRow();
    //populate your new row with default data here
    //....
    //add the new row to the data table
    table.Rows.Add(newRow);
}

Bind to DataGridView using a Stored Procedure

For anyone in future looking for how to get this done, I've attached the code I used to achieve this. This is based primarily on jmcilhinney examples posted here.

Module DataGridViewRetrieve
    Public db As New SqlConnection(lgnGetCredentials)
    Public sp As SqlCommand = db.CreateCommand
    Public sa As SqlDataAdapter = New SqlDataAdapter(sp)
    Public dt As DataTable = New DataTable()

    Public Sub initialiseDataAdapter()
        Dim dRecords As SqlCommand = db.CreateCommand
        Dim uRecords As SqlCommand = db.CreateCommand
        Dim iRecords As SqlCommand = db.CreateCommand

        ' # Initialise delete records Stored Procedure
        With dRecords
            .CommandText = "<NAME OF STORED PROCEDURE TO DELETE>"
            .CommandType = CommandType.StoredProcedure
            .Parameters.Clear()
            .Parameters.Add("@ID", SqlDbType.BigInt, 10, "<NAME OF ID COLUMN IN DataGridView>")
        End With

        ' # Initialise update records Stored Procedure
        ' Note - you'll need to add your parameters for each column in the below example
        With uRecords
            .CommandText = "<NAME OF STORED PROCEDURE TO UPDATE"
            .CommandType = CommandType.StoredProcedure
            .Parameters.Clear()
            .Parameters.Add("@dgID", SqlDbType.BigInt, 10, "<NAME OF ID COLUMN IN DataGridView>")
            .Parameters.Add("@Title", SqlDbType.Bit, 1, "<NAME OF COLUMN IN DataGridView>")
        End With

        ' # Initialise insert records Stored Procedure
        With iRecords
            .CommandText = "pl.x53_loadLifeListGoals_iRecords"
            .CommandType = CommandType.StoredProcedure
            .Parameters.Clear()
            .Parameters.Add("@dgID", SqlDbType.BigInt, 10, "<NAME OF ID COLUMN IN DataGridView>")
            .Parameters.Add("@Title", SqlDbType.Bit, 1, "<NAME OF COLUMN IN DataGridView>")
        End With

        ' # Set data adapter commands
        sa.DeleteCommand = dRecords
        sa.UpdateCommand = uRecords
        sa.InsertCommand = iRecords

        ' # Set missing schema action key
        sa.MissingSchemaAction = MissingSchemaAction.AddWithKey
    End Sub

    Public Sub loadData(TitleName As String)
        ' # Clears data
        FormName.DataGridViewName.DataSource = Null

        ' # Calls the initialise routine
        Call initialiseDataAdapter()

        ' # Initialise the SELECT Stored Procedure
        With sp
            .CommandText = "<NAME OF STORED PROCEDURE TO SELECT>"
            .CommandType = CommandType.StoredProcedure
            .Parameters.Clear()
            .Parameters.Add(New SqlParameter("@TitleName", TitleName))
        End With

        ' # Load data
        sa.Fill(dt)
        FormName.BindingSourceName.DataSource = dt
        FormName.DataGridViewName.DataSource = FormName.BindingSourceName
    End Sub

    Public Sub saveData()
        sa.Update(FormName.BindingSourceName.DataSource)
    End Sub
End Module

'// ATTACH YOUR LOAD CALL TO YOUR EVENT HANDLER - IN THIS INSTANCE, FORM LOAD
Private Sub Form1_Load(sender as object, e as EventArgs) Handles Me.Load
    Call loadData(Me.txtTitle.Text)
End Sub

Private Sub ButtonSave_Click(sender as object, e as EventArgs) Handles Me.ButtonSave
    Call saveData()
End Sub

No data is being display in the gridview

Use DataAdapter to fill the DataTable.

 private void Form1_Load(object sender, EventArgs e)
        {
            DataTable dataTable= new DataTable();
            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
              con.Open();
              SqlCommand cmd = new SqlCommand("Select * from tblProductInventory", con);               
              SqlDataAdapter adapter = new SqlDataAdapter(cmd);
              adapter.Fill(dataTable);                    
              dataGridView1.DataSource = dataTable;             
            }  
       }

C# save changes in datagridview

This should do what you want.

using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        SqlCommand sCommand;
        SqlDataAdapter sAdapter;
        SqlCommandBuilder sBuilder;
        DataSet sDs;
        DataTable sTable;        

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string connectionString = "Data Source=.;Initial Catalog=pubs;Integrated Security=True";
            string sql = "SELECT * FROM Stores";
            SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();
            sCommand = new SqlCommand(sql, connection);
            sAdapter = new SqlDataAdapter(sCommand);
            sBuilder = new SqlCommandBuilder(sAdapter);
            sDs = new DataSet();
            sAdapter.Fill(sDs, "Stores");
            sTable = sDs.Tables["Stores"];
            connection.Close();
            dataGridView1.DataSource = sDs.Tables["Stores"];
            dataGridView1.ReadOnly = true;
            save_btn.Enabled = false;
            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
        }

        private void new_btn_Click(object sender, EventArgs e)
        {
            dataGridView1.ReadOnly = false;
            save_btn.Enabled = true;
            new_btn.Enabled = false;
            delete_btn.Enabled = false;
        }

        private void delete_btn_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Do you want to delete this row ?", "Delete", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {
                dataGridView1.Rows.RemoveAt(dataGridView1.SelectedRows[0].Index);
                sAdapter.Update(sTable);
            }
        }

        private void save_btn_Click(object sender, EventArgs e)
        {
            sAdapter.Update(sTable);
            dataGridView1.ReadOnly = true;
            save_btn.Enabled = false;
            new_btn.Enabled = true;
            delete_btn.Enabled = true;
        }
    }
}

C# SqlCommandBuilder , CommandUpdate - how to write correct update based on select with outer join tables

I would solve the problem by changing the approach instead of mutating the update command of the SqlDataAdapter.

Given that Products.id in your query is unique within the result set:

1- Create a temporary table (local or global), having its columns same as the result of the query with id as primary key.

2- Insert data into the temporary table using your select statement.

3- DataAdatper.selectQuery.commandText is set to "select * from TempTable"

4- The update command is now based on a simple select statement, consequently any change in the datagridview/datatable can be updated to the temptable using dataadapter.update(datatable)

5- As for the final database update, you could use the below statement

Update Prd
set Prd.FlagaWaznosci = TempTable.FlagaWaznosci ,Prd.Notatka = TempTable.Notatka  etc.. all the fields that need to be updated  
from my Products as Prd 
Inner Join TempTable on TempTable.id = Prd.id

Note that the update in (5) will affect all rows, even unchanged ones.
To address this issue you can proceed as below

1- Save changed ids in a list.

List<string> lst = new List<string>();
foreach(DataRow dr in datatable.GetChanges(DataRowState.Modified))
{
 lst.add(dr["id"].ToString());
}

2- Convert your list to a string value to be concatenated with the query in (5)

 String strchange = String.Join(",",lst); //will give you id1,id2,...
 //The update query becomes
 Update Prd
 set Prd.FlagaWaznosci = TempTable.FlagaWaznosci ,Prd.Notatka = 
 TempTable.Notatka  etc.. all the fields that need to be updated  
 from my Products as Prd 
 Inner Join TempTable on TempTable.id = Prd.id
 Where Prd.id In ( strchange ) 

---

Related Topics