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

VS C# Connection Property Not Been Initialized

I have upgraded a program from VS C# 2008 to VS C# 2010. It is now set for .Net FW 3.5 where before it was .Net 2.0

When I run the code and this routine is called, I am now getting the message "Connection Property Has Not Been Initialized" on the cmd.ExecuteNonQuery() line. I tried inserting cmd.Connection.Open() before the execute but then I get "Object has not been set to an instance of an object" message. It appears something is different. How do I structure this code so the sp will fire?

private void InsertReasonForChange()
        {
            try
            {
                SqlCommand cmd = new SqlCommand();

                /* Insert/Update the reason for the sell price change */
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@SOPNUMBE", SOPNUMBE));
                cmd.Parameters.Add(new SqlParameter("@SOPTYPE", SOPTYPE));
                cmd.Parameters.Add(new SqlParameter("@LNITMSEQ", LNITMSEQ));
                cmd.Parameters.Add(new SqlParameter("@CMPSEQNM", CMPSEQNM));
                cmd.Parameters.Add(new SqlParameter("@USERID", GP_User_ID));
                cmd.Parameters.Add(new SqlParameter("@REASON", txtReason.Text));

                cmd.CommandText = "rbs_ChangeSellPriceReason";

                cmd.Connection = DataConnection;
               
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                string eMsg = "003: ERROR: " + ex.Message;
                if (stackTraceWanted) eMsg += "\n" + ex.StackTrace;
                if (stackTraceWanted) MessageBox.Show(eMsg);
            }
        }
C#.NET Programming

Avatar of undefined
Last Comment
rwheeler23

8/22/2022 - Mon
kaufmed

Where is DataConnection initialized? You should have a line similar to:

SqlConnection DataConnection = new SqlConnection("your connection string here");

Open in new window

gt2847c

You need an open SqlConnection in order to do what you're attempting...  You appear to be assigning the connection:

cmd.Connection = DataConnection;

however I don't see where DataConnection is coming from in your code snippet above.

DataConnection would have to be a SqlConnection() created somewhere.  It would also need to be opened before you execute:

cmd.ExecuteNonQuery();

Typically you will want to open the connection, execute, then close.  Often this is done inside a using statement so that you don't leave resources hanging around.

Microsoft's example code looks like this:

private static void CreateCommand(string queryString,
    string connectionString)
{
    using (SqlConnection connection = new SqlConnection(
               connectionString))
    {
        SqlCommand command = new SqlCommand();
        command.Connection = connection;
        command.CommandTimeout = 15;
        command.CommandType = CommandType.Text;
        command.CommandText = queryString;

        connection.Open();
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            Console.WriteLine(String.Format("{0}, {1}",
                reader[0], reader[1]));
        }
    }
}

Open in new window

gt2847c

If, by chance, DataConnection is a property set on your object class containing InsertReasonForChange, you may need to put some code in to verify that DataConnection was properly set up before you try and use it in your function.  It may be that your code is failing due to your function assuming that DataConnection initialization was handled before InsertReasonForChange is called and that not being the case.  Placing some error checking code in to validate that DataConnection is properly initialized may help you correct this problem and prevent others.

This could be in the form of a private internal flag set when DataConnection is initialized or some equivalent sort of mechanism.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
rwheeler23

ASKER
I have attached the entire section of this code. We have a connect reference call GPConnet that actually makes the connection. I have used this for years just as in this code but now that I have upgraded it no longer appears to work as originaly designed. There never was a line that actually opens the connection. I know it is open because I get an error message if I try to open it while it is open. I get the message "Object has not been set to an instance of an object" . If you could provide some guidance as to how to validate the connection. All I have ever done before is create the connection, do whatever I need to do and then close the connection upon termination.

All this code is doing is popping up a box teling the user the change something and asks for a reason for the change. I need this reason written back to a table in the database. InsertReasonForChange is called when the Save button is clicked.
CodeSnippet.txt
rwheeler23

ASKER
P.S. I put in some breakpoints and I can see the GP _User_ID has the correct value however, the value of DataConnection is null. So I do not see the variables returne dfrom GPConnect have values but the connection is null.
SOLUTION
kaufmed

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
gt2847c

Another thing to think about is variable naming.  Current convention has private class variables named with a leading underscore to help you differentiate what your setting.

Private class variables
private object _myObject;

Private/Public/Protected Parameter:
public object MyObject { get;set; }

Variables local to function:
object myObject;

The capitalization and the leading underscore can make it a lot easier to figure out what it is you are setting and makes code easier to read.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
rwheeler23

ASKER
Damn, you guys are good! This is a great teachable moment and I am learning from the best. I make these minor changes and there goes my sp. Thank you so very much!