ExecuteNonQuery requires an open and available Connection. The connection's current state is closed. [Resolved]

Posted by Sudheep.grandhe under C# on 11/22/2011 | Points: 10 | Views : 36098 | Status : [Member] | Replies : 9
Hi,

It shows the following error.please fix my error.please do the needful.

SqlConnection conn=new SqlConnection ("data source=mars-104;initial catalog=marsweb;integrated security=true");
SqlCommand cmd1=new SqlCommand ("select particulars,quantity,perprice,amount from tbl_report1 where orderno=@id",conn );
cmd1 .Parameters .AddWithValue ("@id",id );
SqlDataAdapter da1=new SqlDataAdapter (cmd1 );
if (con.State == ConnectionState.Closed)
{
con.Open();
}
DataTable dt=new DataTable ();
cmd1 .ExecuteNonQuery ();//error:ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
da1 .Fill (dt );
GridView1 .DataSource =dt ;
con .Close ();
vatamount ();
vatdata ();
netamount ();
txt_quantity .Text ="";
txt_perprice .Text ="";
txt_amount .Text ="";


}

Best,
Sudheep.



Responses

Posted by: Blessyjees on: 11/22/2011 [Member] Bronze | Points: 50

Up
0
Down

Resolved
Hi Sudheep,

Your sqlconnection object is "conn", but you are using "con" for checking state and open. Please corect this and try again

if (conn.State == ConnectionState.Closed) 

{
conn.Open();
}


Blessy Baby
Digitalmesh Softech pvt Ltd
https://blessybaby.wordpress.com/

Sudheep.grandhe, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ndebata on: 11/22/2011 [Member] Starter | Points: 25

Up
0
Down
Replace
SqlDataAdapter da1=new SqlDataAdapter (cmd1 ); 

With
SqlDataAdapter da1=new SqlDataAdapter (cmd1 ,con); 


Sudheep.grandhe, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sudheep.grandhe on: 11/22/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

it shows the error.

Error 6: Argument '2': cannot convert from 'System.Data.SqlClient.SqlConnection' to 'string'

private void btn_add_Click(object sender, EventArgs e)
{
//SqlConnection con=new SqlConnection ("data source=mars-104;initial catalog=marsweb;integrated security=true");
DBConnection db = new DBConnection();
SqlConnection con = db.opencon();
SqlCommand cmd = new SqlCommand("insert into tbl_report1(particulars,quantity,perprice,amount,invoicedate,ondate)values(@particulars,@quantity,@perprice,@amount,@invoicedate,@ondate)", con);
cmd .Parameters .AddWithValue ("@invoiceid",txt_invoiceno .Text );
cmd .Parameters .AddWithValue ("@particulars",cmb_particulars .Text );
cmd .Parameters .AddWithValue ("@quantity",txt_quantity .Text );
cmd .Parameters .AddWithValue ("@perprice",txt_perprice .Text );
cmd .Parameters .AddWithValue ("@amount",txt_amount .Text );
cmd .Parameters .AddWithValue ("@orderno",txt_orderno .Text );
cmd .Parameters .AddWithValue ("@compname",cmb_companyname .Text );
cmd.Parameters.AddWithValue("@invoicedate", txt_invoicedate.Text);
cmd.Parameters.AddWithValue("@ondate", txt_ondate.Text);
SqlDataAdapter da=new SqlDataAdapter ();
if (con.State == ConnectionState.Closed)
{
con.Open();
}
cmd .ExecuteNonQuery ();
con .Close ();

SqlConnection conn=new SqlConnection ("data source=mars-104;initial catalog=marsweb;integrated security=true");
SqlCommand cmd1=new SqlCommand ("select particulars,quantity,perprice,amount from tbl_report1 where orderno=@id",conn );
cmd1 .Parameters .AddWithValue ("@id",id );
SqlDataAdapter da1 = new SqlDataAdapter(cmd1, con);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
DataTable dt=new DataTable ();
cmd1 .ExecuteNonQuery ();
da1 .Fill (dt );
GridView1 .DataSource =dt ;
con .Close ();
vatamount ();
vatdata ();
netamount ();
txt_quantity .Text ="";
txt_perprice .Text ="";
txt_amount .Text ="";


}

Best,
Sudheep.

Sudheep.grandhe, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sudheep.grandhe on: 11/22/2011 [Member] Starter | Points: 25

Up
0
Down
Hi mam,

The previous error was ok.But, now i am getting sqlexception .please fix my error.please do the needful.
Error:Conversion failed when converting the nvarchar value 'HB001' to data type int.
public partial class UpdateCustomerInvoice : Form
{
int id=3;//if i am not declaring the value default it will takes as 0 only.
int id2=1;
}

Best,
Sudheep.

Sudheep.grandhe, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Blessyjees on: 11/22/2011 [Member] Bronze | Points: 25

Up
0
Down
Hi,

This error means that you are trying to convert string value to integer value. Check your code and corect this.

Blessy Baby
Digitalmesh Softech pvt Ltd
https://blessybaby.wordpress.com/

Sudheep.grandhe, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sudheep.grandhe on: 11/22/2011 [Member] Starter | Points: 25

Up
0
Down
Hi mam,

I am not getting any idea what's error in my coding.can you please help me.i want to update the customer address.can you please send me the sample coding.please do the needful.

Best,
Sudheep.

Sudheep.grandhe, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Blessyjees on: 11/22/2011 [Member] Bronze | Points: 25

Up
0
Down
Hi,

Add your code. I will check and try to find an issue

Blessy Baby
Digitalmesh Softech pvt Ltd
https://blessybaby.wordpress.com/

Sudheep.grandhe, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sudheep.grandhe on: 11/22/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

the issue is solved mam?

Best,
Sudheep.

Sudheep.grandhe, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Priyagoel_24 on: 9/10/2013 [Member] Starter | Points: 25

Up
0
Down
hello Sir,
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;


public partial class _Default : System.Web.UI.Page
{

SqlConnection cn = new SqlConnection();
protected void Page_Load(object sender, EventArgs e)
{
cn.ConnectionString = ConfigurationManager.ConnectionStrings["payroll"].ConnectionString;
if (cn.State == ConnectionState.Closed)
{

}
}
protected void button_Click(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText ="insert into payroll(nameofemployee,employeecode,designation,department) values (nameofemployee,employeecode,designation,department)";
cmd.Parameters.Add("nameemployee", SqlDbType.VarChar, 50).Value = txtnameofemployee.Text;
cmd.Parameters.Add("employeecode", SqlDbType.VarChar, 50).Value = txtemployeecode.Text;
cmd.Parameters.Add("Designation", SqlDbType.VarChar, 50).Value = txtdesignation.Text;
cmd.Parameters.Add("department", SqlDbType.VarChar, 50).Value = txtdepartment.Text;
cmd.Connection = cn;
//cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
txtnameofemployee.Text = "";
txtemployeecode.Text = "";
txtdesignation.Text = "";
txtdepartment.Text = "";
}
}
Server Error in '/a2z' Application.
ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
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 requires an open and available Connection. The connection's current state is closed.

Source Error:

Line 34: cmd.Connection = cn;
Line 35: //cn.Open();
Line 36: cmd.ExecuteNonQuery();
Line 37: cn.Close();
Line 38: txtnameofemployee.Text = ""

Sudheep.grandhe, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response