C# executing a command after opening connection to SQL database

I have this method that gets the connection string. I first have a method that builds the connection string. Then I open the connection and finally I try to execute a SQL stored procedure against this connection. As soon as I try to execute the sp I get the message "Connection property has not been initialized" What am I missing and how do I correct this?

        public static SqlConnection GetConnection(string ConnectionString)
        {
            /* Create and open a connection to the SQL Server */
            /* Return open connection object */
            SqlConnection SQLConn = new SqlConnection();

            string loginType = Properties.Settings.Default.SQLLogintype.ToUpper();

            try
            {
                if (loginType == "WINDOWS")
                {
                    SQLConn.ConnectionString = ConnectionStringWindows(ConnectionString);
                    SQLConn.Open();
                }
                if(loginType == "SQL")
                {
                    SQLConn.ConnectionString = ConnectionStringSQL(ConnectionString);
                    SQLConn.Open();
                }

                return SQLConn;
            }
            catch (Exception ex)
            {
                Log.Write("Failed to get SQL connection to the database " + Controller.Instance.Model.SQLDatabase + ": " + ex.Message);
                Log.Write(SQLConn.ConnectionString);
                throw ex;
            }

        }
===============================================================================================
/* Build the connection string */
        public static string ConnectionStringSQL(string ConnectionString)
        {
            /* Return connection string for ".Net Framework Data Provider for SQL Server" (System.Data.SqlClient.SqlConnection) */
            return @"Data Source=" + Properties.Settings.Default.SQLSERVER + ";Initial Catalog=" + Properties.Settings.Default.SQLDatabase + ";User Id=" + Properties.Settings.Default.SQLUserID + ";Password=" + Properties.Settings.Default.SQLPassword + ";" + Properties.Settings.Default.SQLOption;
        }
===============================================================================================
            /* Open the connection */
            SqlConnection DataConnection = DataAccess.GetConnection(ConnectionString);
===============================================================================================
Execute stored procedure to insert record
            cmd.Connection = DataConnection;
            cmd.ExecuteNonQuery();
LVL 1
rwheeler23Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rwheeler23Author Commented:
I should the first two methods are located in a separate class of this project.  The execution of the sp occurs in the code behind a button on the form.
0
Nitin SontakkeDeveloperCommented:
All looks good to me too! Not been able to spot anything unusual. Except that ConnectionString parameter is actually never used. However it might be there from earlier code, hence ignored.

Where is the declaration of the cmd? Can you not debug and see that the variable DataConnection has valid connection?

Can you not just code something as follows:

using (SqlCommand cmd = new SqlCommand(DataAccess.GetConnection(ConnectionString)))
{
  cmd.ExecuteNonQuery();
}

Open in new window

0
AndyAinscowFreelance programmer / ConsultantCommented:
if (loginType == "WINDOWS")
                {
                    SQLConn.ConnectionString = ConnectionStringWindows(ConnectionString);
                    SQLConn.Open();
                }
                elseif(loginType == "SQL")
                {
                    SQLConn.ConnectionString = ConnectionStringSQL(ConnectionString);
                    SQLConn.Open();
                }
else
{
  throw new Exception("Unknown loginType - SQLConnection not made");
}


what happens with the above code (not the elseif rather than multiple else statements) ?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

rwheeler23Author Commented:
The first two methods are in a separate class. I know the connection is successfully opened because I had a typo in there and the catch was getting fired. Once I corrected that I was able to click on the button that fires the SQL insert.  I have attached the code in two separate zip files. I am going to continue debugging.
DataAccess.zip
frmArrestScrubber.zip
0
rwheeler23Author Commented:
This is where I am confused. Down in the class getting the SQL connection it has the connection string, although I do not why it does not show the password. But back in the main code the value is shown as null. Please see screenshot.
DataConnection.zip
0
AndyAinscowFreelance programmer / ConsultantCommented:
>>I know the connection is successfully opened because I had a typo in there and the catch was getting fired

See my code.  The routine you had can complete and return without any error but still not work correctly.
0
rwheeler23Author Commented:
Andy, the problem is not that the connection is made. It is when I return the sql connection that it comes back blank. I think it has something to do with how I am declaring the parameters. With the connection being made down in one class and the sql connection being return to the main program. When I test the connection down in the class it is good but when I test it above it is blank.
0
rwheeler23Author Commented:
Just so you know, I am trying to write cleaner code and this is my first attempt at using classes. I took code that works and now I have separated the code that does data access work such as opening the sql connection down into a separate class.  The code in the main section has not changed.  For whatever reason when I try to assign the sql connection, what worked before no longer returns the connection.
0
AndyAinscowFreelance programmer / ConsultantCommented:
Do you understand my suggested change?
If you don't then consider what would happen with your code if the line Properties.Settings.Default.SQLLogintype.ToUpper(); returned eg "WINNT".  The call to public static SqlConnection GetConnection(string ConnectionString) would return an SQLConnection that has not been opened - which results in the error you have when you try to use it.

I would strongly suggest you implement my suggestion and see just what happens - or step through your code and check just shich lines of code are executing.
0
rwheeler23Author Commented:
Using this exact code nothing different happens.  In my main program I have this code
            SqlConnection DataConnection = DataAccess.GetConnection();
