none
Saving data takes two attempts? RRS feed

  • Question

  • Hi,

    So what am I missing here?  In my application I have a datagridview and when I double-click a row it hides the datagridview and makes visible a panel containing text boxes with the details of the selected row for editing.

    I have it basically working, however, I'm finding that when I hit save it doesn't actually send the data back to the SQL server.  Not the first time at least.  If I double-click the same row and hit save again, then it takes the changes.

    I've stepped through with the debugger and it goes through all of the steps but the first time doesn't work, second time through works fine.  I must be missing something.

    I'm using the disconnected model for connecting to the backend database and using SQLCommandBuilder to build the Insert, Update and Delete commands.  The Insert functions work fine and I'm not implementing Delete at this time.

    If you will allow me, here's the relevant code.

    Here's the double-click method.

    // Delivery Board Grid doublclick, switch to patient details.
    private void dgvDeliveryBrd_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
    {
        indexRow = e.RowIndex;                              // Get the selected Row Index
        DataGridViewRow row = this.dgvDeliveryBrd.Rows[indexRow];
    
    // Make the Panel for the DeliveryGridView invisible.
        ToggleDeliveryBrdPanel();                           // Hides Delivery Board panel and show Patient Details panel.
                
    // Set the values of the details controls to the selected record.
        tlpLeft.Controls["pnlPTDetails"].Controls["txtPTID"].Text = row.Cells[1].Value.ToString();
        tlpLeft.Controls["pnlPTDetails"].Controls["txtPTName"].Text = row.Cells[2].Value.ToString();
        tlpLeft.Controls["pnlPTDetails"].Controls["txtTeam"].Text = row.Cells[3].Value.ToString();
        tlpLeft.Controls["pnlPTDetails"].Controls["txtDestination"].Text = row.Cells[4].Value.ToString();
        tlpLeft.Controls["pnlPTDetails"].Controls["txtTime"].Text = row.Cells[6].Value.ToString();
        tlpLeft.Controls["pnlPTDetails"].Controls["txtStatus"].Text = row.Cells[8].Value.ToString();
        tlpLeft.Controls["pnlPTDetails"].Controls["txtDriver"].Text = row.Cells[9].Value.ToString();
        tlpLeft.Controls["pnlPTDetails"].Controls["txtTimeLeft"].Text = row.Cells[10].Value.ToString();
        tlpLeft.Controls["pnlPTDetails"].Controls["dtpDeliveryDate"].Text = row.Cells[11].Value.ToString();
    }
    

    Then the Save button Click method.

    // Write the updated patient details to the DataGridView and commit it to the database.
    private void btnSave_Click(object sender, EventArgs e)
    {
         DataGridViewRow newDataRow = this.dgvDeliveryBrd.Rows[indexRow];
    
    // Write the updated values to the DataGridView.
         newDataRow.Cells[4].Value = tlpLeft.Controls["pnlPTDetails"].Controls["txtDestination"].Text;
         newDataRow.Cells[6].Value = tlpLeft.Controls["pnlPTDetails"].Controls["txtTime"].Text;
         newDataRow.Cells[9].Value = tlpLeft.Controls["pnlPTDetails"].Controls["txtDriver"].Text;
         newDataRow.Cells[11].Value = tlpLeft.Controls["pnlPTDetails"].Controls["dtpDeliveryDate"].Text;
         this.btnRefresh.PerformClick();
         ToggleDeliveryBrdPanel();
     }
    

    The Refresh button Click.

    private void btnRefresh_Click(object sender, EventArgs e)
       {
        // Send current state to database.
        if (_dal.UpdateDeliveryBrd() == 0)
        {
            _dal.RefreshDeliveryBrd(dtpDeliveryDate.Value.Date);
            _dal.RefreshDeliveries(dtpDeliveryDate.Value.Date);
        }
        else
        {
            MessageBox.Show(_dal.GetException());
        }
    }
    
    

    The UpdateDeliveryBrd method.

    public int UpdateDeliveryBrd()
    {
       try
       {
          daDeliveryBrd.Update(dsDeliveryBrd, "DeliveryBrd");
          return 0;
       }
       catch (Exception ex)
       {
          this.Exception = ex;
          return -1;
       }
    }
    


    RefreshDeliveries is not germane to this example so I'll skip it.

    RefreshDeliveryBoard method.

    public void RefreshDeliveryBrd(DateTime date)
    {
       // Set the parameter.
       cmdDelvBrd.Parameters["@delivery_date"].Value = date;
    
       // Clear and refill the patients data table.
       dsDeliveryBrd.Clear();
       daDeliveryBrd.Fill(dsDeliveryBrd, "DeliveryBrd");
    }
    

    So what do you think?  Why would the daDeliveryBrd.Update(dsDeliveryBrd, "DeliveryBrd"); line not actually update the backend the first time through but work the second time?

    Thanks in advance!

    Tuesday, September 10, 2019 10:27 PM

