Link to home
Start Free TrialLog in
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
Avatar of ashraf882
ashraf882
Flag of Bangladesh image

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

ASKER

The same connection string is being used on every other page without problem.
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.
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.
Replace below code
con.Open();
dbCommand.ExecuteNonQuery();

with

con.Open();
dbCommand.Connection = con
dbCommand.ExecuteNonQuery();
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 --------------------
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 ----------------
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.
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
ASKER CERTIFIED SOLUTION
Avatar of Mathiyazhagan
Mathiyazhagan
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.