Not allowed to change the connection string property the connection current state is open

Question

5.00/5 (1 vote)

See more:

Hi..........Every one please help.When i run my code.It gives me the following error.
"Not allowed to change the connection string property.The connection current state is open."
Here is my code.Any help will most be appreciated. I will be waiting for reply.
Thanks in advance to all of you.
..................................................................................................

C#

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

using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using RMSLibrary;

namespace RMS
{
    public partial class InterfaceCityCountry : Window
    {
        CreateAgentAccount creatAgentWin = new CreateAgentAccount();

        CitiesCountriesDAL citCountr = new CitiesCountriesDAL();
        string str = ConfigurationManager.ConnectionStrings["RMSDatabaseSqlProvider"].ConnectionString;

        public InterfaceCityCountry()
        {
            InitializeComponent();
            CenterWindowOnScreen();
            
            LoadListBoxCitiesCountries();
        }
        private void CenterWindowOnScreen()
        {
            double screenWidth = System.Windows.SystemParameters.PrimaryScreenWidth;
            double screenHeight = System.Windows.SystemParameters.PrimaryScreenHeight;
            double windowWidth = this.Width;
            double windowHeight = this.Height;
            this.Left = (screenWidth / 2) - (windowWidth / 2);
            this.Top = (screenHeight / 2) - (windowHeight / 2);
        }
        private void LoadListBoxCitiesCountries()
        {
            try
            {
                citCountr.OpenConnection(str);
                SqlDataAdapter sda = citCountr.GetCities();
                DataSet ds = new DataSet();

                sda.Fill(ds);

                lbCities.ItemsSource = null;
                lbCities.ItemsSource = ds.Tables[0].DefaultView;

                SqlDataAdapter sda2 = citCountr.GetCountries();
                DataSet ds2 = new DataSet();

                sda2.Fill(ds2);

                lbCountries.ItemsSource = null;
                lbCountries.ItemsSource = ds2.Tables[0].DefaultView;

                citCountr.CloseConnnection();
            }
            catch (Exception ex)
            {
                MyErrorMessage(ex);

            }
            finally
            {
                citCountr.CloseConnnection();
            }
            
        }
        private void btnDeleteCity_Click(object sender, RoutedEventArgs e)
        {
            
           
        }

        private void btnDeleteCountry_Click(object sender, RoutedEventArgs e)
        {
            
        }
        private void MyErrorMessage(Exception ex)
        {
            string messageBoxText = "Error Occured! Try Again.\n\n" + ex.Message;
            string caption = "Error";
            MessageBoxButton button = MessageBoxButton.OK;
            MessageBoxImage icon = MessageBoxImage.Error;
            MessageBox.Show(messageBoxText, caption, button, icon);
        }

        private void lbCities_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            if (lbCities.SelectedIndex > -1)
            {
                tbCity.Text = ((DataRowView)lbCities.SelectedItem).Row.ItemArray[1].ToString();
                btnUpdateCity.IsEnabled = true;
            }
        }