All replies

  • Greetings Lkubler.

    I'm not sure of the answer, but my suspicion is the PerformClick might have something to do with it. In my experience that call never works how you might expect.

    Try something like this:

    // Write the updated patient details to the DataGridView and commit it to the database.
    private void btnSave_Click(object sender, EventArgs e)
    {
         DataGridViewRow newDataRow = this.dgvDeliveryBrd.Rows[indexRow];
    
     // Write the updated values to the DataGridView.
         newDataRow.Cells[4].Value = tlpLeft.Controls["pnlPTDetails"].Controls["txtDestination"].Text;
         newDataRow.Cells[6].Value = tlpLeft.Controls["pnlPTDetails"].Controls["txtTime"].Text;
         newDataRow.Cells[9].Value = tlpLeft.Controls["pnlPTDetails"].Controls["txtDriver"].Text;
         newDataRow.Cells[11].Value = tlpLeft.Controls["pnlPTDetails"].Controls["dtpDeliveryDate"].Text;
         this.Refresh();
         ToggleDeliveryBrdPanel();
    }
    
    
    
    
    private void btnRefresh_Click(object sender, EventArgs e)
    {
        Refresh();
    }
    
    private void Refresh()
    {
        // Send current state to database.
        if (_dal.UpdateDeliveryBrd() == 0)
        {
            _dal.RefreshDeliveryBrd(dtpDeliveryDate.Value.Date);
            _dal.RefreshDeliveries(dtpDeliveryDate.Value.Date);
        }
        else
        {
            MessageBox.Show(_dal.GetException());
        }
    }


    Tuesday, September 10, 2019 11:34 PM
  • Hello,

    If it fails the first time and works the second time then (I have not gone over all of your code) there are no changes. To prove this try var result = dsDeliveryBrd("DeliveryBrd").GetChanges(DataRowState.Modified) to see if there are modified records, if not the result will be null, otherwise it will have those rows that have updates, use DataRowState.Added to see if there are added rows. Deleted rows are a different animal and need to get to them a different way, see this code

    So if there are changes the second time whatever event you believe should be triggering changes is not. I recommend (and this is a great way to figure out issues) is to set breakpoints and when hit inspect your data.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, September 11, 2019 1:02 AM
    Moderator
  • Hi lkubler,

    Since we can't reproduce your problem, we can't find out the problem. Can you send a simple demo case? About Insert functions by using the disconnected model, I made a demo you can refer to.

        public partial class Form1 : Form
        {
            SqlConnection con;
            SqlDataAdapter da;
            DataSet ds;
            DataTable dt;
            SqlCommandBuilder cb;
    
            public Form1()
            {
                InitializeComponent();
                string str = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog='My Database';Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
    
                string sql = "select * from test";
                con = new SqlConnection(str);
                con.Open();
                da = new SqlDataAdapter(sql, con);
                cb = new SqlCommandBuilder(da);
                ds = new DataSet();
                da.Fill(ds, "test");
                dt = ds.Tables["test"];
                dataGridView2.DataSource = dt;
            }
    
            private void btinsert_Click(object sender, EventArgs e)
            {
                DataRow dr = dt.NewRow();
                dr["Id"] = textBox2.Text;
                dr["Name"] = textBox1.Text;
                dr["Point"] = textBox3.Text;
                dt.Rows.Add(dr);
                cb = new SqlCommandBuilder(da);
                da.Update(ds.Tables[0]);
    
            }
        }

    Regards,

    Kyle


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 11, 2019 9:59 AM
    Moderator
  • Hi Ante,

    Thanks for the suggestion, does seem cleaner than what I was doing by invoking the Click method directly.  I implemented it but unfortunately it didn't help the issue.  I did use a different method name as Refresh() was conflicting with Object.Refresh(), which the compiler was asking if I was trying to override the built in method.

    Thanks!

    Wednesday, September 11, 2019 3:27 PM
  • [...] I'm finding that when I hit save it doesn't actually send the data back to the SQL server.  [...]


    Did you observe this by checking the data in database tables or by seeing the grid control?



    • Edited by Viorel_MVP Wednesday, September 11, 2019 6:42 PM
    Wednesday, September 11, 2019 6:41 PM
  • Hi Karen,

    I plugged this into the Save button's Click method and checked for null and printed out if there were changes or not.  And it behaves consistently.  On the first pass it reports no changes but then on the second pass it reports the change. 

    Ok, that's weird.  All my previous testing was performed with only one record in the dataset.  I've added a second record to the table and this record doesn't work at all.  No matter how many times I try it just reports that there are no changes to the second record.  But record one continues to behave as before.

    Any thoughts?

    Thursday, September 12, 2019 8:05 PM
  • Hi Kyle,

    I built a simple example program to try and reproduce the issue but couldn't reproduce it.  My simple example seems to be working correctly, however, I simply built a table programmatically and haven't hooked it up to an SQL backend.

    Definitely feels like there's something wrong with my code, but I'll be damned if I can figure out what yet.

    Thanks!

    Thursday, September 12, 2019 8:10 PM
  • Hi Viorel,

    Both.  As Karen suggested I setup a breakpoint and stepped through the code.  I can see where it sometimes sets the DataGridViewRow with the updated data and where it sometimes doesn't.  When it doesn't the updated data does not make it to the backend server, but when it does pick up the changes it does get recorded in the database and then, of course, in the data grid.

    Thanks!

    Thursday, September 12, 2019 8:14 PM