0

I have a windows forms project that is working well except for the save button that was created by the designer. The last statement in the save item click event fails. This code was generated by the designer and I don't totally understand it. Does anyone have an idea what is causing the error? I would preferably like it to auto save when navigating off the record and make the button a single record save if that is possible.

    private void cUSTOMERBindingNavigatorSaveItem_Click(object sender, EventArgs e)
    {
        this.Validate();
        this.cUSTOMERBindingSource.EndEdit();
        dbCon = new SqlConnection(dbConString);
        SqlDataAdapter dbAdapter = new SqlDataAdapter();
        dbAdapter.SelectCommand = new SqlCommand("Select * From Customers");
        SqlCommandBuilder dbBldr = new SqlCommandBuilder();
        dbBldr.DataAdapter = dbAdapter;
        this.tableAdapterManager.UpdateAll(this.bML_WMS245GDataSet);
    }

The exception detail and the full code is below:

System.InvalidOperationException was unhandled
  HResult=-2146233079
  Message=Update requires a valid UpdateCommand when passed DataRow collection with modified rows.
  Source=Ballmill
  StackTrace:
       at Ballmill.BML_WMS245GDataSetTableAdapters.TableAdapterManager.UpdateAll(BML_WMS245GDataSet dataSet) in C:\Users\Jerry\documents\visual studio 2015\Projects\Ballmill\Ballmill\BML_WMS245GDataSet.Designer.cs:line 2102
       at Ballmill.Customer.cUSTOMERBindingNavigatorSaveItem_Click(Object sender, EventArgs e) in C:\Users\Jerry\documents\visual studio 2015\Projects\Ballmill\Ballmill\Customer.cs:line 29
       at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
       at System.Windows.Forms.ToolStripButton.OnClick(EventArgs e)
       at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
       at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
       at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
       at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
       at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
       at System.Windows.Forms.ToolStrip.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at Ballmill.Program.Main() in C:\Users\Jerry\documents\visual studio 2015\Projects\Ballmill\Ballmill\Program.cs:line 19
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: 


public partial class Customer : Form
{
    public string dbConString = "Data Source=localhost\\BALLMILL;Initial Catalog=Ballmill;Integrated Security=True";
    public SqlConnection dbCon = null;
    public SqlDataReader dbRdr = null;
    public SqlCommand dbCommand = null;

    public Customer()
    {
        InitializeComponent();
    }
    private void cUSTOMERBindingNavigatorSaveItem_Click(object sender, EventArgs e)
    {
        this.Validate();
        this.cUSTOMERBindingSource.EndEdit();
        dbCon = new SqlConnection(dbConString);
        SqlDataAdapter dbAdapter = new SqlDataAdapter();
        dbAdapter.SelectCommand = new SqlCommand("Select * From Customers");
        SqlCommandBuilder dbBldr = new SqlCommandBuilder();
        dbBldr.DataAdapter = dbAdapter;
        this.tableAdapterManager.UpdateAll(this.bML_WMS245GDataSet);
    }

    private void Customer_Load(object sender, EventArgs e)
    {
        this.cUSTOMERTableAdapter.Fill(this.bML_WMS245GDataSet.CUSTOMER);

        SqlConnection dbCon = new SqlConnection(dbConString);
        SqlDataReader rdrCustomers = null;
        try
        {
            dbCon.Open();
            SqlCommand sqlCustomers = new SqlCommand("SELECT CustomerCode FROM Customer", dbCon);
            rdrCustomers = sqlCustomers.ExecuteReader();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message.ToString(), "Error accessing database");
            return;
        }

        while (rdrCustomers.Read())
        {
            listCustomers.Items.Add(rdrCustomers["CustomerCode"].ToString());
        }
    }

    private void listCustomers_SelectedIndexChanged(object sender, EventArgs e)
    {
        cUSTOMERBindingSource.Position = listCustomers.SelectedIndex;
    }
}
Jerry Welliver
  • 377
  • 1
  • 13
  • You need a SQLCommandBuilder. https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder(v=vs.110).aspx. The update command to work you need four queries in the SQLCommand 1) Update 2) Delete 3) Insert 4) Select. The CommandBuilder takes the select query and automatically generates the other three queries. – jdweng Jun 20 '18 at 16:47
  • I added the command builder as I read it in several example sites, but I must still be missing something. I am getting the same error. I updated the code excerpt in the question with the current code. – Jerry Welliver Jun 20 '18 at 18:38
  • Does you connection string list the default database? You may need to specify the database. Try : "Use Database_Name; Select * From Customers". Use real database name. Are you using the same query that you used to originally used to get the data? Normally I use the same connection to update the database that I use when querying the data. Not sure if that is the issue. I would put a break point at at the UpdateAll. Then view the Dataset with the Visualizer. Right click on this.bML_WMS245GDataSet. The click on down arrow and click on Data Visualizer. Then in pulldown look at all tables. – jdweng Jun 20 '18 at 22:23
  • When a DataAdapter is used to fill a DataTable from a database a mapping is created between Data Table and the Database which includes a schema. When you do the UpdateAll the four commands in the DataAdapter are verified as well as the mapping and schema. You have a very general error message saying the validation of the commands (4x), mapping and schema are failing. You may have entered invalid data in the Datatable (a number instead of a string or a Date that cannot be parsed). Looking at the exception the update is being done in a thread which may indicate cross-threading. – jdweng Jun 20 '18 at 23:37
  • I'm suspecting looking at your code that your may have two connections between the app and the database since you are creating a new SQLConnection which may cause cross-threading. It is probably occurring during the validation of the four commands which is why you are getting the exception message indicating the UpdateAll is failing. This is only a guess. – jdweng Jun 20 '18 at 23:42
  • Thanks for following up on this. I had a similar suspicion that I had two different connections so I created public variables for all the objects and that still did not work. The variables are setup during load and I am trying to use them in the update page. I am wondering if the designer code behind the scenes is using a different sql command. I created the form by dragging fields from the data source pane and a binding navigator. I found an example online that used view state to save the query and the dataset during the load for use in the update routine. – Jerry Welliver Jun 21 '18 at 13:57
  • Designer file is the same as running from app except the designer is called in the initialize(), while regular code has to be place after initialize. I do not put a lot of code into the designer. Only the minimum necessary. Often I take code out of designer and put into regular code. Often there is issues because the code in the Designer is run before all objects are defined. You can't do an Update of the Dataset until it is filled. So you may want to add an if to make sure this.bML_WMS245GDataSet is not null. – jdweng Jun 21 '18 at 14:09

0 Answers0