我存到数据库时遇到问题 [英] I have a problem saving into a database
本文介绍了我存到数据库时遇到问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在开发一个将master和addEmployee分别作为表和表单的解决方案.该解决方案将使用addEmployee表单将数据保存到master中.该解决方案使用C#作为前端，使用sql2008数据库作为后端.编译解决方案后，后端没有任何数据，并且我收到错误消息"
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;
}
}
}
}
推荐答案
不要全局使用连接对象，而只声明连接字符串或从配置文件中读取它.这样可以避免连接状态问题
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();
}
}
根据 Richard的 [
edit: updated based on Richard''s [^] comment
The数据库操作代码应该包含在try..catch块中，并且您应该在"finally"块中关闭并处置您的连接.即使代码中有异常，这也可以确保关闭连接.
另外，您也可以在连接中使用"using"块.
Google会为您提供许多示例，如果您找不到它，请告诉我.
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.
要逐步解决此问题，请执行以下操作:
1.执行sql命令后，请关闭下一行的sql连接，因为您还在其他方法中使用连接.
这将解决您当前的问题使用con.close();将解决连接已经打开的问题.".
2.总是最好使用try catch和finally，final里面的con.close()会更好.
我说的是最后放进去，因为如果我们将关闭连接放在try内，那么在异常情况下，该连接将不会关闭，我们必须再次将其放在catch语句内.所以最好有一个final并在final内部关闭sql连接
3.将连接字符串移至Web.config，然后使用配置管理器获取连接字符串. ->这只是最佳实践.
4.如果这是一个新的实现，我建议您使用Entity Framework，因为您希望使用LINQ和sql命令来编写代码.
尝试上述操作，让我知道您是否需要除此以外的其他物品！！！！！
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 !!!
这篇关于我存到数据库时遇到问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
