Avatar of eviseinc
eviseinc
 asked on

ExecuteNonQuery: Connection property has not been initialized.

OK - I have looked at several similar cases, but I just don't know enough to find the problem.  Can anyone tell me why the following code behind file is throwing the error at the bottom of the page?

-----------------  Here is the code behind file ----------------
using System;
using System.Text.RegularExpressions;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Globalization;
using AspDotNetStorefrontCore;
using System.Data.Sql;
using System.Data.SqlClient;


namespace AspDotNetStorefront
{

public partial class createaccount : SkinBase
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
      int addResult = 0;
      addResult = AddLead(txtCampaignID.Text, txtEmail.Text);
      switch (addResult) {
            case 0:
                  Message.Text = "Success";
                  goto case -1;
            case -1:
                  Message.Text = "Failure - record already exists";
                  goto default;
            default:
                  Message.Text = "Please complete ALL required fields and try again.";
                  break;
      }
      txtCampaignID.Text = "";
      txtEmail.Text = "";
}

using (SqlConnection con = new SqlConnection(DB.GetDBConn()))
                {
                    con.Open();
      dbCommand.CommandText = "spAddLead";
      dbCommand.CommandType = CommandType.StoredProcedure;
      dbCommand.Parameters.Add(new SqlParameter("@FName", SqlDbType.VarChar, 50));
      dbCommand.Parameters["@FName"].Value = txtFName;
      dbCommand.Parameters.Add(new SqlParameter("@LName", SqlDbType.VarChar, 50));
      dbCommand.Parameters["@LName"].Value = txtLName;
      dbCommand.Parameters.Add(new SqlParameter("@Email", SqlDbType.VarChar, 50));
      dbCommand.Parameters["@Email"].Value = txtEmail;
      dbCommand.Parameters.Add(new SqlParameter("@Phone", SqlDbType.VarChar, 50));
      dbCommand.Parameters["@Phone"].Value = txtPhone;
      dbCommand.Parameters.Add(new SqlParameter("@Addr1", SqlDbType.VarChar, 50));
      dbCommand.Parameters["@Addr1"].Value = txtAddr1;
      dbCommand.Parameters.Add(new SqlParameter("@Addr2", SqlDbType.VarChar, 50));
      dbCommand.Parameters["@Addr2"].Value = txtAddr2;
      dbCommand.Parameters.Add(new SqlParameter("@City", SqlDbType.VarChar, 50));
      dbCommand.Parameters["@City"].Value = txtCity;
      dbCommand.Parameters.Add(new SqlParameter("@State", SqlDbType.VarChar, 50));
      dbCommand.Parameters["@State"].Value = txtState;
      dbCommand.Parameters.Add(new SqlParameter("@Zip", SqlDbType.VarChar, 50));
      dbCommand.Parameters["@Zip"].Value = txtZip;
      dbCommand.Parameters.Add(new SqlParameter("@RequestType", SqlDbType.VarChar, 50));
      dbCommand.Parameters["@RequestType"].Value = QuoteReq;
      dbCommand.Parameters.Add(new SqlParameter("@CampaignID", SqlDbType.Int));
      dbCommand.Parameters["@CampaignID"].Value = txtCampaignID;
      dbCommand.Parameters.Add(new SqlParameter("@Result", SqlDbType.Int));
      dbCommand.Parameters["@Result"].Direction = ParameterDirection.ReturnValue;
      int commandResult = 1;
      try {
            con.Open();
            dbCommand.ExecuteNonQuery();
            commandResult = (int)dbCommand.Parameters["@Result"].Value;
      }
      catch (SqlException ex) {
            commandResult = ex.Number;
      }
      finally {
      con.Close();
      }
      return commandResult;
}
}
}
--------------- End Code Behind ----------------------

--------------  Begin Error Msg ----------------------
 ExecuteNonQuery: Connection property has not been initialized.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: ExecuteNonQuery: Connection property has not been initialized.

