Click here to Skip to main content
16,016,770 members
Search within:


I have a problem saving into a database
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
C#
SQL
I am developing a solution that has master and addEmployee as a table and a form respectively. The solution will save data into master using addEmployee form. The solution uses C# as front-end and sql2008 database as back-end. After compiling the solution, the back-end does not have any data and i receive an error "
Additional information: The connection was not closed. The connection's current state is open.
. Please can anyone help me?

What I have tried:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;


namespace FHAMortgageBank
{
    public partial class Add_Employee : Form
    {
        SqlConnection con = new SqlConnection("Data Source=AKINTOMIDE-HP\\SQLEXPRESS;Initial Catalog=fham;Integrated Security=True");
        SqlCommand cmd;
        SqlDataAdapter adapt;
        private DataViewManager dviewmanager;
        //ID variable used in Updating and Deleting Record  
        int SN = 0;

        public Add_Employee()
        {
            InitializeComponent();
            ClearData();// Method
            FillBank();
            FillStatus();
            FillPenAdmin();
            FillYear();
            FillGrade();
            FillDpt();
        }

        private void frmMain_Enter(object sender, EventArgs e)
        {

        }

        private void BntSave_Click(object sender, EventArgs e)
        {

            if (this.txteid.Text != "" && txtStaf.Text != "")
            {

                con.Open();
                cmd = new SqlCommand("insert into master (eid,mon,yr,dat,staf,adrs,coy,loc,dpt,grd,datead,datemod,bsal,tran,hous,lunc,uti,furn,harz,educ,housM,dres,risk,veh,driv,dome,pfs,nhf,nhfs,nhis,oded,otherpay,housU,dressU,staffM,Thrift,accno,bank,email,penadmin,pennum,status,dupfront,picstaffpix) values(@eid,@mon,@yr,@dat,@staf,@adrs,@coy,@loc,@dpt,@grd,@datead,@datemod,@bsal,@tran,@hous,@lunc,@uti,@furn,@harz,@educ,@housM,@dres,@risk,@veh,@driv,@dome,@pfs,@nhf,@nhfs,@nhis,@oded,@otherpay,@housU,@dressU,@staffM,@Thrift,@accno,@bank,@email,@penadmin,@pennum,@status,@dupfront,@picstaffpix)", con);
                cmd.Parameters.AddWithValue("@eid", txteid.Text);
                cmd.Parameters.AddWithValue("@mon", cmbMon.Text);
                cmd.Parameters.AddWithValue("@yr", cmbYr.Text);
                cmd.Parameters.AddWithValue("@dat", SqlDbType.Date);
                cmd.Parameters.AddWithValue("@datead", SqlDbType.DateTime);
                cmd.Parameters.AddWithValue("@datemod", SqlDbType.DateTime);
                cmd.Parameters.AddWithValue("@staf", txtStaf.Text);
                cmd.Parameters.AddWithValue("@adrs", txtAdrs);
                cmd.Parameters.AddWithValue("@coy", txtCoy.Text);
                cmd.Parameters.AddWithValue("@dpt", cmbDpt.Text);
                cmd.Parameters.AddWithValue("@loc", cmbLoc.Text);
                cmd.Parameters.AddWithValue("@grd", CmbGrd.Text);
                cmd.Parameters.AddWithValue("@accno", TxtAccno.Text);
                cmd.Parameters.AddWithValue("@email", txtemail.Text);
                cmd.Parameters.AddWithValue("@picstaffpix", picStafPix);
                cmd.Parameters.AddWithValue("@bank", cmbBank.Text);
                cmd.Parameters.AddWithValue("@penadmin", cmbPenAdmin.Text);
                cmd.Parameters.AddWithValue("@status", cmbStatus.Text);
                cmd.Parameters.AddWithValue("@pennum", txtPenNum.Text);
                cmd.Parameters.AddWithValue("@basl", txtBSal.Text);
                cmd.Parameters.AddWithValue("@dres", txtDres.Text);
                cmd.Parameters.AddWithValue("@dupfront", txtDUpfront.Text);
                cmd.Parameters.AddWithValue("@educ", txtEduc.Text);
                cmd.Parameters.AddWithValue("@furn", txtFurn.Text);
                cmd.Parameters.AddWithValue("@harz", txtHarz.Text);
                cmd.Parameters.AddWithValue("@tran", txtTran.Text);
                cmd.Parameters.AddWithValue("@hous", txtHous.Text);
                cmd.Parameters.AddWithValue("@lunc", txtLunc.Text);
                cmd.Parameters.AddWithValue("@uti", txtUti.Text);
                cmd.Parameters.AddWithValue("@housM", txtHousM.Text);
                cmd.Parameters.AddWithValue("@risk", txtRisk.Text);
                cmd.Parameters.AddWithValue("@veh", txtVeh.Text);
                cmd.Parameters.AddWithValue("@pfs", txtPfS.Text);
                cmd.Parameters.AddWithValue("@nhf", txtNHF.Text);
                cmd.Parameters.AddWithValue("@nhfs", txtNHFS.Text);
               cmd.Parameters.AddWithValue("@oded", txtOded.Text);
                cmd.Parameters.AddWithValue("@pens", txtPens.Text);
                cmd.Parameters.AddWithValue("@PAYE", txtPAYE.Text);
                cmd.Parameters.AddWithValue("@tded", txtTDed.Text);
                cmd.Parameters.AddWithValue("@driv", txtDriv.Text);
                cmd.Parameters.AddWithValue("@dome", txtDome.Text);
                cmd.Parameters.AddWithValue("@tpay", txtTPay.Text);
                cmd.Parameters.AddWithValue("@npay", txtNPay.Text);
                cmd.Parameters.AddWithValue("@housU", this.txtHousU.Text);
                cmd.Parameters.AddWithValue("@housM", this.txtHousM.Text);
                cmd.Parameters.AddWithValue("@staffM", this.txtStaffM.Text);
                cmd.Parameters.AddWithValue("@thrift", this.txtThrift.Text);
                cmd.ExecuteNonQuery();
                con.Close();

                MessageBox.Show("Record Inserted Successfully");
                ClearData();
            }
            else
            {
                MessageBox.Show("Please Provide Details!");
            }
            if (this.txteid.Text != "" && txtStaf.Text != "")
            //if (this.txtStatus.Text == "")
            {
                MessageBox.Show("Please provide Status");
                return;
            }

        }

