ADO.NET编程说发生了错误-ExecuteNonQuery需要打开且可用的连接 [英] ADO.NET programming says error occured - ExecuteNonQuery requires an open and available connection

问题描述

现在，我的教授要求我使用ADO.NET进行案例研究，以将数据保存到SQL Server中。我已经在SQL Server中创建了数据库和表，并且正在尝试通过C＃ADO.NET在Visual Studio中创建一些表单。我是根据YouTube视频写的。但是我不知道为什么我无法将数据成功保存到数据库。

Right now, my professor requires me to implement a case study using ADO.NET to save data into SQL Server. I have already created a database and tables in SQL Server and I'm trying to create some forms in Visual Studio by C# ADO.NET. I write according to a YouTube video. But I don't know why I cannot save my data to database successfully.

我编写这样的代码的结果。

The result as I write my code like this.

任何帮助将不胜感激。

Any help would be appreciated.

namespace casestudy
{
    public partial class Form2 : Form
    {
        SqlConnection vcon2 = new SqlConnection(@"Data Source=SOPHIA-PC\SQLEXPRESS;Initial Catalog=casestudy;Integrated Security=True");

        public Form2()
        {
            InitializeComponent();
        }

        private void Form2_Load(object sender, EventArgs e)
        {
            try
            {
                vcon2.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show("error.occured" + ex.Message);
                this.Dispose();
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string vsql = string.Format("insert into Calluser values ({0}, '{1}', '{2}', {3})", Int32.Parse(txtUserID.Text), txtFName.Text, txtLName.Text, Int32.Parse(txtZoneID.Text));
            SqlCommand vCom = new SqlCommand(vsql, vcon2);
        try
        {
            vCom.ExecuteNonQuery();
            vCom.Dispose();
            MessageBox.Show("The User Information stored.");
            txtZoneID.Text = "";
            txtLName.Text = "";
            txtFName.Text = "";
            txtUserID.Text = "";
            txtUserID.Focus();
        }

        catch (Exception ex)
        {
            MessageBox.Show("error.occured" + ex.Message);
            this.Dispose();
        }
    }
}
}

推荐答案

在打开应用程序或表单时打开连接可能不是最佳方法。您想在执行sql之前立即打开连接并尽快将其关闭。

Opening the connection when the application or form opens is probably not the best approach. You want to open the connection right before you execute your sql and close it as soon as possible.

话虽如此，我建议从Form2_Load事件中删除代码。然后执行button1_Click或您从那里调用的其他方法中的所有操作。最好有一个类或组件来为您的应用程序进行数据访问。还要使用using语句，因为即使抛出异常也会确保资源被处置。

That being said, I recommend removing the code from the Form2_Load event. And do everything in the button1_Click or another method you call from there. Even better would be to have a class or component that does the data access for your application. Also use a using statement as it will ensure resources are disposed even if an exception is thrown.

    using (SqlConnection connection = new SqlConnection(@"Data Source=SOPHIA-PC\SQLEXPRESS;Initial Catalog=casestudy;Integrated Security=True");))
    {
        SqlCommand command = new SqlCommand(vsql, connection);
        command.Connection.Open();
        command.ExecuteNonQuery();
    }

using语句的某些信息：

Some info on the using statement:

http://www.dotnetperls.com/using

https://msdn.microsoft.com/ zh-cn / library / yh598w02.aspx

这篇关于ADO.NET编程说发生了错误-ExecuteNonQuery需要打开且可用的连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！