C# How save data entered into datagridview?
I have these two methods that correctly populate a datagridview. The question is what do I need to add to get any changes committed to the SQL tables in the database? I can change values in the grid but those changes are not being committed to the database.
private void InitializeDataGridView()
{
try
{
// Set up the data source.
bindingSourceStations.DataSource = GetData("SELECT [STATIONID] AS 'Station ID',[STATNAME] AS 'Station Name' FROM [WXDATA].[dbo].[STATIONS]");
dgvStations.DataSource = bindingSourceStations;
// Automatically resize the visible rows.
dgvStations.AutoSizeRowsMode =
DataGridViewAutoSizeRowsMode.DisplayedCellsExceptHeaders;
// Set the DataGridView control's border.
dgvStations.BorderStyle = BorderStyle.Fixed3D;
// Put the cells in edit mode when user enters them.
dgvStations.EditMode = DataGridViewEditMode.EditOnEnter;
}
catch (SqlException)
{
MessageBox.Show("To run this sample replace connection.ConnectionString" +
" with a valid connection string to the stations" +
" database accessible to your system.", "ERROR",
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
System.Threading.Thread.CurrentThread.Abort();
}
}
private static DataTable GetData(string sqlCommand)
{
string connectionString = DataAccess.ConnectionStringSQL(Controller.Instance.Model.SQLDatabase);
SqlConnection stationsConnection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand(sqlCommand, stationsConnection);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = command;
DataTable table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
adapter.Fill(table);
return table;
}
private void InitializeDataGridView()
{
try
{
// Set up the data source.
bindingSourceStations.DataSource = GetData("SELECT [STATIONID] AS 'Station ID',[STATNAME] AS 'Station Name' FROM [WXDATA].[dbo].[STATIONS]");
dgvStations.DataSource = bindingSourceStations;
// Automatically resize the visible rows.
dgvStations.AutoSizeRowsMode =
DataGridViewAutoSizeRowsMode.DisplayedCellsExceptHeaders;
// Set the DataGridView control's border.
dgvStations.BorderStyle = BorderStyle.Fixed3D;
// Put the cells in edit mode when user enters them.
dgvStations.EditMode = DataGridViewEditMode.EditOnEnter;
}
catch (SqlException)
{
MessageBox.Show("To run this sample replace connection.ConnectionString" +
" with a valid connection string to the stations" +
" database accessible to your system.", "ERROR",
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
System.Threading.Thread.CurrentThread.Abort();
}
}
private static DataTable GetData(string sqlCommand)
{
string connectionString = DataAccess.ConnectionStringSQL(Controller.Instance.Model.SQLDatabase);
SqlConnection stationsConnection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand(sqlCommand, stationsConnection);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = command;
DataTable table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
adapter.Fill(table);
return table;
}
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
From the example they keep the variables global so that it can be used in all functions.
So in the form (not in a subroutine) you could have:
and then new it up on the form_load:
Then you would use the adapter where needed.
So in the form (not in a subroutine) you could have:
SqlDataAdapter adapter;
and then new it up on the form_load:
adapter = new SqlDataAdapter();
Then you would use the adapter where needed.
You can do it in the following way:
private void buttonSave_Click(object sender, EventArgs e) {
foreach (DataGridViewRow row in dataGridView1.Rows) {
string constring = @"Data Source=<yourSrver>;Initial Catalog=<yourDB>;Integrated Security=true";
using (SqlConnection con = new SqlConnection(constring)) {
using (SqlCommand cmd = new SqlCommand("INSERT INTO yourTable " +
"VALUES(@Column1, @Column2, @Column3)", con)) {
cmd.Parameters.AddWithValue("@Column1", row.Cells["col1Name"].Value);
cmd.Parameters.AddWithValue("@Column2", row.Cells["col2Name"].Value);
cmd.Parameters.AddWithValue("@Column3", row.Cells["col3Name"].Value);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
}
private void buttonSave_Click(object sender, EventArgs e) {
foreach (DataGridViewRow row in dataGridView1.Rows) {
string constring = @"Data Source=<yourSrver>;Initial Catalog=<yourDB>;Integrated Security=true";
using (SqlConnection con = new SqlConnection(constring)) {
using (SqlCommand cmd = new SqlCommand("INSERT INTO yourTable " +
"VALUES(@Column1, @Column2, @Column3)", con)) {
cmd.Parameters.AddWithValue("@Column1", row.Cells["col1Name"].Value);
cmd.Parameters.AddWithValue("@Column2", row.Cells["col2Name"].Value);
cmd.Parameters.AddWithValue("@Column3", row.Cells["col3Name"].Value);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
}
The abovr only handles inserts, not updates.
C#
C# is an object-oriented programming language created in conjunction with Microsoft’s .NET framework. Compilation is usually done into the Microsoft Intermediate Language (MSIL), which is then JIT-compiled to native code (and cached) during execution in the Common Language Runtime (CLR).
98K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER
private void btnSave_Click(object sender, EventArgs e)
{
dgvStations.EndEdit(); //very important step
adapter.Update(dataTable); // use the adapter to update the data in the data table.
MessageBox.Show("Updated");
PopulateDataGridView(); // Should probably be renamed to something else as initialize implies only on creation.
}