        private void DisplayData()
        {
            
        }

       // Clear Data  
        private void ClearData()
        {
           txteid.Text = "FHA136";
            txtStaf.Text = "Akintomide Ayodele";
            txtAdrs.Text = "39 Durban Street, Wuse II, Abuja";
            txtCoy.Text = "FHA Homes Ltd";
            TxtAccno.Text = "11234567";
            txtemail.Text = "info@fhamortgage.gov.ng";
            txtBSal.Text = "0";
            txtDres.Text = "0";
            txtDUpfront.Text = "0";
            txtEduc.Text = "0";
            txtFurn.Text = "0";
            txtHarz.Text = "0";
            txtHous.Text = "0";
            txtHousM.Text = "0";
            txtHousU.Text = "0";
            txtLunc.Text = "0";
            txtNHF.Text = "0";
            txtNHFS.Text = "0";
            txtOded.Text = "0";
            txtOtherPay.Text = "0";
            txtPenNum.Text="pen1234";
            txtPAYE.Text = "0";
            txtPens.Text = "0";
            txtPfS.Text = "0";
            txtRisk.Text = "0";
            txtStaffM.Text = "0";
            txtThrift.Text = "0";
            txtTran.Text = "0";
            txtUti.Text = "0";
            txtVeh.Text = "0";
          //  picStafPix.Image ="" ;
            //picStafPix.imag= Image.FromFile(open.yinka);
            datAd.Text = DateTime.Now.Date.ToShortDateString();
            DatMod.Text = DateTime.Now.Date.ToShortDateString();
            datEmp.Text = DateTime.Now.Date.ToShortDateString();
            cmbBank.Text = "United Bank of Africa";
            cmbDpt.Text = "Operations";
            CmbGrd.Text = "Banking Officer";
            cmbLoc.Text = "Abuja";
            cmbMon.Text = DateTime.Now.Date.ToShortDateString();
            cmbPenAdmin.Text = "Crusader";
            cmbStatus.Text = "staff";
            cmbYr.Text = DateTime.Now.Date.ToShortDateString();
            txtTDed.Text = "0";
            txtDriv.Text = "0";
            txtDome.Text= "0";
            txtTPay.Text = "0";
            txtNPay.Text= "0";
            SN = 0;
         }


