To modify a DataGridview as well as save it to a database table using C #

Question

I am using MYSQL Server as my backend project. I have a DataGridView that is populated with data from a database. when I make changes to the DataGridView cells and I click saveButton, the data must be changed in the DataGridView as well as in the database table.

here is my coding:

using MySql.Data.MySqlClient; using System; using System.Data; using System.IO; using System.Windows.Forms; namespace datagridview { public partial class Form1 : Form { DataTable datatab; MySqlDataAdapter mydtadp; MySqlCommandBuilder cmbl; String MyConnection = "SERVER=*****;" + "DATABASE=****;" + "UID=root;" + "PASSWORD=pws"; public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { MySqlConnection MyConn = new MySqlConnection(MyConnection); MyConn.Open(); MySqlCommand comand = new MySqlCommand("select * from aster_scripts;", MyConn); datatab = new DataTable(); mydtadp = new MySqlDataAdapter(comand); mydtadp.Fill(datatab); dataGridView1.DataSource = datatab; MyConn.Close(); } private void BtnSave_Click(object sender, EventArgs e) { DataSet ds = new DataSet(); cmbl = new MySqlCommandBuilder(mydtadp); mydtadp.Update(datatab); MessageBox.Show("SAVED"); } } } 

I had an error like this (Additional information: Dynamic SQL generation for UpdateCommand is not supported using SelectCommand, which does not return any information about a column of a column.) In the string mydtadp.Update (datatab); How can I do that?

Answer 1

Pay attention to the module level variables. And refer to this part of MSDN here , if necessary.

scheme

 create table aster_scripts ( id int auto_increment primary key, i int not null, sThing varchar(30) not null ); insert aster_scripts (i,sThing) values (8,'frog'),(11,'cat'); 

WITH#

 using System; using System.Data; using System.Windows.Forms; using MySql.Data.MySqlClient; using System.IO; namespace WindowsFormsApplication1 { public partial class Form2 : Form { private MySqlDataAdapter mydtadp = new MySqlDataAdapter(); private BindingSource bindingSource1 = new BindingSource(); MySqlCommandBuilder cmbl; String MyConnection = "SERVER=hostname;" + "DATABASE=dbname;" + "UID=dbuser;" + "PASSWORD=fffff"; public Form2() { InitializeComponent(); } private void Form2_Load(object sender, EventArgs e) { MySqlConnection MyConn = new MySqlConnection(MyConnection); MyConn.Open(); mydtadp.SelectCommand=new MySqlCommand("select * from aster_scripts", MyConn); cmbl = new MySqlCommandBuilder(mydtadp); DataTable table = new DataTable(); mydtadp.Fill(table); bindingSource1.DataSource = table; dataGridView1.DataSource = bindingSource1; } private void BtnSave_Click(object sender, EventArgs e) { mydtadp.Update((DataTable)bindingSource1.DataSource); MessageBox.Show("SAVED"); } } } 

Chg data hit save Screen Shot

DB Records

 mysql> select * from aster_scripts; +----+----------+--------+ | id | i | sThing | +----+----------+--------+ | 1 | 8 | frog | | 2 | 11333322 | cat | +----+----------+--------+ 

Answer 2

You may need to update your SQL query:

 select * from aster_scripts); 

be

 select * from aster_scripts 

In the request, you included the closing parenthesis ')'.