• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 463
  • Last Modified:

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).
0
rmmarsh
Asked:
rmmarsh
  • 7
  • 5
1 Solution
 
Shahan AyyubSenior Software Engineer - iOSCommented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Shahan AyyubSenior Software Engineer - iOSCommented:
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 Engineer - iOSCommented:
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 Engineer - iOSCommented:
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 Engineer - iOSCommented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now