Avatar of rwheeler23
rwheeler23
Flag for United States of America asked on

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();
DatabasesC#SQL

Avatar of undefined
Last Comment
rwheeler23

8/22/2022 - Mon
rwheeler23

ASKER
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.
Nitin Sontakke

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

AndyAinscow

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) ?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
rwheeler23

ASKER
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
rwheeler23

ASKER
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
AndyAinscow

>>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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
rwheeler23

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

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

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
rwheeler23

ASKER
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; */
            }

        }
AndyAinscow

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)
rwheeler23

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
rwheeler23

ASKER
Is the SQLConn.Open() stripping away the ;Password=Password99?
SOLUTION
AndyAinscow

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Snarf0001

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
rwheeler23

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

ASKER
Thank you for all your help!
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck