Updating DataGridView Changes to Database

[mjjks]

Hi

I have a DataGridView thas being populated from a single table.

cmdSelectMrkrData = new SqlCommand("SELECT * FROM mrkr");
dtMrkrFilteredData = new DataTable();
cmdSelectMrkrData.Connection = this.GetConnection("HTCIS");
adpMrkrRecords = new SqlDataAdapter(cmdSelectMrkrData);
                                    
adpMrkrRecords.Fill(dtMrkrFilteredData);
dgvRecords.DataSource = dtMrkrFilteredData;

How do I update changes to a database when grid cell value changes. I tried this sample but I get errors.

[URL unfurl="true"]http://www.codeproject.com/cs/database/DataGridView2Db.asp[/url]

Any help is appreciated. Thanks
I'm using .Net 2.0

 

[IT4EVR]

If you are using 2.0 then your best bet is to use the SqlDataSource initially, then move on to more advanced methods.

I believe you can just click on the Smart Tag and configure data source. Then after you do that you want to go to the advanced tab and check the box for creating insert, update and delete statements. Then study the code so you know what's going on.

There are other way to do this but the method listed in your post isn't what you need right now.

 

[mjjks]

Thanks IT4EVR for an advice, however I have to stick with the above method as it's coming from Data Access framework that is being used by app. I'm not building app from scratch, so have to use build-in standard.

Thanks

 

[mjjks]

I'd really appreciate links to some sample code I could use as a starting point.

Thanks much

 

[mjjks]

In case someone will look for a similar solution, here's how I solved it.

class
{
        private SqlDataAdapter adpMrkrRecords;
        private DataTable dtMrkrFilteredData;
        private DataTable dtMrkrExceptionData;
        private SqlCommand cmdSelectMrkrData;
        private SqlCommandBuilder cb;
        private StringBuilder sb;

Form_Load()
 {
    // Create your connection
    SqlConnection conn = new SqlConnection();
    conn.ConnectionString = "bla-bla";
    conn.Open();
    adpMrkrRecords = new SqlDataAdapter("select * from MyTable", conn);
            cb = new SqlCommandBuilder(adpMrkrRecords);
            dtMrkrFilteredData = new DataTable("YourTableName");
            adpMrkrRecords.FillSchema(dtMrkrFilteredData, SchemaType.Source);
            adpMrkrRecords.Fill(dtMrkrFilteredData);
// set grid data source         
dgvRecords.DataSource = dtMrkrFilteredData.DefaultView;
 }

private void dgvRecords_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            // Create a new SQL statement for all updates.
            sb = new StringBuilder();

            // Update the data source.
            adpMrkrRecords.Update(dtMrkrFilteredData);

            if (sb.Length > 0)
            {
                // Create a connection command with the aggregate update command.
                // Create new connection
                SqlConnection connN = new SqlConnection();
                conn.ConnectionString = "bla-bla";
                conn.Open();
                SqlCommand cmd = new SqlCommand(sb.ToString(), connN);
                // Execute the update command.
                connN.Open();
                cmd.ExecuteScalar();
                connN.Close();

                // Refresh the DataTable.
                dtMrkrFilteredData.Clear();
                adpMrkrRecords.Fill(dtMrkrFilteredData);
            }
        }
}

Good luck