        //New Record  
        private void New_Rec()
        {
            
        }

        private void Add_Employee_Load(object sender, EventArgs e)
        {
            //FillBank();
            //FillStatus();
            //FillPenAdmin();
            //FillYear();
            //FillGrade();
            //FillDpt();
           
           
{

                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }

                string sqlLoc = "SELECT * FROM Location";
                var lAdapter = new SqlDataAdapter(sqlLoc, con);
                var lset = new DataSet();
                lAdapter.TableMappings.Add("Table", "Location");
                lAdapter.Fill(lset);
                this.dviewmanager = lset.DefaultViewManager;
       try
                {

                    for (int i = 0; i <= lset.Tables[0].Rows.Count - 1; i++)
                    {
                        this.cmbLoc.Items.Add(lset.Tables[0].Rows[i]["Location"].ToString());
                    }
                }
                catch (Exception exl)
                {

                }
                this.cmbMon.Items.Add("January");
                this.cmbMon.Items.Add("February");
                this.cmbMon.Items.Add("March");
                this.cmbMon.Items.Add("April");
                this.cmbMon.Items.Add("May");
                this.cmbMon.Items.Add("June");
                this.cmbMon.Items.Add("July");
                this.cmbMon.Items.Add("August");
                this.cmbMon.Items.Add("September");
                this.cmbMon.Items.Add("October");
                this.cmbMon.Items.Add("November");
                this.cmbMon.Items.Add("December");

            }
        }

        protected void FillBank()
        {

            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            string sqlStr = "SELECT * FROM BANK";
            var dAdapter = new SqlDataAdapter(sqlStr, con);
            var dset = new DataSet();
            dAdapter.TableMappings.Add("Table", "Bank");
            dAdapter.Fill(dset);
            this.dviewmanager = dset.DefaultViewManager;
            try
            {

                for (int i = 0; i <= dset.Tables[0].Rows.Count - 1; i++)
                {
                    this.cmbBank.Items.Add(dset.Tables[0].Rows[i]["Bank"].ToString());

                }


            }
            catch (Exception ex)
            {
                //Exception Message
            }
        }
        protected void FillDpt()
        {

            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            string sqlStr = "SELECT * FROM Dpt";
            var dAdapter = new SqlDataAdapter(sqlStr, con);
            var dset = new DataSet();
            dAdapter.TableMappings.Add("Table", "Dpt");
            dAdapter.Fill(dset);
            this.dviewmanager = dset.DefaultViewManager;
            try
            {

                for (int i = 0; i <= dset.Tables[0].Rows.Count - 1; i++)
                {
                    this.cmbDpt.Items.Add(dset.Tables[0].Rows[i]["Dpt"].ToString());

                }


            }
            catch (Exception ex)
            {
                //Exception Message
            }
        }
        protected void FillYear()
        {

            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            string sqlStr = "SELECT * FROM Year";
            var dAdapter = new SqlDataAdapter(sqlStr, con);
            var dset = new DataSet();
            dAdapter.TableMappings.Add("Table", "Year");
            dAdapter.Fill(dset);
            this.dviewmanager = dset.DefaultViewManager;
            try
            {

                for (int i = 0; i <= dset.Tables[0].Rows.Count - 1; i++)
                {
                    this.cmbYr.Items.Add(dset.Tables[0].Rows[i]["Year"].ToString());

                }


            }
            catch (Exception ex)
            {
                //Exception Message
            }
        }
        protected void FillGrade()
        {

            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            string sqlStr = "SELECT * FROM Grade";
            var dAdapter = new SqlDataAdapter(sqlStr, con);
            var dset = new DataSet();
            dAdapter.TableMappings.Add("Table", "Grade");
            dAdapter.Fill(dset);
            this.dviewmanager = dset.DefaultViewManager;
            try
            {

                for (int i = 0; i <= dset.Tables[0].Rows.Count - 1; i++)
                {
                    this.CmbGrd.Items.Add(dset.Tables[0].Rows[i]["Grade"].ToString());

                }


            }
            catch (Exception ex)
            {
                //Exception Message
            }
        }
        protected void FillStatus()
        {

            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            string sqlStr = "SELECT * FROM Status";
            var dAdapter = new SqlDataAdapter(sqlStr, con);
            var dset = new DataSet();
            dAdapter.TableMappings.Add("Table", "Status");
            dAdapter.Fill(dset);
            this.dviewmanager = dset.DefaultViewManager;
            try
            {

                for (int i = 0; i <= dset.Tables[0].Rows.Count - 1; i++)
                {
                    this.cmbStatus.Items.Add(dset.Tables[0].Rows[i]["Status"].ToString());


                }


            }
            catch (Exception ex)
            {
                //Exception Message
            }
        }
        protected void FillPenAdmin()
        {

            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            string sqlStr = "SELECT * FROM PenAdmin";
            var dAdapter = new SqlDataAdapter(sqlStr, con);
            var dset = new DataSet();
            dAdapter.TableMappings.Add("Table", "PenAdmin");
            dAdapter.Fill(dset);
            this.dviewmanager = dset.DefaultViewManager;
            try
            {

                for (int i = 0; i <= dset.Tables[0].Rows.Count - 1; i++)
                {
                    this.cmbPenAdmin.Items.Add(dset.Tables[0].Rows[i]["PenAdmin"].ToString());


                }


            }
            catch (Exception ex)
            {
                //Exception Message
            }
        }

        
        private void groupBox1_Enter(object sender, EventArgs e)
        {

        }

