Why am I getting an "unhandled exception" when saving a database entry?

Question

I am currently working on a database that keeps track of vehicles parked in a parking lot. However I am running into a few problems saving new records to the database table. Now I still fairly new to program so I am having trouble understanding the error I am receiving. I've tried looking it up and have found similar situations, but not quite the same. I've created the app in C# and my database is an access .mdb file.

The current error I am getting is the following:

"An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll

Additional information: Not allowed to change the 'ConnectionString' property. The connection's current state is open"

I would like to point out that this is happening only when I click save and there are values in the textboxes. If I click save without entering anything it says "Guest Information Saved Successfully".

I am at a loss as to what is happening, so I'm hoping someone here may be able to help. I have provided the code for my app so far along with Call Stack.

Below is the code for my app:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data.OleDb;
using System.Data;
using System.ComponentModel;


namespace ParkingDatabase
{
/// <summary>
/// Interaction logic for MainWindow.xaml
/// </summary>
public partial class MainWindow : Window
{

    public MainWindow()
    {
        InitializeComponent();
    }

    OleDbConnection DBConnect = new OleDbConnection();
    private void btnSave_Click(object sender, RoutedEventArgs e)
    {
        DBConnect.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\bkoso\Documents\Visual Studio 2015\Projects\ParkingDatabase\ParkingDatabase\ParkingData.mdb";
        DBConnect.Open();
        OleDbCommand com = new OleDbCommand("INSERT INTO Guest Info([Guest FName], [Guest LName], [Room #], Departure, Return, [Vehicle Colour], [Vehicle Make], [Plate #], [Contact FName], [Contact LName], [Contact #], [Contact Email], [Tag #]) Values(@[Guest FName], @[Guest LName], @[Room #], @Departure, @Return, @[Vehicle Colour], @[Vehicle Make], @[Plate #], @[Contact FName], @[Contact LName], @[Contact #], @[Contact Email], @[Tag #])", DBConnect);
            com.Parameters.AddWithValue("@[Guest FName]", txtBxGstFName.Text);
            com.Parameters.AddWithValue("@[Guest LName]", txtBxGstLName.Text);
            com.Parameters.AddWithValue("@[Room #]", txtBxRm.Text);
            com.Parameters.AddWithValue("@Departure", txtBxDDate.Text);
            com.Parameters.AddWithValue("@[Return]", txtBxRDate.Text);
            com.Parameters.AddWithValue("@[Vehicle Colour]", txtBxVColour.Text);
            com.Parameters.AddWithValue("@[Vehicle Make]", txtBxVMake.Text);
            com.Parameters.AddWithValue("@[Plate #]", txtBxPlate.Text);
            com.Parameters.AddWithValue("@[Contact FName]", txtBxContactFName.Text);
            com.Parameters.AddWithValue("@[Contact LName]", txtBxContactLName.Text);
            com.Parameters.AddWithValue("@[Contact #]", txtBxPhone.Text);
            com.Parameters.AddWithValue("@[Contact Email]", txtBxEmail.Text);
            com.Parameters.AddWithValue("@[Tag #", txtBxTag.Text);

        if (DBConnect.State == ConnectionState.Open)
        {
            try
            {
                //com.ExecuteNonQuery();
                MessageBox.Show("Guest Information Saved Successfully");
                txtBxGstFName.Text = "";
                txtBxGstLName.Text = "";
                txtBxRm.Text = "";
                txtBxDDate.Text = "";
                txtBxRDate.Text = "";
                txtBxVColour.Text = "";
                txtBxVMake.Text = "";
                txtBxPlate.Text = "";
                txtBxContactFName.Text = "";
                txtBxContactLName.Text = "";
                txtBxPhone.Text = "";
                txtBxEmail.Text = "";
                txtBxTag.Text = "";

            }
            catch (Exception notSaved)
            {
                if (DBConnect != null && DBConnect.State != ConnectionState.Closed)
                {
                    DBConnect.Close();
                }
                MessageBox.Show("Error Saving Data \n" + notSaved.ToString());
                DBConnect.Close();
            }
        }
        else
        {
            MessageBox.Show("Connection Failed");
        }
    }

