CODEWITHSUNDEEP
c#sql-serverwinformsdatagridview
icarus
icarus

Reputation: 166

updating a database through dataGridView

I have a datagridview in c# and I am filling it with whatever table from my database I want, getting the table name from a textbox. everything is fine until I try to commit the changes I do in the datagridview to the database.

button2 is the button I will be using for update. I've searched all over and apparently updating the adapter should work, but it doesn't in this case and I can't figure out why. here is my code:

public partial class Form1 : Form
{
    static string connstr = "DataSource=localhost;Database=sc2db;Trusted_Connection=True;";
    SqlConnection conn = new SqlConnection(connstr);

    public Form1()
    {
        InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        try
        {
            SqlDataAdapter adap = new SqlDataAdapter();
            DataTable dt = new DataTable();
            //BindingSource bs = new BindingSource();
            SqlCommand comm = new SqlCommand("select * from " + textBox1.Text, conn);
            adap.SelectCommand = comm;
            dataGridView1.DataSource = dt;
            adap.Fill(dt);
        }

        catch (Exception ex)
        {
            label1.Text = ex.Message;
        }
    }

    private void button2_Click(object sender, EventArgs e)
    {
        try
        {
            SqlDataAdapter adap = new SqlDataAdapter();
            DataTable dt = new DataTable();
            BindingSource bs = new BindingSource();
            adap.Update(dt);
        }

        catch (Exception ex)
        {
            label1.Text = ex.Message;
        }
    }
}

Upvotes: 0

Views: 4099

Answers (2)

user3284126
user3284126

Reputation: 55

private void button3_Click(object sender, EventArgs e)
        {
            scb = new SqlCommandBuilder(sqlDataAdapter1);
            sqlDataAdapter1.Update(dataSetUser);
        }

im able to edit my database from gridview by this code

Upvotes: 0

King King
King King

Reputation: 63317

Your adap in the button2_Click event handler doesn't have any Command (SelectCommand, DeleteCommand, InsertCommand and UpdateCommand) initialized. So how it can update?

You obviously don't understand how a DataAdapter works. The DataAdapter has 4 Command which are SelectCommand, DeleteCommand, InsertCommand, UpdateCommand. The SelectCommand is required for the method Fill() to work. Other commands are required for the method Update() works, of course in the general case which the DataRows can have all kinds of state: DataRowState.Deleted, DataRowState.Added, DataRowState.Modified. The SelectCommand is also required to build other commands automatically using a CommandBuilder (in SqlClient, that's SqlCommandBuilder). Once you pass a DataTable in the Update() method, all the rows in that table will be browsed through and the state of each DataRow will be checked to see if that row is added, deleted or modified and the adapter will call the corresponding Command (InsertCommand, DeleteCommand and UpdateCommand) it has to perform the actual operation to the database.

In your case, you can try declaring your adap as public and build other commands for it by using SqlCommandBuilder before calling the method Update() like this:

SqlCommandBuilder cb = new SqlCommandBuilder(adap);

Remember that using SqlCommandBuilder() will work only for single table in the Select query, if you have more than 1 table involved you have to build your own Commands for your DataAdapter. That's not difficult, you may want to search more about how to build commands for a DataAdapter.

I hope that helps you get started!

Upvotes: 1

Related Questions