        private void label14_Click(object sender, EventArgs e)
        {

        }

        private void frmTpay_Enter(object sender, EventArgs e)
        {

        }

        private void txtHousU_TextChanged(object sender, EventArgs e)
        {

        }

        private void label33_Click(object sender, EventArgs e)
        {

        }

        private void txtStaffM_TextChanged(object sender, EventArgs e)
        {

        }

        private void txtPAYE_TextChanged(object sender, EventArgs e)
        {

        }

        private void datEmp_ValueChanged(object sender, EventArgs e)
        {
            //// Set title bar to selected date.
            //DateTime result = dateTimePicker1.Value;
            //this.Text = result.ToString();
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            this.Dispose();
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            




        }

        private void picStafPix_Click(object sender, EventArgs e)
        {
            //this.picStafPix.Image = Image.FromFile(@"Images\a.bmp");
        }

        private void button1_Click(object sender, EventArgs e)
        {
            // open file dialog   
            OpenFileDialog open = new OpenFileDialog();
            // image filters  
            open.Filter = "Image Files(*.jpg; *.jpeg; *.gif; *.bmp)|*.jpg; *.jpeg; *.gif; *.bmp";
            if (open.ShowDialog() == DialogResult.OK)
            {
                // display image in picture box  
                this.picStafPix.Image = new Bitmap(open.FileName);
                // image file path  
                textBox1.Text = open.FileName;

            }
        }
    }
}
Posted
Updated 4-Oct-17 20:47pm
Add a Solution

4 solutions

Use the Using Statement ... and inside of it open connection and close it so it not give you error your getting .. When you open connection without using or try catch it will stay open doesnt dispose properly that causes the problem i hope you try it ..
namespace FHAMortgageBank
{
    public partial class Add_Employee : Form
    {
      // can use connection from the web configuration which is far better than this ... 
      // can give the simple string 
        string constring = "path" or Webconfiguration add..
        SqlConnection con = new SqlConnection("Data Source=AKINTOMIDE-HP\\SQLEXPRESS;Initial Catalog=fham;Integrated Security=True");
        SqlCommand cmd;
        SqlDataAdapter adapt;
        private DataViewManager dviewmanager;
        //ID variable used in Updating and Deleting Record  
        int SN = 0;
 
