无法将记录插入数据库

I have the followind code in my .NET application

我的.NET应用程序中有followind代码

public string connectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Storage.mdf;Integrated Security=True;User Instance=True";
 string insertStatement = string.Empty;
            insertStatement = "INSERT INTO UserDetail(UserName, Password, IsRemember) Values('" + txtUserName.Text.Trim() + "','" + txtPassword.Text.Trim() + "','" + chkRemember.Checked + "')";
            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(insertStatement, con);
            con.Open();
            int result = cmd.ExecuteNonQuery();
            con.Close();

Storgae.mdf is the database which I have attached to the project. It's in the root directory.

Storgae.mdf是我附加到项目的数据库。它位于根目录中。

Table structure

UserName NVarChar(50) NULL,
Password NVarChar(50) NULL,
IsRememberBit NULL

When I am running the query, it is returning 1 [No error, No exception] But when I checked into the database table, I found it empty.

当我运行查询时,它返回1 [没有错误,没有异常]但是当我检查数据库表时,我发现它是空的。

Where I am wrong?

哪里错了?

Edit1

When I changed the insertStatement to

当我将insertStatement更改为

insertStatement = "INSERT INTO UserDetail(UserName, Password) Values('" + txtUserName.Text.Trim() + "','" + txtPassword.Text.Trim() + "')"

Than also nothing is updated into the database. and execute non query returned 1 to me.

也没有什么更新到数据库中。并执行非查询返回1给我。

On keeping debugger on the insertStatement, I am getting the below Statement

保持调试器在insertStatement上,我得到以下语句

INSERT INTO UserDetail(UserName, Password) Values('testName','TestPassword')

4 个解决方案

#1

I strongly recommend you use SqlParameters, and never user string concatenations. Besides security concerns, performance reasons, it actually helps you a lot since some values cannot be represented in string so easily (inserting varbinary wouldn't be so straightforward)

我强烈建议您使用SqlParameters,绝不使用用户字符串连接。除了安全问题,性能原因,它实际上对你有很大的帮助,因为有些值不能很容易地用字符串表示(插入varbinary不会那么简单)

var cmd = new SqlCommand("INSERT INTO UserDetail(UserName, Password, IsRemember) Values (@user, @pwd, @remember)", connection);
cmd.Parameters.AddWithValue("@user", username);
cmd.Parameters.AddWithValue("@pwd", pwd);
cmd.Parameters.AddWithValue("@remember", isremember);
cmd.ExecuteNonQuery();

#2

This section '" + chkRemember.Checked + "')

本节'“+ chkRemember.Checked +”')

should be:

" + chkRemember.Checked + ")

i.e remove the single quotes around the final bit / boolean parameter.

即删除最后一位/布尔参数周围的单引号。

#3

In respect to your Edit 1 you need to provide a default value for IsRememberBit in your database if you do not want to include it in your SQL statement.

对于Edit 1,如果您不想将其包含在SQL语句中,则需要在数据库中为IsRememberBit提供默认值。

#4

I don't see any problem with your code. Can you run SQL Profiler on the database, run the app and check what SQL queries are getting fired? Check to see if the Insert statement in your code is getting triggered. My assumption is the issue might possibly be with the Connection string or you checking the wrong database. No hard feelings but I am used to doing that occasionally :)

我没有看到您的代码有任何问题。您可以在数据库上运行SQL事件探查器,运行该应用程序并检查哪些SQL查询被触发?检查代码中的Insert语句是否被触发。我的假设是问题可能是连接字符串或您检查错误的数据库。没有难过的感觉,但我习惯偶尔这样做:)

#1

var cmd = new SqlCommand("INSERT INTO UserDetail(UserName, Password, IsRemember) Values (@user, @pwd, @remember)", connection);
cmd.Parameters.AddWithValue("@user", username);
cmd.Parameters.AddWithValue("@pwd", pwd);
cmd.Parameters.AddWithValue("@remember", isremember);
cmd.ExecuteNonQuery();

#2