Help With INSERT Command

[StoneTheCrows]

Hi,

Can anyone help?

I have a machine control application that needs to add a row to a table in an access 'Issues' database. The idea being that the operator can submit issues directly from the machine User interface. To test this out I have knocked up a fairly simple test app which just aks the user for some info then should INSERT into the database table.

Initially, the application opens the database and reads some data from other tables to populate some Combo Boxes and some Properties for the form. Everything takes place in a ViewModel the code for which is at the end of this post.

Whenever the ExecuteNonQuery method is called, I get an OleDbException "{"Object invalid or no longer set."}.

Here's a snippet taken from the View Model's full code.

                            dbConnection.Open();
                            
                            dbCommand.CommandType = CommandType.Text;
                            dbCommand.CommandText = "INSERT INTO `Issues` (`Title`, `Assigned To`, `Opened By`, `Opened Date`, `Status`, `Category`, `Priority`, `Description`, `Due Date`, `Comments`, `TaskID`) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )";
                            dbCommand.Parameters.Add("Title",OleDbType.WChar).Value = newTitle;
                            dbCommand.Parameters.Add("Assigned To",OleDbType.Integer).Value=DBNull.Value;
                            dbCommand.Parameters.Add("Opened By", OleDbType.Integer).Value = newOpener;
                            dbCommand.Parameters.Add("Opened Date",OleDbType.Date).Value= ReportedDateTime;
                            dbCommand.Parameters.Add("Status",OleDbType.Integer).Value= newStatus;
                            dbCommand.Parameters.Add("Category",OleDbType.Integer).Value= newCategory;
                            dbCommand.Parameters.Add("Priority", OleDbType.Integer).Value = newPriority;
                            dbCommand.Parameters.Add("Description",OleDbType.WChar).Value= newDescription;
                            dbCommand.Parameters.Add("Due Date", OleDbType.Date).Value = DBNull.Value;
                            dbCommand.Parameters.Add("Comments",OleDbType.WChar).Value= "Submitted";
                            dbCommand.Parameters.Add("TaskID",OleDbType.Integer).Value= DBNull.Value;

                            int r = dbCommand.ExecuteNonQuery();
                            dbCommand.Clone();
                            dbConnection.Close();

Anyone able to see what I'm doing wrong?

Cheers,