calling the method that gets the connection string. It appears to return nothing and as soon as try to execute the stored procedure against this connection that is when I get the error message about not being assigned. If I insert breakpoint in the equal SQL section it does stop there and no where else.  The namespace of the main program and this class are the same.

        public static SqlConnection GetConnection()
        {
            /* Create and open a connection to the SQL Server */
            /* Return open connection object */
            SqlConnection SQLConn = new SqlConnection();

            string loginType = Properties.Settings.Default.SQLLogintype.ToUpper();
   
            try
            {
                if (loginType == "WINDOWS")
                {
                    SQLConn.ConnectionString = ConnectionStringWindows();
                    SQLConn.Open();
                }
                else if(loginType == "SQL")
                {
                    SQLConn.ConnectionString = ConnectionStringSQL();
                    SQLConn.Open();
                  }
                else
                {
                    throw new Exception("Unknown loginType - SQLConnection not made");
                }
                return SQLConn;
            }
            catch (Exception ex)
            {
                throw new Exception("Unknown loginType - SQLConnection not made");
                /* Log.Write("Failed to get SQL connection to the database " + Controller.Instance.Model.SQLDatabase + ": " + ex.Message);
                Log.Write(SQLConn.ConnectionString);
                throw ex; */
            }

        }
0
AndyAinscowFreelance programmer / ConsultantCommented:
Do NOT make a change like this, it is dangerous
            catch (Exception ex)
            {
                throw new Exception("Unknown loginType - SQLConnection not made");
                /* Log.Write("Failed to get SQL connection to the database " + Controller.Instance.Model.SQLDatabase + ": " + ex.Message);
                Log.Write(SQLConn.ConnectionString);
                throw ex; */
            }

Open in new window


Keep that as it was previously
            catch (Exception ex)
            {
                Log.Write("Failed to get SQL connection to the database " + Controller.Instance.Model.SQLDatabase + ": " + ex.Message);
                Log.Write(SQLConn.ConnectionString);
                throw ex; 
            }

Open in new window



In this section of code
                else if(loginType == "SQL")
                {
                    SQLConn.ConnectionString = ConnectionStringSQL();
                    SQLConn.Open();
                  }
When you single step please confirm there is no exception thrown and the SQLConn.Open() call executes without a problem.  (Maybe there is a problem with the SQLConn.ConnectionString)
0
rwheeler23Author Commented:
Message Box 1a gives me a blank
Message Box 1b gives me the complete connection string
Message Box 1c gives me the connection string minus the password.
I have attached a screenshot of debugging.

                else if(loginType == "SQL")
                {
                    MessageBox.Show("1a-The connection string is: " + SQLConn.ConnectionString);
                    ConnectionString = ConnectionStringSQL();
                    SQLConn.ConnectionString = ConnectionStringSQL();
                    MessageBox.Show("1b-The connection string is: " + SQLConn.ConnectionString);
                    SQLConn.Open();
                    MessageBox.Show("1c-The connection string is: " + SQLConn.ConnectionString);
                }
DataConnection02.bmp
0
rwheeler23Author Commented:
Is the SQLConn.Open() stripping away the ;Password=Password99?
0
AndyAinscowFreelance programmer / ConsultantCommented:
I've asked a moderator to remove the picture with the password in your 2nd last comment.

This is odd.
You have code like this:
            SqlConnection DataConnection = DataAccess.GetConnection(ConnectionString);
            cmd.Connection = DataConnection;
            cmd.ExecuteNonQuery();

What do you see in the message box with
            SqlConnection DataConnection = DataAccess.GetConnection(ConnectionString);
MessageBox.Show("Connection state is " + DataConnection.State);
            cmd.Connection = DataConnection;
            cmd.ExecuteNonQuery();


https://msdn.microsoft.com/en-us/library/system.data.connectionstate(v=vs.110).aspx
0
Snarf0001Commented:
Pretty sure the problem is just in your frmArrestScrubber constructor.
You have a class level variable called DataConnection, but in the constructor you're explicitly calling:

            /* Open the connection */
            SqlConnection DataConnection = DataAccess.GetConnection(ConnectionString);

Open in new window


By putting "SqlConnection" in front again, you're declaring another "function level" variable that's overriding the global one, setting the local variable to the value, ignoring the global, and going out of scope as soon as the function terminates.
So basically you've never actually touched the global version, and its coming back null.

Change your constructor to:

        public frmArrestScrubber()
        {
            InitializeComponent();
            //Win32.AllocConsole();

            StackTraceWanted = true;

            /* Get the import path and build the complete connection string*/
            ImportPath = Properties.Settings.Default.ImportPath;

            /* Open the connection */
            DataConnection = DataAccess.GetConnection(ConnectionString);

        }

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rwheeler23Author Commented:
Wow, you folks are great. I just learned a valuable lesson here. The layers in this stuff always catch me. I am an old dog trying to learn new tricks. Thanks also for keeping an eye on that password. It is a dummy one but it is nice to know people keep an eye out for that sort of stuff.
0
rwheeler23Author Commented:
Thank you for all your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.