How to create update/insert statements for datagridview bound source?

I have a datagridview bound to a data table, via a data adapter.  I am using the validating method of the dgv to update the data table.  I now see that I need Insert and Update statements, tied to the data adapter.

The problem is that the data is already in the dgv, so how do I build the Insert/Update statements using the data which is already in the dgv?  (the dgv has only two columns).
rmmarshAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shahan AyyubSenior Software EngineerCommented:
0
rmmarshAuthor Commented:
In the validating method for the dgv, how do I know which command to use (delete, update or insert)?
0
rmmarshAuthor Commented:
**validating event
0
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Shahan AyyubSenior Software EngineerCommented:
Hi!

you can do something like this: (PLEASE SEE COMMENTS IN CODE)

        private void dataGridView1_Validating(object sender, CancelEventArgs e)
        {
            DataTable dt = ((DataTable)dataGridView1.DataSource).GetChanges();
            if (dt != null)
            { 
                //On  basis of Primary key you need to see:
                // if records exists  Prepare Update command
                // if record is not exists prepare Insert Command
            }
        }

Open in new window



For row deletion you can use  KeyDownEvent :
        private void dataGridView1_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode = Keys.Delete && dataGridView1.CurrentRow != null)
               { object obj = dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells[0PrimaryKeyColumnIndex].Value;
   // save this rows information to prepare delete command.
}
        }

Open in new window

0
rmmarshAuthor Commented:
I attempted to use CommandBuilder but it's not working (nothing happens)... here's my code:
 
ds = new DataSet();
            da = new FbDataAdapter();
            FbCommand cmd = new FbCommand("SELECT * FROM tCannedText", mediaConn);  //  create select command
            da.SelectCommand = cmd;
            cmd = new FbCommand("INSERT INTO tCannedText (sTitle, sCannedText) VALUES (?,?)", mediaConn);
            cmd.Parameters.Add("sTitle", FbDbType.VarChar, 20, "sTitle");
            cmd.Parameters.Add("sCannedText", FbDbType.VarChar, 200, "sCannedText");
            da.InsertCommand = cmd;
            cmd = new FbCommand("UPDATE tCannedText set sTitle ?, SET sCannedText = ?", mediaConn);
            cmd.Parameters.Add("sTitle", FbDbType.VarChar, 20, "sTitle");
            cmd.Parameters.Add("sCannedText", FbDbType.VarChar, 200, "sCannedText");
            da.UpdateCommand = cmd;

            cb = new FbCommandBuilder(da);  //  make the FbCommandBuilder

            da.Fill(ds);  //  fill the datatable
            dgvCannedText.DataSource = ds;  //  set the dgv source object

Open in new window


And here's the code in the Validating event:
 
//--  update the datatable 
        private void dgvCannedText_Validating(object sender, CancelEventArgs e) {
            da.Update(ds);
        }

Open in new window


I appreciate your help...
0
Shahan AyyubSenior Software EngineerCommented:
Hmmm.... FbCommandBuilder ? It seems to be a 3rd party library ?  
0
rmmarshAuthor Commented:
Firebird's .NET wrapper... I don't think the problem is there, it's in the logic I'm using...
0
Shahan AyyubSenior Software EngineerCommented:
Hi!

In your validation event code, did you try:

        private void dgvCannedText_Validating(object sender, CancelEventArgs e) {
            ds.AcceptChanges();
            da.Update(ds);
        }

Open in new window

0
rmmarshAuthor Commented:
Shahan... nope!  (does absolutely nothing).
0
Shahan AyyubSenior Software EngineerCommented:
Hi!

Could you please try like this:

See code first then visit this link as well:

        private void dgvCannedText_Validating(object sender, CancelEventArgs e)
        {
            DataTable dtChanges = ((DataTable)dgvCannedText.DataSource).GetChanges();

            if (dtChanges != null)
            {
                mediaConn.Open();
                cmd = new SqlCommand("SELECT * FROM tCannedText", mediaConn);
                for (int i = 0; i < dtChanges.Rows.Count; i++)
                { 
                    da.SelectCommand = cmd;

                    SqlCommand DAUpdateCmd = new SqlCommand("Update tCannedText set sTitle = @sTitle, sCannedText = @sCannedText where ID=" + dtChanges.Rows[i].ItemArray.GetValue(0) , da.SelectCommand.Connection);
                    DataSet DSet = new DataSet();

                    //Create and append the parameters for the Update command.
                    DAUpdateCmd.Parameters.Add(new SqlParameter("@sTitle", SqlDbType.VarChar));
                    DAUpdateCmd.Parameters["@sTitle"].SourceVersion = DataRowVersion.Current;
                    DAUpdateCmd.Parameters["@sTitle"].SourceColumn = "sTitle";

                    DAUpdateCmd.Parameters.Add(new SqlParameter("@sCannedText", SqlDbType.VarChar));
                    DAUpdateCmd.Parameters["@sCannedText"].SourceVersion = DataRowVersion.Original;
                    DAUpdateCmd.Parameters["@sCannedText"].SourceColumn = "sCannedText";

                    //Assign the SqlCommand to the UpdateCommand property of the SqlDataAdapter.
                    da.UpdateCommand = DAUpdateCmd;
                    int rec = da.Fill(DSet, "tCannedText");

                    DSet.Tables[0].Rows[i].ItemArray = dtChanges.Rows[i].ItemArray;
          
                    int recCount = da.Update(DSet, "tCannedText");
                    DSet.AcceptChanges();
                    
                }
                mediaConn.Close(); 
            }
        }

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rmmarshAuthor Commented:
I'm right in the middle of something else...please give me a couple of days to check this out... thanks.
0
rmmarshAuthor Commented:
Thank you...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.