Source Error:

Line 71:       int commandResult = 1;
Line 72:       try {
Line 73:             using (SqlConnection con = new SqlConnection(DB.GetDBConn()))
Line 74:                 {
Line 75:                     con.Open();


Source File: \\fs1-n02\stor2wc1dfw1\400154\www.vinylreplacementwindowsquote.com\web\content\_Raleigh.aspx.cs    Line: 73

Stack Trace:

[InvalidOperationException: ExecuteNonQuery: Connection property has not been initialized.]
   System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async) +4876133
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +105
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
   AspDotNetStorefront.createaccount.AddLead(String txtCampaignID, String txtEmail) in \\fs1-n02\stor2wc1dfw1\400154\www.vinylreplacementwindowsquote.com\web\content\_Raleigh.aspx.cs:73
   AspDotNetStorefront.createaccount.Button1_Click(Object sender, EventArgs e) in \\fs1-n02\stor2wc1dfw1\400154\www.vinylreplacementwindowsquote.com\web\content\_Raleigh.aspx.cs:26
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +110
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565
.NET ProgrammingEditors IDEsC#

Avatar of undefined
Last Comment
eviseinc

8/22/2022 - Mon
ashraf882

Seems it is a connectivity problem, Please check the connection string.
eviseinc

ASKER
The same connection string is being used on every other page without problem.
Mathiyazhagan

HI,
seems you are not assining Connection object.do you assignig con object while creating dbCommand ? or else you should assign it like,
    dbCommand.Connection = con;

hope this helps.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
vora_bhaumik

The problem is you haven't assigned the connection to the command object.
You missed objCmd.Connection = Conn; before calling the execute command.
It should be like this.
akhileshcoer

Replace below code
con.Open();
dbCommand.ExecuteNonQuery();

with

con.Open();
dbCommand.Connection = con
dbCommand.ExecuteNonQuery();
eviseinc

ASKER
OK - here is the current code.  I made the changes recommended by akhileshcoer, I now receive a different error message.  Do I have to close the connection then reopen it?

------------------  start error -------------------------
 The connection was not closed. The connection's current state is open.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: The connection was not closed. The connection's current state is open.

Source Error:

Line 73:       int commandResult = 1;
Line 74:       try {
Line 75:             dbCommand.ExecuteNonQuery();
Line 76:             commandResult = (int)dbCommand.Parameters["@Result"].Value;
Line 77:       }

-------------------------  end error ---------------


------------------------- start code --------------------
 The connection was not closed. The connection's current state is open.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: The connection was not closed. The connection's current state is open.

Source Error:

Line 73:       int commandResult = 1;
Line 74:       try {
Line 75:             dbCommand.ExecuteNonQuery();
Line 76:             commandResult = (int)dbCommand.Parameters["@Result"].Value;
Line 77:       }
--------------------------  end code --------------------
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
eviseinc

ASKER
SORRY - please ignore previous post.  Here is the new error message and code:

------------------  begin error --------------------
 The connection was not closed. The connection's current state is open.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: The connection was not closed. The connection's current state is open.

Source Error:

Line 73:       int commandResult = 1;
Line 74:       try {
Line 75:             con.Open();
Line 76:             dbCommand.Connection = con;
Line 77:             dbCommand.ExecuteNonQuery();

----------------------------  end error ----------------------------

---------------------------- begin code ---------------------------

using System;
using System.Text.RegularExpressions;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Globalization;
using AspDotNetStorefrontCore;
using System.Data.Sql;
using System.Data.SqlClient;


namespace AspDotNetStorefront
{

public partial class createaccount : SkinBase
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
      int addResult = 0;
      addResult = AddLead(txtCampaignID.Text, txtEmail.Text);
      switch (addResult) {
            case 0:
                  Message.Text = "Success";
                  goto case -1;
            case -1:
                  Message.Text = "Failure - record already exists";
                  goto default;
            default:
                  Message.Text = "Please complete ALL required fields and try again.";
                  break;
      }
      txtCampaignID.Text = "";
      txtEmail.Text = "";
}

int AddLead(string txtCampaignID, string txtEmail)
{
      SqlCommand dbCommand = new SqlCommand();
      dbCommand.CommandText = "spAddLead";
      dbCommand.CommandType = CommandType.StoredProcedure;
      SqlConnection con = new SqlConnection(DB.GetDBConn());
      con.Open();
      dbCommand.Parameters.Add(new SqlParameter("@FName", SqlDbType.VarChar, 50));
      dbCommand.Parameters["@FName"].Value = txtFName;
      dbCommand.Parameters.Add(new SqlParameter("@LName", SqlDbType.VarChar, 50));
      dbCommand.Parameters["@LName"].Value = txtLName;
      dbCommand.Parameters.Add(new SqlParameter("@Email", SqlDbType.VarChar, 50));
      dbCommand.Parameters["@Email"].Value = txtEmail;
      dbCommand.Parameters.Add(new SqlParameter("@Phone", SqlDbType.VarChar, 50));
      dbCommand.Parameters["@Phone"].Value = txtPhone;
      dbCommand.Parameters.Add(new SqlParameter("@Addr1", SqlDbType.VarChar, 50));
      dbCommand.Parameters["@Addr1"].Value = txtAddr1;
      dbCommand.Parameters.Add(new SqlParameter("@Addr2", SqlDbType.VarChar, 50));
      dbCommand.Parameters["@Addr2"].Value = txtAddr2;
      dbCommand.Parameters.Add(new SqlParameter("@City", SqlDbType.VarChar, 50));
      dbCommand.Parameters["@City"].Value = txtCity;
      dbCommand.Parameters.Add(new SqlParameter("@State", SqlDbType.VarChar, 50));
      dbCommand.Parameters["@State"].Value = txtState;
      dbCommand.Parameters.Add(new SqlParameter("@Zip", SqlDbType.VarChar, 50));
      dbCommand.Parameters["@Zip"].Value = txtZip;
      dbCommand.Parameters.Add(new SqlParameter("@RequestType", SqlDbType.VarChar, 50));
      dbCommand.Parameters["@RequestType"].Value = QuoteReq;
      dbCommand.Parameters.Add(new SqlParameter("@CampaignID", SqlDbType.Int));
      dbCommand.Parameters["@CampaignID"].Value = txtCampaignID;
      dbCommand.Parameters.Add(new SqlParameter("@Result", SqlDbType.Int));
      dbCommand.Parameters["@Result"].Direction = ParameterDirection.ReturnValue;
      int commandResult = 1;
      try {
            con.Open();
            dbCommand.Connection = con;
            dbCommand.ExecuteNonQuery();
            commandResult = (int)dbCommand.Parameters["@Result"].Value;
      }
      catch (SqlException ex) {
            commandResult = ex.Number;
      }
      finally {
      con.Close();
      }
      return commandResult;
}
}
}
------------------------- end code ----------------
Mathiyazhagan

it is the error because you are trying to open already opened connection.notice that you are using
con.Open() twice.remove this statement here

  SqlConnection con = new SqlConnection(DB.GetDBConn());
      con.Open();

because,you are opening connection here:
  try {
            con.Open();
            dbCommand.Connection = con;
            dbCommand.ExecuteNonQuery();
            commandResult = (int)dbCommand.Parameters["@Result"].Value;
      }

Hope this helps.
eviseinc

ASKER
Thanks for your help.  When I do that, I get the following message:

Compiler Error Message: CS0103: The name 'con' does not exist in the current context
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Mathiyazhagan

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.
eviseinc

ASKER
Thanks Mathiyazhagan... that appears to have corrected the connection issues.  I now have a whole new problem though.  I will post a new question, since the new error is related to passing textbox controls from a form to a stored procedure.

Thanks again.