ExecuteNonQuery requires an open and available Connection. The connection's current state is closed. [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

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

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

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

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

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

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

Hi,

the issue is solved mam?

Best,
Sudheep.

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

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