Updating Datagridview data using SQLite Database

Question

I'm having trouble updating data from a data grid view with the use of a button. The text is editable but the changes does not save to the SQLite database. any ideas?

    private void ProjectsAdmin_Load(object sender, EventArgs e)
    {
        // TODO: This line of code loads data into the 'seniorProjectsDataSet2.DataTable1' table. You can move, or remove it, as needed.
        this.dataTable1TableAdapter.Fill(this.seniorProjectsDataSet2.DataTable1);
    }


    private void dataGridView1_CellContentClick_1(object sender, DataGridViewCellEventArgs e)
    {
        if (e.RowIndex == -1 || e.ColumnIndex != 3)  //ignore header row and any column that doesnt have file name
            return;

        var filename = dataGridView1.CurrentCell.Value.ToString();

        if (File.Exists(filename))
            Process.Start(filename);

    }



    private void updateData_Click(object sender, EventArgs e)
    {

        SQLiteConnection conn = new SQLiteConnection();
        dataGridView1.EndEdit();

        dataTable1TableAdapter.Adapter.Update(seniorProjectsDataSet.Tables[0]);


        for (int i = 0; i < seniorProjectsDataSet.Tables[0].Rows.Count; i++)
        {
            seniorProjectsDataSet.Tables[0].Rows[i].AcceptChanges();

        }

    }
}

}

Xeidos · Accepted Answer · 2014-10-27 12:56:28Z

I solved the problem without a Button. In the following code I´ll give you a example how the connection and the update works with a mysql-database (update in runtime):

CODE

    DataTable dt = null;
    DataGridView dgv1 = null;

If the form load you have to set your dt variable to a new datatable:

        private void Form1_Load(object sender, EventArgs e)
    {
        dt = new DataTable();



        using (MySqlConnection conn = new MySqlConnection("datasource=localhost;port=3306;username=root;password=1234"))
        {
            conn.Open();
            using (MySqlCommand cmd = new MySqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandText = "select *from try.data ;";
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(dt);
            }
            conn.Close();
        }

        dgv1 = new DataGridView();
        dgv1.AllowUserToAddRows = false;
        dgv1.CellEndEdit += new DataGridViewCellEventHandler(dgv_CellEndEdit);
        dgv1.CellValidating += new DataGridViewCellValidatingEventHandler(dgv_CellValidating);
        dgv1.Dock = DockStyle.Fill;
        dgv1.DataSource = dt;

        this.Controls.Add(dgv1);
    }

You have to set two events: CellValidating

   private void dgv_CellValidating(object sender, DataGridViewCellValidatingEventArgs e)
    {
        InitializeComponent();
        if (e.ColumnIndex == 0)
        {
            dgv1.CancelEdit();
        }
    }

and the CellValidating Event:

   private void dgv_CellEndEdit(object sender, DataGridViewCellEventArgs e)
    {
        string id = dt.Rows[e.RowIndex]["Eid"] + "";
        string col = dt.Columns[e.ColumnIndex].ColumnName;
        string data = dgv1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value + "";

        string sql = string.Format("UPDATE `try`.`data` SET `{0}` = '{1}' WHERE Eid = {2};", col, data, id);

        using (MySqlConnection conn = new MySqlConnection("datasource=localhost;port=3306;username=root;password=1234"))
        {
            conn.Open();
            using (MySqlCommand cmd = new MySqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }
            conn.Close();
        }
    }

This aktually works with MySql but in Sql are "Equal" components like the SqlConnection or the SqlCommand... I hope this solve you problem. Have a nice day!

I understand your solution, but my datagridview is displayed by a binding source with JOINS from 3 different tables. I am going to somehow implement your solution to fit my problem. Thanks.
Okay. The binding source actually don´t matter for this solution. The code is nearly the same.I hope you can solve the problem with this solution. If it will please accept the answer.

Shaneless · Accepted Answer · 2022-02-22 15:21:02Z

0

using System.Data.SQLite;

SQLiteConnection con = new SQLiteConnection("Data Source=C:\\Cogs\\bin\\Release\\db\\my_database_file.db");
SQLiteCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from [my_table]";
con.Open();
cmd.ExecuteNonQuery();
DataTable dta = new DataTable();
SQLiteDataAdapter dataadp = new SQLiteDataAdapter(cmd);
dataadp.Fill(dta);
dataGridView1.DataSource = dta;
con.Close();

answered Feb 22, 2022 at 15:21

Shaneless

114 bronze badges

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Updating Datagridview data using SQLite Database

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related