        public Add_Employee()
        {
            InitializeComponent();
            ClearData();// Method
            FillBank();
            FillStatus();
            FillPenAdmin();
            FillYear();
            FillGrade();
            FillDpt();
        }
 
        private void frmMain_Enter(object sender, EventArgs e)
        {
 
        }
 
        private void BntSave_Click(object sender, EventArgs e)
        {
            using (sqlconnection con = new sqlconnection (Constring );
{
        // can also take the using for sqlcommand  inside of it..rest is same as you did
            if (this.txteid.Text != "" && txtStaf.Text != "")
            {
 
                con.Open();
                cmd = new SqlCommand("insert into master (eid,mon,yr,dat,staf,adrs,coy,loc,dpt,grd,datead,datemod,bsal,tran,hous,lunc,uti,furn,harz,educ,housM,dres,risk,veh,driv,dome,pfs,nhf,nhfs,nhis,oded,otherpay,housU,dressU,staffM,Thrift,accno,bank,email,penadmin,pennum,status,dupfront,picstaffpix) values(@eid,@mon,@yr,@dat,@staf,@adrs,@coy,@loc,@dpt,@grd,@datead,@datemod,@bsal,@tran,@hous,@lunc,@uti,@furn,@harz,@educ,@housM,@dres,@risk,@veh,@driv,@dome,@pfs,@nhf,@nhfs,@nhis,@oded,@otherpay,@housU,@dressU,@staffM,@Thrift,@accno,@bank,@email,@penadmin,@pennum,@status,@dupfront,@picstaffpix)", con);
                cmd.Parameters.AddWithValue("@eid", txteid.Text);
                cmd.Parameters.AddWithValue("@mon", cmbMon.Text);
                cmd.Parameters.AddWithValue("@yr", cmbYr.Text);
                cmd.Parameters.AddWithValue("@dat", SqlDbType.Date);
                cmd.Parameters.AddWithValue("@datead", SqlDbType.DateTime);
                cmd.Parameters.AddWithValue("@datemod", SqlDbType.DateTime);
                cmd.Parameters.AddWithValue("@staf", txtStaf.Text);
                cmd.Parameters.AddWithValue("@adrs", txtAdrs);
                cmd.Parameters.AddWithValue("@coy", txtCoy.Text);
                cmd.Parameters.AddWithValue("@dpt", cmbDpt.Text);
                cmd.Parameters.AddWithValue("@loc", cmbLoc.Text);
                cmd.Parameters.AddWithValue("@grd", CmbGrd.Text);
                cmd.Parameters.AddWithValue("@accno", TxtAccno.Text);
                cmd.Parameters.AddWithValue("@email", txtemail.Text);
                cmd.Parameters.AddWithValue("@picstaffpix", picStafPix);
                cmd.Parameters.AddWithValue("@bank", cmbBank.Text);
                cmd.Parameters.AddWithValue("@penadmin", cmbPenAdmin.Text);
                cmd.Parameters.AddWithValue("@status", cmbStatus.Text);
                cmd.Parameters.AddWithValue("@pennum", txtPenNum.Text);
                cmd.Parameters.AddWithValue("@basl", txtBSal.Text);
                cmd.Parameters.AddWithValue("@dres", txtDres.Text);
                cmd.Parameters.AddWithValue("@dupfront", txtDUpfront.Text);
                cmd.Parameters.AddWithValue("@educ", txtEduc.Text);
                cmd.Parameters.AddWithValue("@furn", txtFurn.Text);
                cmd.Parameters.AddWithValue("@harz", txtHarz.Text);
                cmd.Parameters.AddWithValue("@tran", txtTran.Text);
                cmd.Parameters.AddWithValue("@hous", txtHous.Text);
                cmd.Parameters.AddWithValue("@lunc", txtLunc.Text);
                cmd.Parameters.AddWithValue("@uti", txtUti.Text);
                cmd.Parameters.AddWithValue("@housM", txtHousM.Text);
                cmd.Parameters.AddWithValue("@risk", txtRisk.Text);
                cmd.Parameters.AddWithValue("@veh", txtVeh.Text);
                cmd.Parameters.AddWithValue("@pfs", txtPfS.Text);
                cmd.Parameters.AddWithValue("@nhf", txtNHF.Text);
                cmd.Parameters.AddWithValue("@nhfs", txtNHFS.Text);
               cmd.Parameters.AddWithValue("@oded", txtOded.Text);
                cmd.Parameters.AddWithValue("@pens", txtPens.Text);
                cmd.Parameters.AddWithValue("@PAYE", txtPAYE.Text);
                cmd.Parameters.AddWithValue("@tded", txtTDed.Text);
                cmd.Parameters.AddWithValue("@driv", txtDriv.Text);
                cmd.Parameters.AddWithValue("@dome", txtDome.Text);
                cmd.Parameters.AddWithValue("@tpay", txtTPay.Text);
                cmd.Parameters.AddWithValue("@npay", txtNPay.Text);
                cmd.Parameters.AddWithValue("@housU", this.txtHousU.Text);
                cmd.Parameters.AddWithValue("@housM", this.txtHousM.Text);
                cmd.Parameters.AddWithValue("@staffM", this.txtStaffM.Text);
                cmd.Parameters.AddWithValue("@thrift", this.txtThrift.Text);
                cmd.ExecuteNonQuery();
                con.Close();
}
 
                MessageBox.Show("Record Inserted Successfully");
                ClearData();
            }
            else
            {
                MessageBox.Show("Please Provide Details!");
            }
            if (this.txteid.Text != "" && txtStaf.Text != "")
            //if (this.txtStatus.Text == "")
            {
                MessageBox.Show("Please provide Status");
                return;
            }
 
        }

more over take this approach and your wont get error like it again ... it will work .
 
Share this answer
 
Posted
Comments
Aydotcom 5-Oct-17 14:39pm    
Thanks for the assistance, however, i still received an error "An unhandled exception of type 'System.ArgumentException' occurred in System.Data.dll

Additional information: No mapping exists from object type System.Windows.Forms.TextBox to a known managed provider native type"

Please help
Aydotcom 6-Oct-17 13:32pm    
Thanks for the assistance . After modifying with the following code, i receive the error " An unhandled exception of type 'System.ArgumentException' occurred in System.Data.dll

Additional information: Format of the initialization string does not conform to specification starting at index 0.". Please help

namespace FHAMortgageBank
{
public partial class Add_Employee : Form
{
// can use connection from the web configuration which is far better than this ...
// can give the simple string
//string constring = "path" or Webconfiguration add..;
string constring = "path";
SqlConnection con = new SqlConnection("Data Source=AKINTOMIDE-HP\\SQLEXPRESS;Initial Catalog=fham;Integrated Security=True");
SqlCommand cmd;
SqlDataAdapter adapt;
private DataViewManager dviewmanager;
//ID variable used in Updating and Deleting Record
int SN = 0;

public Add_Employee()
{
InitializeComponent();
ClearData();// Method
FillBank();
FillStatus();
FillPenAdmin();
FillYear();
FillGrade();
FillDpt();

//New_Rec();
//label1.Text = strTextBox;

//listPB = new List<picturebox>();
//chkBoxList = new List<checkbox>();
////Declare the connection string
//str = "Data Source=MYPC;Initial Catalog=myDB;Integrated Security=True";
////Initialize the list
//listPB.Add(pictureBox1);
//listPB.Add(pictureBox2);
//listPB.Add(pictureBox3);
//listPB.Add(pictureBox4);
//chkBoxList.Add(chkpicture1);
//chkBoxList.Add(chkpicture2);
//chkBoxList.Add(chkpicture3);
//chkBoxList.Add(chkpicture4);
// Get Database Connection
//_conn = new SqlConnection("data source=XEON;" +
// "initial catalog=Northwind;" +
// "user id=sa;password=manager;");
}

private void frmMain_Enter(object sender, EventArgs e)
{

}




//public frmMain()
//{
// InitializeComponent();
// DisplayData();
//}

private void BntSave_Click(object sender, EventArgs e)
{

using (SqlConnection con = new SqlConnection(constring)) ;
{
// can also take the using for sqlcommand inside of it..rest is same as you did
if (this.txteid.Text != "" && txtStaf.Text != "")
{

con.Open();

cmd = new SqlCommand("insert into master (eid,mon,yr,dat,staf,adrs,coy,loc,dpt,grd,datead,datemod,bsal,tran,hous,lunc,uti,furn,harz,educ,housM,dres,risk,veh,driv,dome,pfs,nhf,nhfs,nhis,oded,otherpay,housU,dressU,staffM,Thrift,accno,bank,email,penadmin,pennum,status,dupfront,picstaffpix) values(@eid,@mon,@yr,@dat,@staf,@adrs,@coy,@loc,@dpt,@grd,@datead,@datemod,@bsal,@tran,@hous,@lunc,@uti,@furn,@harz,@educ,@housM,@dres,@risk,@veh,@driv,@dome,@pfs,@nhf,@nhfs,@nhis,@oded,@otherpay,@housU,@dressU,@staffM,@Thrift,@accno,@bank,@email,@penadmin,@pennum,@status,@dupfront,@picstaffpix)", con);
cmd.Parameters.AddWithValue("@eid", txteid.Text);
cmd.Parameters.AddWithValue("@mon", cmbMon.Text);
cmd.Parameters.AddWithValue("@yr", cmbYr.Text);
cmd.Parameters.AddWithValue("@dat", SqlDbType.Date);
cmd.Parameters.AddWithValue("@datead", SqlDbType.DateTime);
cmd.Parameters.AddWithValue("@datemod", SqlDbType.DateTime);
cmd.Parameters.AddWithValue("@staf", txtStaf.Text);
cmd.Parameters.AddWithValue("@adrs", txtAdrs);
cmd.Parameters.AddWithValue("@coy", txtC
To solve this go step by step:

1. After you execute the sql commands close the sql connection in the next line, since you are using connections in other methods also.

This will solve your current issue "Connection is already open issue will be resolved when you use con.close();".

2. It is always better to use try catch and finally and the con.close() inside finally will be the better practise.

I am saying to put inside finally because if we place the close connection inside the try then during the exception situations the connection will not be closed and we have to place that inside the catch statement again. So better to have a finally and to close the sql connection inside finally

3. Move the connection string to Web.config and use configuration manager to get the connection string. --> This is just a best practise.


4. I would recommend you to use the Entity Framework if this is a new implementation because you would love writing code using LINQ that using the sql commands.


Try the above things and let me know if you need anything else than this !!!
 
Share this answer
 
Posted
Dont use the connection object globally, instead declare only the connection string or read it from the config file. This will avoid the connection state issues

private string conString = "Your Connection string";

private void Method1()
       {
           using (SqlConnection con = new SqlConnection(conString))
           {
               con.Open();
               // your code
               con.Close();
           }
       }
       private void Method2()
       {
           using (SqlConnection con = new SqlConnection(conString))
           {
               con.Open();
               // your code
               con.Close();
           }
       }

edit: updated based on Richard's [^] comment
 
Share this answer
 
Posted
Updated 10-Oct-17 17:21pm
v3
Comments
Richard Deeming 5-Oct-17 13:15pm    
You'll probably want to wrap those connections in using blocks as well. :)
Karthik_Mahalingam 10-Oct-17 23:21pm    
updated :)
The database operation code should be enclosed in try..catch block and you should close and dispose your connection in "finally" block. This ensures your connection is closed even if there is an exception in your code.

As an alternate you can use "using" block with connection.

Google will give you many examples, let me know if you don't find.
 
Share this answer
 
Posted
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month
Related Questions
How to Save upload file with the name of the ID in the database to have unique name
How to save text with quotes from textbox to access database
Why I can't save database backup on desktop?
How to save the dataset.acceptchanges( ) to database
Xamarin save data to database
Problem inserting into database
Problem in Saved and modification of database on the network
How do I save data that doesn't need to be saved to a DB, or would only have 1 row in the DB?
problem in uploading and saving in database image ?
I have a problem with dividing the value of 0 by 0 in an access database

Advertise
Privacy
Cookies
Terms of Use
Last Updated 10 Oct 2017
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web03 2.8:2024-07-22:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900