HERE'S THE FULL CODE

    public class SimpleDataEntryViewModel : ViewModelBase
    {
        #region Private fields

        private string Crlf = Environment.NewLine;
        private OleDbConnection dbConnection;
        OleDbCommand dbCommand;
        OleDbDataReader dataReader;

        private string _employeeName;
        private string _category;
        private string _summaryText =  "TEST";
        private string _descriptionText = "DELETE ME";
        private ICommand _submitCommand;
        private ICommand _cancelCommand;

        #endregion Private fields

        #region Events

        public delegate void CloseViewEventHandler(object sender, EventArgs e);
        public event CloseViewEventHandler CloseView;
        private string _priority;

        #endregion Events

        #region Public Properties

        /// <summary>
        /// Gets or sets the employee names.
        /// </summary>
        /// <value>
        /// The employee names.
        /// </value>
        public ObservableCollection<string> EmployeeNames { get; set; }

        /// <summary>
        /// Gets or sets the name of the selected employee.
        /// </summary>
        /// <value>
        /// The name of the selected employee.
        /// </value>
        public string EmployeeName
        {
            get
            {
                return _employeeName;
            } // End of property-get
            set
            {
                if (_employeeName != value)
                {
                    _employeeName = value;
                    FirePropertyChanged("EmployeeName");
                } // End if
            } // End of property-set
        } // End of property


        public IDictionary<string, int> Employees { get; set; }

        /// <summary>
        /// Gets or sets the category names.
        /// </summary>
        /// <value>
        /// The category names.
        /// </value>
        public ObservableCollection<string> CategoryNames { get; set; }

        /// <summary>
        /// Gets or sets the selected category.
        /// </summary>
        /// <value>
        /// The selected category.
        /// </value>
        public string Category
        {
            get
            {
                return _category;
            } // End of property-get
            set
            {
                if (_category != value)
                {
                    _category = value;
                    FirePropertyChanged("Category");
                } // End if
            } // End of property-set
        } // End of property


        public ObservableCollection<string> PriorityNames { get; set; }

        /// <summary>
        /// Gets or sets the selected category.
        /// </summary>
        /// <value>
        /// The selected category.
        /// </value>
        public string Priority
        {
            get
            {
                return _priority;
            } // End of property-get
            set
            {
                if (_priority != value)
                {
                    _priority = value;
                    FirePropertyChanged("Priority");
                } // End if
            } // End of property-set
        } // End of property

        public IDictionary<string, int> Priorities { get; set; }
        public IDictionary<string, int> Categories { get; set; }
        public IDictionary<string, int> IssueStatuses { get; set; }

        /// <summary>
        /// Gets or sets the reported date time.
        /// </summary>
        /// <value>
        /// The reported date time.
        /// </value>
        public DateTime ReportedDateTime { get; set; }


        /// <summary>
        /// Gets or sets the summary text.
        /// </summary>
        /// <value>
        /// The summary text.
        /// </value>
        public string SummaryText
        {
            get
            {
                return _summaryText;
            } // End of property-get
            set
            {
                if (_summaryText != value)
                {
                    _summaryText = value;
                    FirePropertyChanged("SummaryText");
                } // End if
            } // End of property-set
        } // End of property

        public string DescriptionText
        {
            get
            {
                return _descriptionText;
            } // End of property-get
            set
            {
                if (_descriptionText != value)
                {
                    _descriptionText = value;
                    FirePropertyChanged("DescriptionText");
                } // End if
            } // End of property-set
        } // End of property

        #endregion Public Properties

        #region Object Lifetime

        /// <summary>
        /// Initializes a new instance of the <see cref="SimpleDataEntryViewModel"/> class.
        /// </summary>
        public SimpleDataEntryViewModel()
        {
            EmployeeNames = new ObservableCollection<string>();
            Employees = new Dictionary<string, int>();
            CategoryNames = new ObservableCollection<string>();
            Categories = new Dictionary<string, int>();
            PriorityNames = new ObservableCollection<string>();
            Priorities = new Dictionary<string, int>();
            IssueStatuses = new Dictionary<string, int>();
            ReportedDateTime = DateTime.Now;
            InitialiseDataModel();
        } // End of method

        #endregion Object Lifetime


        #region Public Methods


        #endregion Public Methods

        #region Private methods

        private void InitialiseDataModel()
        {
            // Connect the Datasource
            dbConnection = new OleDbConnection(BreederIssues.Properties.Settings.Default.BreederManagement_beDevelopmentCopyConnectionString);
            dbCommand = new OleDbCommand();
            dbCommand.Connection = dbConnection;

            GetCategories();
            GetPriorities();
            GetEmployees();
        } // End of method


        private void GetCategories()
        {
            dbConnection.Open();
            dbCommand.CommandText = "SELECT * FROM Categories";
            dataReader = dbCommand.ExecuteReader();
            if (dataReader.HasRows)
            {
                while (dataReader.Read())
                {
                    string catName = dataReader[1].ToString();
                    int catID = Convert.ToInt32(dataReader[0]);
                    CategoryNames.Add(catName);
                    Categories.Add(catName, catID);
                } // End while
            } // End if
            dbConnection.Close();
        }

        private void GetPriorities()
        {
            dbConnection.Open();
            dbCommand.CommandText = "SELECT * FROM Priorities";
            dataReader = dbCommand.ExecuteReader();
            if (dataReader.HasRows)
            {
                while (dataReader.Read())
                {
                    string priName = dataReader[1].ToString();
                    int priID = Convert.ToInt32(dataReader[0]);
                    PriorityNames.Add(priName);
                    Priorities.Add(priName, priID);
                } // End while
            } // End if
            dbConnection.Close();
        }

        private void GetEmployees()
        {
            dbConnection.Open();
            dbCommand.CommandText = "SELECT * FROM Employees";
            dataReader = dbCommand.ExecuteReader();
            if (dataReader.HasRows)
            {
                while (dataReader.Read())
                {
                    string empName = dataReader[3].ToString() + " " + dataReader[2];
                    int empID = Convert.ToInt32(dataReader[0]);
                    EmployeeNames.Add(empName);
                    Employees.Add(empName, empID);
                } // End while
            } // End if
            dbConnection.Close();
        }

        #endregion Private methods

        #region Commands

        public ICommand CancelCommand
        {
            get
            {
                return _cancelCommand ?? (_cancelCommand = new RelayCommand(() =>
                {
                    CloseView(this, new EventArgs());
                }));
            } // End of property-get
        } // End of property

        public ICommand SubmitCommand
        {
            get
            {
                return _submitCommand ?? (_submitCommand = new RelayCommand(() =>
                {
                    bool commit = true;
                    if (string.IsNullOrEmpty(EmployeeName) || !EmployeeNames.Contains(EmployeeName))
                    {
                        BreederMessageBox.Show("You MUST select a Name for 'Reported By'.", "MISSING DATA", null, WpfMessageBoxButtons.Ok, WpfMessageBoxImage.Warning);
                        commit = false;
                    } // End if
                    else if(string.IsNullOrEmpty(Category) || !CategoryNames.Contains(Category))
                    {
                        if (BreederMessageBox.Show("You have not selected a valid Category for this issue.  If you continue the Category 'Other' will be selected for you." + Crlf + Crlf + "Would you like to continue?", "MISSING DATA", null, WpfMessageBoxButtons.YesNo, WpfMessageBoxImage.Warning) == WpfMessageBoxResult.No)
                        {
                            commit = false;
                        } // End if
                    } // End else if
                    else if (string.IsNullOrEmpty(SummaryText))
                    {
                        BreederMessageBox.Show("You MUST enter a summary for this issue.", "MISSING DATA", null, WpfMessageBoxButtons.Ok, WpfMessageBoxImage.Warning);
                        commit = false;
                    } // End else if
                    else if (string.IsNullOrEmpty(DescriptionText))
                    {
                        if (BreederMessageBox.Show("You have not entered a detailed description for this issue." + Crlf + Crlf + "Would you like to continue?", "MISSING DATA", null, WpfMessageBoxButtons.YesNo, WpfMessageBoxImage.Warning) == WpfMessageBoxResult.No)
                        {
                            commit = false;
                        } // End if
                    } // End else if

                    if (commit)
                    {
                        int newStatus = 1; // = New
                        int newOpener = Employees[EmployeeName];
                        int newCategory = Categories[Category];
                        int newPriority = Priorities[Priority]; //Normal
                        string newTitle = SummaryText;
                        string newDescription = DescriptionText;

                        try
                        {
                            dbConnection.Open();
                            
                            dbCommand.CommandType = CommandType.Text;
                            dbCommand.CommandText = "INSERT INTO `Issues` (`Title`, `Assigned To`, `Opened By`, `Opened Date`, `Status`, `Category`, `Priority`, `Description`, `Due Date`, `Comments`, `TaskID`) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )";
                            dbCommand.Parameters.Add("Title",OleDbType.WChar).Value = newTitle;
                            dbCommand.Parameters.Add("Assigned To",OleDbType.Integer).Value=DBNull.Value;
                            dbCommand.Parameters.Add("Opened By", OleDbType.Integer).Value = newOpener;
                            dbCommand.Parameters.Add("Opened Date",OleDbType.Date).Value= ReportedDateTime;
                            dbCommand.Parameters.Add("Status",OleDbType.Integer).Value= newStatus;
                            dbCommand.Parameters.Add("Category",OleDbType.Integer).Value= newCategory;
                            dbCommand.Parameters.Add("Priority", OleDbType.Integer).Value = newPriority;
                            dbCommand.Parameters.Add("Description",OleDbType.WChar).Value= newDescription;
                            dbCommand.Parameters.Add("Due Date", OleDbType.Date).Value = DBNull.Value;
                            dbCommand.Parameters.Add("Comments",OleDbType.WChar).Value= "Submitted";
                            dbCommand.Parameters.Add("TaskID",OleDbType.Integer).Value= DBNull.Value;

                            int r = dbCommand.ExecuteNonQuery();
                            dbCommand.Clone();
                            dbConnection.Close();
                        }
                        catch (Exception ex)
                        {
                            BreederMessageBox.Show(ex.Message, "Update Error");
                        } // End catch
                        dbCommand.Dispose();
                        dbConnection.Dispose();
                        CloseView(this, new EventArgs());
                    } // End if
                }));
            } // End of property-get
        } // End of property


        #endregion Commands

    } // End of class