    private void btnClear_Click(object sender, RoutedEventArgs e)
    {

    }

    private void btnView_Click(object sender, RoutedEventArgs e)
    {

    }

    private void btnSame_Click(object sender, RoutedEventArgs e)
    {

    }

    private void txtBoxGuestFirstName_TextChanged(object sender, TextChangedEventArgs e)
    {

    }


}
}

Below is the Call Stack:

>   ParkingDatabase.exe!ParkingDatabase.MainWindow.btnSave_Click(object sender, System.Windows.RoutedEventArgs e) Line 36   C#
PresentationCore.dll!System.Windows.RoutedEventHandlerInfo.InvokeHandler(object target, System.Windows.RoutedEventArgs routedEventArgs) Unknown
PresentationCore.dll!System.Windows.EventRoute.InvokeHandlersImpl(object source, System.Windows.RoutedEventArgs args, bool reRaised)    Unknown
PresentationCore.dll!System.Windows.UIElement.RaiseEventImpl(System.Windows.DependencyObject sender, System.Windows.RoutedEventArgs args)   Unknown
PresentationCore.dll!System.Windows.UIElement.RaiseEvent(System.Windows.RoutedEventArgs e)  Unknown
PresentationFramework.dll!System.Windows.Controls.Primitives.ButtonBase.OnClick()   Unknown
PresentationFramework.dll!System.Windows.Controls.Button.OnClick()  Unknown
PresentationFramework.dll!System.Windows.Controls.Primitives.ButtonBase.OnMouseLeftButtonUp(System.Windows.Input.MouseButtonEventArgs e)    Unknown
PresentationCore.dll!System.Windows.UIElement.OnMouseLeftButtonUpThunk(object sender, System.Windows.Input.MouseButtonEventArgs e)  Unknown
PresentationCore.dll!System.Windows.Input.MouseButtonEventArgs.InvokeEventHandler(System.Delegate genericHandler, object genericTarget) Unknown
PresentationCore.dll!System.Windows.RoutedEventArgs.InvokeHandler(System.Delegate handler, object target)   Unknown
PresentationCore.dll!System.Windows.RoutedEventHandlerInfo.InvokeHandler(object target, System.Windows.RoutedEventArgs routedEventArgs) Unknown
PresentationCore.dll!System.Windows.EventRoute.InvokeHandlersImpl(object source, System.Windows.RoutedEventArgs args, bool reRaised)    Unknown
PresentationCore.dll!System.Windows.UIElement.ReRaiseEventAs(System.Windows.DependencyObject sender, System.Windows.RoutedEventArgs args, System.Windows.RoutedEvent newEvent)  Unknown
PresentationCore.dll!System.Windows.UIElement.OnMouseUpThunk(object sender, System.Windows.Input.MouseButtonEventArgs e)    Unknown
PresentationCore.dll!System.Windows.Input.MouseButtonEventArgs.InvokeEventHandler(System.Delegate genericHandler, object genericTarget) Unknown
PresentationCore.dll!System.Windows.RoutedEventArgs.InvokeHandler(System.Delegate handler, object target)   Unknown
PresentationCore.dll!System.Windows.RoutedEventHandlerInfo.InvokeHandler(object target, System.Windows.RoutedEventArgs routedEventArgs) Unknown
PresentationCore.dll!System.Windows.EventRoute.InvokeHandlersImpl(object source, System.Windows.RoutedEventArgs args, bool reRaised)    Unknown
PresentationCore.dll!System.Windows.UIElement.RaiseEventImpl(System.Windows.DependencyObject sender, System.Windows.RoutedEventArgs args)   Unknown
PresentationCore.dll!System.Windows.UIElement.RaiseTrustedEvent(System.Windows.RoutedEventArgs args)    Unknown
PresentationCore.dll!System.Windows.UIElement.RaiseEvent(System.Windows.RoutedEventArgs args, bool trusted) Unknown
PresentationCore.dll!System.Windows.Input.InputManager.ProcessStagingArea() Unknown
PresentationCore.dll!System.Windows.Input.InputManager.ProcessInput(System.Windows.Input.InputEventArgs input)  Unknown
PresentationCore.dll!System.Windows.Input.InputProviderSite.ReportInput(System.Windows.Input.InputReport inputReport)   Unknown
PresentationCore.dll!System.Windows.Interop.HwndMouseInputProvider.ReportInput(System.IntPtr hwnd, System.Windows.Input.InputMode mode, int timestamp, System.Windows.Input.RawMouseActions actions, int x, int y, int wheel)   Unknown
PresentationCore.dll!System.Windows.Interop.HwndMouseInputProvider.FilterMessage(System.IntPtr hwnd, MS.Internal.Interop.WindowMessage msg, System.IntPtr wParam, System.IntPtr lParam, ref bool handled)   Unknown
PresentationCore.dll!System.Windows.Interop.HwndSource.InputFilterMessage(System.IntPtr hwnd, int msg, System.IntPtr wParam, System.IntPtr lParam, ref bool handled)    Unknown
WindowsBase.dll!MS.Win32.HwndWrapper.WndProc(System.IntPtr hwnd, int msg, System.IntPtr wParam, System.IntPtr lParam, ref bool handled) Unknown
WindowsBase.dll!MS.Win32.HwndSubclass.DispatcherCallbackOperation(object o) Unknown
WindowsBase.dll!System.Windows.Threading.ExceptionWrapper.InternalRealCall(System.Delegate callback, object args, int numArgs)  Unknown
WindowsBase.dll!System.Windows.Threading.ExceptionWrapper.TryCatchWhen(object source, System.Delegate callback, object args, int numArgs, System.Delegate catchHandler) Unknown
WindowsBase.dll!System.Windows.Threading.Dispatcher.LegacyInvokeImpl(System.Windows.Threading.DispatcherPriority priority, System.TimeSpan timeout, System.Delegate method, object args, int numArgs)   Unknown
WindowsBase.dll!MS.Win32.HwndSubclass.SubclassWndProc(System.IntPtr hwnd, int msg, System.IntPtr wParam, System.IntPtr lParam)  Unknown
[Native to Managed Transition]  
[Managed to Native Transition]  
WindowsBase.dll!System.Windows.Threading.Dispatcher.PushFrameImpl(System.Windows.Threading.DispatcherFrame frame)   Unknown
WindowsBase.dll!System.Windows.Threading.Dispatcher.PushFrame(System.Windows.Threading.DispatcherFrame frame)   Unknown
PresentationFramework.dll!System.Windows.Application.RunDispatcher(object ignore)   Unknown
PresentationFramework.dll!System.Windows.Application.RunInternal(System.Windows.Window window)  Unknown
PresentationFramework.dll!System.Windows.Application.Run(System.Windows.Window window)  Unknown
PresentationFramework.dll!System.Windows.Application.Run()  Unknown
ParkingDatabase.exe!ParkingDatabase.App.Main()  C#
[Native to Managed Transition]  
[Managed to Native Transition]  
mscorlib.dll!System.AppDomain.ExecuteAssembly(string assemblyFile, System.Security.Policy.Evidence assemblySecurity, string[] args) Unknown
Microsoft.VisualStudio.HostingProcess.Utilities.dll!Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()   Unknown
mscorlib.dll!System.Threading.ThreadHelper.ThreadStart_Context(object state)    Unknown
mscorlib.dll!System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state, bool preserveSyncCtx)   Unknown
mscorlib.dll!System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state, bool preserveSyncCtx)   Unknown
mscorlib.dll!System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state) Unknown
mscorlib.dll!System.Threading.ThreadHelper.ThreadStart()    Unknown

In case anyone is wondering the Unknown in the Call Stack is in the Language column.

In the code sample you may notice that line 57, com.ExecuteNonQuery(); , is commented out. The reason for this is that I was getting a syntax error with it.

Any help with this would be greatly appreciated. If I need to provide any more information please let me know.

Bloodstalker

You open the connection in the button click but don't close it. You should really move the connection object inside the method anyway, then you won't have trouble with trying to modify open connections. General rule of thumb for databases in code is: connect as late as possible, disconnect as quickly as possible. — DavidG, Sep 15 '15 at 10:49
And use the *using* statement so you can't forget to close it. — Hans Passant, Sep 15 '15 at 10:54
and you will close it twice in the current code if an exception is thrown. once if it is not closed and not null and again after you show the messagebox... And you never close the connection if it is successful.. just do what hans said and forget about handling the connection state. .... And when do you actually execute the command? you wont get a syntax error till you execute it — Choco Smith, Sep 15 '15 at 10:55
@Hans Passant I'm not sure what you mean by use the using statment so I can't forget to close it. — Bloodstalker, Sep 15 '15 at 10:59
Like this: `using(OleDbConnection DBConnect = new OleDbConnection()) { //your code here }` — DavidG, Sep 15 '15 at 11:00
@ChocoSmith if I remove the section handling the connection state, how can I have it check to see if the data was successfully entered or not? Sthat's basically what I was trying to do with that section. I just want a way to say the data was entered successfully or have it say it wasn't. — Bloodstalker, Sep 15 '15 at 11:02
@DavidG the part where you put your code here would that be my connection string? — Bloodstalker, Sep 15 '15 at 11:03

