Go Back   Access World Forums > Apps and Windows > Other Software

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-06-2013, 04:36 AM   #1
StoneTheCrows
Newly Registered User
 
Join Date: Feb 2013
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
StoneTheCrows is on a distinguished road
Help With INSERT Command

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.
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
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


Last edited by StoneTheCrows; 02-13-2013 at 02:38 AM. Reason: Clarity
StoneTheCrows is offline   Reply With Quote
Reply

Tags
insert , oledb , visual studio

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Formatting of Insert Command reeseburg Modules & VBA 16 01-25-2012 07:26 AM
insert into using command button jerry28ph Forms 15 01-20-2009 12:27 PM
Insert Picture Command Byoung2006 Forms 0 09-16-2006 04:11 PM
Insert Into Command joe789 Modules & VBA 1 05-21-2003 09:22 AM
Stumped on "INSERT INTO..." command Mendel Modules & VBA 2 12-19-2001 03:00 PM




All times are GMT -8. The time now is 07:09 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World