У меня есть проблема с сохранением в базе данных
Я разрабатываю решение, которое имеет master и addEmployee в виде таблицы и формы соответственно. Решение сохранит данные в master с помощью формы addEmployee. Решение использует C# и базы данных sql2008 в качестве бэк-конец. После компиляции решения бэк-энд не имеет никаких данных, и я получаю ошибку "
Additional information: The connection was not closed. The connection's current state is open.- Пожалуйста, кто-нибудь может мне помочь?
Что я уже пробовал:
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;
}
}
}
}