        private void lbCountries_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            if (lbCountries.SelectedIndex > -1)
            {
                tbCountry.Text = ((DataRowView)lbCountries.SelectedItem).Row.ItemArray[1].ToString();
                btnUpdateCountry.IsEnabled = true;
            }
        }

        private void btnUpdateCity_Click(object sender, RoutedEventArgs e)
        {
            bool created = true;
            
            DataClassesDataContext dc = new DataClassesDataContext();
            try
            {
                string id = lbCities.SelectedValue.ToString();
                if ((from c in dc.Cities where c.Name == tbCity.Text select c).Count() == 1)
                {
                    MessageBox.Show("Name already Exist. Choose a different name");
                    created = false;
                }
                else
                {
                    var query = (from c in dc.Cities
                                 where c.CityID == int.Parse(id)
                                 select c).First();

                    query.Name = tbCity.Text;
                    dc.SubmitChanges();
                
                }
                
            }
            catch(Exception ex)
            {
                created = false;
                MyErrorMessage(ex);
            }

            if (created)
            {
                MessageBox.Show("Successfull");
                tbCity.Text = string.Empty;
                LoadListBoxCitiesCountries();
            }
                        
        }

        private void btnUpdateCountry_Click(object sender, RoutedEventArgs e)
        {
            bool created = true;
            DataClassesDataContext dc = new DataClassesDataContext();

            try
            {
                string id = lbCountries.SelectedValue.ToString();

                if((from c in dc.Countries where c.Name == tbCountry.Text select c).Count() == 1)
                {
                    MessageBox.Show("Name already Exist. Choose a different name");
                    created = false;
                }
                else
                {
                    var query = (from c in dc.Countries
                                 where c.CountryID == int.Parse(id)
                                 select c).First();

                    query.Name = tbCountry.Text;
                    dc.SubmitChanges();
                }
            }
            catch (Exception ex)
            {
                created = false;
                MyErrorMessage(ex);
            }

            if (created)
            {
                MessageBox.Show("Successfull");
                tbCountry.Text = string.Empty;
                LoadListBoxCitiesCountries();
            }
                    
        }

        private void City_CanExecute(object sender, CanExecuteRoutedEventArgs e)
        {
            bool hasError = Validation.GetHasError(tbCity);

            e.CanExecute = !hasError;
        }

        private void City_Executed(object sender, ExecutedRoutedEventArgs e)
        {
            try
            {
                citCountr.OpenConnection(str);

                if (!(citCountr.CheckAlreadyExistCity(tbCity.Text.Trim())))
                {
                    citCountr.InsertCity(tbCity.Text.ToString().Trim());

                    LoadListBoxCitiesCountries();

                    citCountr.CloseConnnection();

                    MessageBox.Show("Added Successfully!");

                    creatAgentWin.LoadCitiesAndCountries();

                    tbCity.Text = "";
                    tbCountry.Text = "";
                }
                else
                {
                    citCountr.CloseConnnection();
                    string msgtext = "City with same name already exist. You can't add same city twice. Try with Different name!";
                    string caption = "Error";
                    MessageBoxButton button = MessageBoxButton.OK;
                    MessageBoxImage image = MessageBoxImage.Error;
                    MessageBox.Show(msgtext, caption, button, image).ToString();
                }

            }
            catch (Exception ex)
            {
                string messageBoxText = "Error occured! Transection Failed. Try again";
                string caption = "Error";
                MessageBoxButton button = MessageBoxButton.OK;
                MessageBoxImage icon = MessageBoxImage.Error;
                MessageBox.Show(messageBoxText, caption, button, icon);


            }
            finally
            {
                citCountr.CloseConnnection();
            }
        }

        private void Country_CanExecute(object sender, CanExecuteRoutedEventArgs e)
        {
            bool hasError = Validation.GetHasError(tbCountry);

            e.CanExecute = !hasError;
        }

        private void Country_Executed(object sender, ExecutedRoutedEventArgs e)
        {
            try
            {
                citCountr.OpenConnection(str);
                if (!(citCountr.CheckAlreadyExistCountry(tbCountry.Text.Trim())))
                {
                    citCountr.InsertCountry(tbCountry.Text.ToString().Trim());

                    LoadListBoxCitiesCountries();
                    citCountr.CloseConnnection();

                    MessageBox.Show("Added Successfully!");

                    creatAgentWin.LoadCitiesAndCountries();
                    tbCity.Text = "";
                    tbCountry.Text = "";
                }
                else
                {
                    citCountr.CloseConnnection();

                    string msgtext = "Country with with same name already exist. You can't add same country twice. Try with Different name!";
                    string caption = "Error";
                    MessageBoxButton button = MessageBoxButton.OK;
                    MessageBoxImage image = MessageBoxImage.Error;
                    MessageBox.Show(msgtext, caption, button, image).ToString();
                }


            }
            catch (Exception ex)
            {
                this.MyErrorMessage(ex);

            }
            finally
            {
                citCountr.CloseConnnection();
            }
            
        }
    }
}

Posted 30-Mar-14 19:52pm

SADIQ BJ

Updated 30-Mar-14 22:45pm

v4

Add a Solution

Comments

Meysam Toluie 31-Mar-14 2:25am

It will be more efficient if you put the part of code which cause the error.

SADIQ BJ 31-Mar-14 2:54am

Meysam Tolouee please check it now i have updated my question.

Orcun Iyigun 31-Mar-14 3:12am

Which line of code you have that exception thrown?

SADIQ BJ 31-Mar-14 4:42am

I have done exception handling on my code. It does not throw any exception.But it raises the following error. That "Not allowed to change the connection string property.The connection current state is open."
I do not know that where is the exception occur

Orcun Iyigun 31-Mar-14 4:56am

Do you know any means of debugging? Start using the debugger tool? If you want to understand where it throws the exception, comment the try-catch-finally statements. And your exception detail is pretty straight-forward. You open a connection and you don't close it and.

Before assigning connection string to your connection object make sure your connection object is closed or it is not opened.
So check your connection:
close it by sqlconn.Close();
or check it by
If( sqlconn.State == ConnectionState.Closed)
{
}

SADIQ BJ 31-Mar-14 4:58am

how can i debug please help me

Orcun Iyigun 31-Mar-14 5:04am

By putting a breakpoint. There are tutorials online. Google it! Be it your best friend!

SADIQ BJ 31-Mar-14 5:32am

