For each connection you need to instantiate an new sql connection and not re-use a connection unless you are using connection pooling. What is the getCon and setCon functions?
Personally you should re-write your code above to:
protected void btn_add_cat_Click(object sender, EventArgs e)
{
sqlConnection cn = New sqlconnection("connection string");
string found = "select category from ctgry where category='" + txt_cat_name.Text + "'";
SqlDataAdapter ad1 = new SqlDataAdapter(found, cn );
DataSet ds1 = new DataSet();
ad1.Fill(ds1);
if (ds1.Tables[0].Rows.Count == 0)
{
string s1 = "select max(cat_id) from ctgry";
SqlDataAdapter ad = new SqlDataAdapter(s1, cn );
DataSet ds = new DataSet();
ad.Fill(ds);
if (ds.Tables[0].Rows[0][0] == DBNull.Value)
{
cat_no = "1";
}
else
{
cat_no = (System.Convert.ToInt32(ds.Tables[0].Rows[0][0]) + 1).ToString();
}
string s = "insert into ctgry values('" + cat_no + "','" + txt_cat_name.Text + "')";
cmd = new SqlCommand(s, cn );
cmd.ExecuteNonQuery();
}
My other suggestion would be to move the data access into a seperate class and event class library. You should not really have this sort of thing in the code behind IMHO.
UPDATE
use this to get your connection if you want to run your code the way you have written it.
public static SqlConnection GetConnection()
{
try
{
con = new SqlConnection();
con.ConnectionString = @"connection String";
con.Open();
return con;
}
catch (Exception ex)
{
}
}
Also get rid of your set connection. Not needed.
Also if data access is an issue for you I would recommend the DataAccess blocks from Enterprise library 5.
http://entlib.codeplex.com/releases[
^] makes life a bit cleaner!!