INSERT语句到SQL Server数据库 [英] Insert statement into SQL Server database

问题描述

我试图找到过去几天这个bug，但没有成功。

I'm trying to find this bug for the last several days, but without any success.

我想在数据库中插入一个新行。一切顺利的话：没有错误，也没有程序崩溃。

I'm trying to insert one new row in a database. Everything goes well: there is no error, and no program crashes.

我的插入语句是这样的：

INSERT INTO Polozaj(Znesek, Uporabnik, Cas, Kupec, Popust, Poln_znesek)
VALUES(1,1,1,1,1,1)

这声明是确定的，因为当我跑在我的数据库查询它增加了新行。

That statement is ok, because when I run the query in my database it adds the new row.

我的C＃代码如下所示：

My C# code looks like this:

string connection = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=" + Application.StartupPath + "\\Trgovina.mdf;Integrated Security=True";
SqlConnection cn = new SqlConnection(connection);

string payment = ((Button)sender).Text, maxID = string.Empty;
double discount = Convert.ToDouble(discauntText.Text), totalPrice = Convert.ToDouble(fullPriceText.Text), fullPrice = Convert.ToDouble(discountPriceText.Text);
switch (payment)
{
    case "Dobavnica": discount = 10; break;
    case "Kartica": discount = 0; break;
    case "Gotovina": discount = 5; break;
}

cn.Open();

SqlCommand maxIdCmd = new SqlCommand("SELECT MAX(Id_racuna) FROM Racun", cn);
maxID = Convert.ToString(maxIdCmd.ExecuteScalar());
maxID = maxID != "" ? Convert.ToString(Convert.ToInt32(maxID) + 1) : "1";

string stmt = "INSERT INTO Racun(Znesek, Uporabnik, Cas, Kupec, Popust, Poln_znesek) " + 
              "VALUES(@Price, @User, @Time, @Customer, @Discount, @FullPrice)";

SqlCommand cmd = new SqlCommand(stmt, cn);
cmd.ExecuteNonQuery();

cn.Close();

正如我前面提到的，它看起来像程序运行此查询，一切都只是正常的，但是当我看看表中的 Racun ，没有新行。此外，当我试图刷新此表数据的Visual Studio（2012）给我，看起来像一个错误：这个数据库无法导入。它要么不支持SQL Server版本或不支持的数据库兼容。

As I already mentioned, it looks like the program runs this query and everything is just normal, but when I look in the table Racun, there is no new row. Furthermore, when I try to refresh this table data visual studio (2012) gives me an error that looks like: This database cannot be imported. It is either unsupported SQL Server version or an unsupported database compatibility.

和我的表 Racun 创建查询看起来是这样的：

And my table Racun create query looks like this:

CREATE TABLE [dbo].[Racun] (
   [Id_racuna]   INT             IDENTITY (1, 1) NOT NULL,
   [Znesek]      NUMERIC (10, 3) NULL,
   [Uporabnik]   NCHAR (20)      NULL,
   [Cas]         NCHAR (15)      NULL,
   [Kupec]       NCHAR (10)      NULL,
   [Popust]      NUMERIC (10, 3) NULL,
   [Poln_znesek] NUMERIC (10, 3) NULL,
   PRIMARY KEY CLUSTERED ([Id_racuna] ASC)
);

我不知道发生了什么事情错了。 ？谁能帮助

I don't know what's going wrong. Can anyone help?

推荐答案

有是那样的插入三种可能的情况：

There is three possible scenarios for an insert like that:

1. 插入成功。


2. 您得到一个异常。


3. 您有一个触发器，它取代了与其他一些动作插入。

我猜你没有触发，和你没有得到一个记录该表中，必须有一个例外。

I guess that you don't have a trigger, and as you don't get a record in the table, there has to be an exception.

你有没有在任何其他级别捕获异常的代码？这可以解释为什么你没有看到它，它也将离开数据库连接未关闭的，这可以解释为什么在连接到后来的数据库问题。

Do you have any code that catches the exception at any other level? That would explain why you don't see it, and it would also leave the database connection unclosed, which would explain why you have problems connecting to the database afterwards.

使用一个使用数据库连接块将正确地关闭它，即使没有在代码中的错误。

Using a using block for the database connection would close it properly even if there is an error in the code.

您正在使用参数化查询，但我不能看到你在代码中的参数添加到命令对象的任何地方。这将是这样的：

You are using a parameterised query, but I can't see that you add the parameters to the command object anywhere in the code. That would be something like:

cmd.Parameters.Add("Price", SqlDbType.Decimal).Value = price;
cmd.Parameters.Add("User", SqlDbType.NChar, 20).Value = user;
cmd.Parameters.Add("Time", SqlDbType.NChar, 15).Value = time;
cmd.Parameters.Add("Customer", SqlDbType.NChar, 10).Value = customer;
cmd.Parameters.Add("Discount", SqlDbType.Decimal).Value = discount;
cmd.Parameters.Add("FullPrice", SqlDbType.Decimal).Value = fullPrice;

这篇关于INSERT语句到SQL Server数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！