score 1 · Accepted Answer · answered Sep 15 '15 at 11:09

1

And there is also a typo:

this:

com.Parameters.AddWithValue("@[Tag #", txtBxTag.Text);

should be:

com.Parameters.AddWithValue("@[Tag #]", txtBxTag.Text);

answered Sep 15 '15 at 11:09

Csab

51
2

1

This is not an answer it should be a comment or an edit suggestion. – Tamir Vered Sep 15 '15 at 11:13
Thanks Csab that actually solved the problem and successfully gave me the "Guest information saved successfully" message that I was hoping for. However now I may have a new error that I need to solve lol. – Bloodstalker Sep 15 '15 at 11:17
1

One more typo: "INSERT INTO Guest Info(" should be INSERT INTO [Guest Info]( – gd73 Sep 15 '15 at 11:40
even if Guest Info is the name of the table? – Bloodstalker Sep 15 '15 at 11:48
@Some1Pr0 agreed but Csab doesn't have enough points to make a comment. – Choco Smith Sep 15 '15 at 12:56
@Bloodstalker: you should use square brackets, especially if the identifier or the column name contains space or hyphen. Related: http://stackoverflow.com/questions/52898/what-is-the-use-of-the-square-brackets-in-sql-statements – Csab Sep 15 '15 at 13:18
@Some1Pr0: Choco Smith is right, I can make comments only to my posts yet. I will do so, as soon as I have enough points. – Csab Sep 15 '15 at 13:20

Roman Dibikhin · Answer 2 · 2015-09-15T11:37:46.167

using statement closes your connection for you. Look at MSDN for OleDbConnection case (jump to "Examples" section in the end of the article).

Your code should be like this:

private void btnSave_Click(object sender, RoutedEventArgs e)
{
    using (OleDbConnection DBConnect = new OleDbConnection())
    {
        DBConnect.ConnectionString = @"Provider=Mi...";
        DBConnect.Open();
        OleDbCommand com = new OleDbCommand("INSERT INTO ...", DBConnect);

        // Parameters goes here

        if (DBConnect.State == ConnectionState.Open)
        {
            com.ExecuteNonQuery();
        }

        //...

        // No need to close connection
    }
}

I think I understand but please correct me if I'm wrong. Would this mean I should be setting up my connection string as a separate class and calling that class with using statement mentioned? — Bloodstalker, Sep 15 '15 at 11:22

Why am I getting an "unhandled exception" when saving a database entry?

2 Answers2