0

I am trying to set up a system that allows the user to alter the values of stock in an MS Access database. It is supposed to update the stock value of the SKU that has been entered on a previous form. Instead I don't get any error message, the program just sits there and doesn't do anything. The record does not get updated, no exceptions are thrown and I'm 99% certain the connection string is valid.

I had set it up so that once the operation is complete, it notified the user with a popup but this doesn't display either. Any suggestions would be appreciated.

Code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace InventoryManager
{
    public partial class frmAdjustment : Form
    {
        frmAmendStock _main;

        public string enteredSKU { get; set; }

        public frmAdjustment(frmAmendStock main)
        {
            InitializeComponent();
            _main = main;
        }        

        private void frmAdjustment_Load(object sender, EventArgs e)
        {
            this.AcceptButton = btnSubmit;
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btnSubmit_Click(object sender, EventArgs e)
        {
            using (OleDbConnection connect = new OleDbConnection())
            {

                connect.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Other\Documents\University Work\USB\Individual Project\Artefact\InventoryManager\InventoryManager\stock.mdb";
                connect.Open();

                OleDbCommand cmd = new OleDbCommand("UPDATE items SET Stock = @stock, Stock_Counted = @counted WHERE SKU ='" +enteredSKU+"'", connect);
                string units = txtAmount.Text;

                    if (connect.State == ConnectionState.Open)
                    {
                        if (string.IsNullOrEmpty(units))
                        {
                            MessageBox.Show("Please enter the correct amount of units.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                        }
                        else
                        {
                            cmd.Parameters.Add("@stock", OleDbType.Integer, 5).Value = txtAmount.Text;
                            cmd.Parameters.Add("@counted", OleDbType.Integer, 5).Value = txtAmount.Text;

                            try
                            {
                                cmd.ExecuteNonQuery();
                                MessageBox.Show("Stock Adjusted", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);

                                txtAmount.Clear();

                                connect.Close();
                            }
                            catch (Exception expe)
                            {
                                MessageBox.Show(expe.Source);
                                connect.Close();
                            }
                      }
                }
                else
                {
                    MessageBox.Show("Connection Failed");
                }
            }
        }
    }
}

After updating the code I now get this error:

Microsoft JET Database Engine error

iiAaronXiX
  • 93
  • 2
  • 3
  • 13
  • Can you provide some more information? Does the record get updated in the Access db? Is your connection string valid? Are there any exceptions? – Mark C. May 05 '16 at 14:26
  • The record does not get updated, the connection string is valid and no exceptions are thrown. – iiAaronXiX May 05 '16 at 14:30
  • What about the data types? I see that they are of type VarChar - is that what they are in your database (text)? – Mark C. May 05 '16 at 14:32
  • Changed them to Integers and nothing happened, pressing the submit button once does nothing, pressing it a second time however gives the error `An unhandled exception of type 'System.InvalidOperationException' occured in System.Data.dll Additional information: Not allowed to change the 'ConnectionString propety. The connection's current state is open.` – iiAaronXiX May 05 '16 at 14:42
  • what is `connect` ? You're going to want to use a `using` statement to dispose of your connection once you've executed your SQL statement(s) – Mark C. May 05 '16 at 14:45
  • I have updated the code in the post to show all of the code in the form. – iiAaronXiX May 05 '16 at 15:35
  • I wouldn't initialize the `OleDbConnection` like that. Just try to use it in a `using` statement inside the code block where you execute the sql command – Mark C. May 05 '16 at 15:47
  • Would you mind giving me an example? I've never used `using` before. – iiAaronXiX May 05 '16 at 16:00
  • Let me link you to another answer [here](http://stackoverflow.com/a/10375210/2679750) .. Let me know if it helps – Mark C. May 05 '16 at 16:02
  • Updated the code in the post to show off the using function, however nothing changed in the response. – iiAaronXiX May 05 '16 at 16:35
  • Do you still get the exception? – Mark C. May 05 '16 at 16:36
  • One the second button push no I do not. – iiAaronXiX May 05 '16 at 16:46
  • Check your table definition. It looks like your `SKU` field may not be defined as `Text`. You should be using a query parameter in your WHERE clause anyway. (There's no need to mix query parameters [good] *and* dynamic SQL [bad].) – Gord Thompson May 05 '16 at 17:49

2 Answers2

1

Your code to perform the update is inside the if (string.IsNullOrEmpty(units)) block, so if units actually has a value then the code will never be executed. It looks like you need an else after your error message.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
1

Managed to fix the issue and get it to save to the database. Thank you for everyone's help and suggestions.

Here's the working code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace InventoryManager
{
    public partial class frmAdjustment : Form
    {
        frmAmendStock _main;

        public string enteredSKU { get; set; }

        public frmAdjustment(frmAmendStock main)
        {
            InitializeComponent();
            _main = main;
        }        

        private void frmAdjustment_Load(object sender, EventArgs e)
        {
            this.AcceptButton = btnSubmit;
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btnSubmit_Click(object sender, EventArgs e)
        {
            using (OleDbConnection connect = new OleDbConnection())
            {

                connect.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Other\Documents\University Work\USB\Individual Project\Artefact\InventoryManager\InventoryManager\stock.mdb";
                connect.Open();

                OleDbCommand cmd = new OleDbCommand("UPDATE items SET Stock = @stock, Stock_Counted = @counted WHERE SKU LIKE '" +enteredSKU+"'", connect);
                string units = txtAmount.Text;

                    if (connect.State == ConnectionState.Open)
                    {
                        if (string.IsNullOrEmpty(units))
                        {
                            MessageBox.Show("Please enter the correct amount of units.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                        }
                        else
                        {
                            cmd.Parameters.Add("@stock", OleDbType.Integer, 5).Value = txtAmount.Text;
                            cmd.Parameters.Add("@counted", OleDbType.Integer, 5).Value = txtAmount.Text;

                            try
                            {
                                cmd.ExecuteNonQuery();
                                MessageBox.Show("Stock Adjusted", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);

                                txtAmount.Clear();

                                connect.Close();
                            }
                            catch (Exception expe)
                            {
                                MessageBox.Show(expe.ToString());
                                connect.Close();
                            }
                      }
                }
                else
                {
                    MessageBox.Show("Connection Failed");
                }
            }
        }
    }
}
iiAaronXiX
  • 93
  • 2
  • 3
  • 13