The exception occur at the line no =5 of the following code. i checked private void LoadListBoxCitiesCountries() { try { citCountr.OpenConnection(str);// Here Exception raise. SqlDataAdapter sda = citCountr.GetCities(); DataSet ds = new DataSet(); sda.Fill(ds); lbCities.ItemsSource = null; lbCities.ItemsSource = ds.Tables[0].DefaultView; SqlDataAdapter sda2 = citCountr.GetCountries(); DataSet ds2 = new DataSet(); sda2.Fill(ds2); lbCountries.ItemsSource = null; lbCountries.ItemsSource = ds2.Tables[0].DefaultView; citCountr.CloseConnnection(); }

Meysam Toluie 31-Mar-14 4:46am

Still you didn't specify where the exception occurs.
Anyway when I was reviewing your code, I see another mistake; And that is:
The final block will execute anyway. So you do not need to rewrite its code in try block!

You rewrite
citCountr.CloseConnnection();
several times.

SADIQ BJ 31-Mar-14 4:56am

Meysam Tolouee actully that is not my code.This application is developed by some one other.Which is gone away from the organization.And now i am fixing bugs and error.
And that person is not in contact with us that is the reason.
But please take another review that where is the mistake in the code.
Thanks for your help.And i will be waiting for your next reply.

Meysam Toluie 31-Mar-14 5:00am

If specifying the line which cause the exception is hard to you I suggest to leave coding as soon as possible.
How can I fix the code when I do not know where exception occurs?!

SADIQ BJ 31-Mar-14 5:31am

The exception occur at the line no =5 of the following code. i checked private void LoadListBoxCitiesCountries() { try { citCountr.OpenConnection(str);// Here Exception raise. SqlDataAdapter sda = citCountr.GetCities(); DataSet ds = new DataSet(); sda.Fill(ds); lbCities.ItemsSource = null; lbCities.ItemsSource = ds.Tables[0].DefaultView; SqlDataAdapter sda2 = citCountr.GetCountries(); DataSet ds2 = new DataSet(); sda2.Fill(ds2); lbCountries.ItemsSource = null; lbCountries.ItemsSource = ds2.Tables[0].DefaultView; citCountr.CloseConnnection(); }

SADIQ BJ 31-Mar-14 5:15am

The exception occur at the line no =5 of the following code. i checked
private void LoadListBoxCitiesCountries()
{
try
{
citCountr.OpenConnection(str);// Here Exception raise.
SqlDataAdapter sda = citCountr.GetCities();
DataSet ds = new DataSet();

sda.Fill(ds);

lbCities.ItemsSource = null;
lbCities.ItemsSource = ds.Tables[0].DefaultView;

SqlDataAdapter sda2 = citCountr.GetCountries();
DataSet ds2 = new DataSet();

sda2.Fill(ds2);

lbCountries.ItemsSource = null;
lbCountries.ItemsSource = ds2.Tables[0].DefaultView;

citCountr.CloseConnnection();
}

Meysam Toluie 31-Mar-14 5:35am

Solution 1 didn't solve your problem?

SADIQ BJ 31-Mar-14 5:43am

NO i set it according to my code but still the same error occured

Meysam Toluie 31-Mar-14 5:59am

Can you put the codes of CitiesCountriesDAL class?

SADIQ BJ 31-Mar-14 6:04am

what i do not understand

SADIQ BJ 31-Mar-14 5:32am

The exception occur at the line no =5 of the following code. i checked private void LoadListBoxCitiesCountries() { try { citCountr.OpenConnection(str);// Here Exception raise. SqlDataAdapter sda = citCountr.GetCities(); DataSet ds = new DataSet(); sda.Fill(ds); lbCities.ItemsSource = null; lbCities.ItemsSource = ds.Tables[0].DefaultView; SqlDataAdapter sda2 = citCountr.GetCountries(); DataSet ds2 = new DataSet(); sda2.Fill(ds2); lbCountries.ItemsSource = null; lbCountries.ItemsSource = ds2.Tables[0].DefaultView; citCountr.CloseConnnection(); }

SADIQ BJ 1-Apr-14 3:06am

I am still waiting for your reply.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sampath Lokuge · Accepted Answer · 2014-03-30T23:08:00

Please check below links.May be helped to you.

Note: Sharing your connections is the problem.Below one is just a pseudo code.Adjust it according to your situation.

C#

If( sqlconn.State == ConnectionState.Closed)//if closed
{
  citCountr.OpenConnection(str);
}
else{
   //use already opened connection string without opening it again.
}

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

"Not allowd to change the"connecionstring property".The connection's current state is open "[^]

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

SADIQ BJ · Accepted Answer · 2014-04-01T01:35:00

I have fixed my problem Because the connection was already open.I have checked my connection with if else statement to find that where is the connection open.
for checking the connection wither the connection is open or close. use the following code

if (cn.State == ConnectionState.Open)
{
MessageBox.Show("open");
}
else {
MessageBox.Show("closed");
}
...................................................................................................
and hence i put the following line of code

citCountr.CloseConnnection();

Before
citCountr.CloseConnnection();

and my problem gone away..................
Thanks.........and happy..................

Not allowed to change the connection string property the connection current state is open

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us