连接错误：连接未关闭。连接状态打开 [英] connection erron:connection was not closed.connection state open
问题描述
using System;
using System.Collections.Generic;
using System.Linq;
using System.Configuration;
using System.Data.SqlClient;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace projectDressDesign
{
public partial class registration : System.Web.UI.Page
{
SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnectionString"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
}
protected void registerBtn_Click1(object sender, EventArgs e)
{
Page.Validate();
if (Page.IsValid)
{
myConnection.Open();
string userInvalid = "The username entered is invalid, please choose another.";
string checkDatabase = "SELECT * FROM Users WHERE Email_Id = @em AND Password = @pas";
SqlCommand command = new SqlCommand(checkDatabase, myConnection);
command.Parameters.AddWithValue("@em", email.Text);
command.Parameters.AddWithValue("@pas", password.Text);
command.ExecuteNonQuery();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
outputlabel.Text = userInvalid;
myConnection.Close();
}
else
{
outputlabel.Text = "Succesfully Registered";
string query = "Insert into Users (FirstName,LastName,Gender,DateOfBirth,Email_Id,Password) Values (@fn,@ln,@gen,@dob,@em,@pas)";
myConnection.Open();
SqlCommand insertCommand = new SqlCommand(query, myConnection);
insertCommand.Parameters.AddWithValue("@fn", fname.Text);
insertCommand.Parameters.AddWithValue("@ln", lname.Text);
insertCommand.Parameters.AddWithValue("@gen", gender_dd.SelectedItem.Text);
insertCommand.Parameters.AddWithValue("@dob", date.Text);
insertCommand.Parameters.AddWithValue("@em", email.Text);
insertCommand.Parameters.AddWithValue("@pas", password.Text);
insertCommand.ExecuteNonQuery();
myConnection.Close();
Response.Redirect("login.aspx");
}
}
}
}
}
推荐答案
请更改代码;
您使用.. myconnection.open（）;
删除该行..
在条件循环之前......你打开..再一次打开其他条件......
打开之前..至少关闭连接..
Else
{
Connection.close（）;
Connection.open（）; < br $>
....
....
。
连接。关闭（）;
}
进入其他状态
.................................. .................................................. ........
之后
Reader.ExecuteReader（）;
Reader。阅读（）
如果（reader.hasrows）
{
Reader.close（）;
} < br $>
Else
{
Reader.close（）;
}
Pls change code ;
You used .. myconnection.open ();
Delete that line..
Before condition loop..you open.. once again it open in else condition...
Before open.. atleast close the connection..
Else
{
Connection.close ();
Connection.open ();
....
....
.
Connection.close ();
}
On entering into else condition
............................................................................................
After that
Reader.ExecuteReader ();
Reader.Read ()
If (reader.hasrows)
{
Reader.close ();
}
Else
{
Reader.close ();
}
我相信有一些你应该经历的代码问题。您已正确使用参数，但请查看以下几点。
I believe there are some problems with the code that you should go through. You have correctly used parameters, but have a look at following points.
string checkDatabase =SELECT * FROM Users WHERE Email_Id = @ em和密码= @pas;
SqlCommand命令=新的SqlCommand（checkDatabase，myConnection）;
command.Parameters.AddWithValue（@ em，email.Text） ;
command.Parameters.AddWithValue（@ pas，password.Text）;
string checkDatabase = "SELECT * FROM Users WHERE Email_Id = @em AND Password = @pas";
SqlCommand command = new SqlCommand(checkDatabase, myConnection);
command.Parameters.AddWithValue("@em", email.Text);
command.Parameters.AddWithValue("@pas", password.Text);
看起来你将密码存储为纯文本你的数据库。这是你永远不应该做的事情。密码应仅使用单向加密存储，以便无法显示原始密码。在验证用户时，您不需要知道密码，只需要知道它是否正确。请查看密码存储：如何操作。 [< a href =http://www.codeproject.com/Tips/186585/Password-Storage-How-to-do-ittarget =_ blanktitle =New Window> ^ ] br />
另一件事是如何在班级中存储连接。您应该使用连接变量，该变量的范围仅限于您需要它的方法。另外，要正确处理连接，您应该使用使用 [ ^ ]声明。而且由于您仍然使用数据库连接，为什么不在输入if之前打开连接。换句话说，类似于：
It looks like you're storing the password as plain text in your database. This is something you should never do. The passwords should be stored only using a one-way encryption so that the original password cannot be revealed. When verifying the user you don't need to know the password, you just need to know if it is correct. Have a look at Password Storage: How to do it.[^]
Another thing is how you store the connection in your class. You should use connection variable which would be scoped only to the method where you need it. Also to properly dispose the connection you should use a using[^] statement. And since you use the database connection anyway, why not open the connection before entering the if. In other words, something like:
protected void registerBtn_Click1(object sender, EventArgs e)
{
Page.Validate();
using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnectionString"].ConnectionString))
{
if (Page.IsValid)
{
...
}
else
{
...
}
}
}
有关详细讨论，请参阅版本2，关闭并处置数据库对象 [ ^ ]。此外，您还需要添加正确的错误处理...
For more discussion, see Version 2, close and dispose database objects[^]. Also you would need to add proper error handling...
string userInvalid =输入的用户名无效，请选择其他用户名。 ;
string checkDatabase =SELECT * FROM Users WHERE Email_Id = @em AND Password = @pas;
SqlCommand command = new SqlCommand（checkDatabase，myConnection）;
command.Parameters.AddWithValue（@ em，email.Text）;
command.Parameters.AddWithValue（@ pas， password.Text）;
command.ExecuteNonQuery（）;
SqlDataReader reader = command.ExecuteReader（）;
if（reader.HasRows）
{
outputlabel.Text = userInvalid;
myConnection.Close（）;
}
string userInvalid = "The username entered is invalid, please choose another.";
string checkDatabase = "SELECT * FROM Users WHERE Email_Id = @em AND Password = @pas";
SqlCommand command = new SqlCommand(checkDatabase, myConnection);
command.Parameters.AddWithValue("@em", email.Text);
command.Parameters.AddWithValue("@pas", password.Text);
command.ExecuteNonQuery();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
outputlabel.Text = userInvalid;
myConnection.Close();
}
第三件事是识别用户。如果我正确解释您的代码，如果密码不同，您允许人们使用相同的ID。在我看来，允许每个id只有一次是可行的。 id是静态的，而密码随时间而变化。例如，如果用户忘记了密码并希望重置密码，并且系统中有两个具有相同ID的用户，您将重置哪一个？
The third thing is identifying the user. If I interpret your code correctly, you allow people to use the same id if the password is different. In my opinion it would be feasible to allow each id only once. The id is static whereas the password changes over time. For example if the user forgets the passwords and wants to reset it and you have two users with the same id in the system, which one will you reset?
这篇关于连接错误：连接未关闭。连接状态打开的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
