asked on

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;
}

ASKER CERTIFIED SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rwheeler23

ASKER

How do I code this to use the adapter to update the data in the data table? The line in bold needs to be setup. What must I do?

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.
}

Kyle Abrahams, PMP

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:

  SqlDataAdapter adapter;

Open in new window

and then new it up on the form_load:

adapter = new SqlDataAdapter();

Open in new window

Then you would use the adapter where needed.

Andrei Fomitchev

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();
}
}
}
}

Kyle Abrahams, PMP

The abovr only handles inserts, not updates.