将语句插入SQL Server数据库

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

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

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

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

My INSERT statement looks like this:

我的INSERT语句如下所示：

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.

该声明没问题，因为当我在我的数据库中运行查询时，它会添加新行。

My C# code looks like this:

我的C＃代码如下所示：

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();

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时，没有新行。此外，当我尝试刷新此表数据时，visual studio（2012）给出了一个错误，如下所示：此数据库无法导入。它是不受支持的SQL Server版本或不受支持的数据库兼容性。

And my table Racun create query looks like this:

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

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?

我不知道出了什么问题。有人可以帮忙吗？

2 个解决方案

#1

There is three possible scenarios for an insert like that:

这样的插入有三种可能的场景：

The insert succeeds.
插入成功。
You get an exception.
你得到一个例外。
You have a trigger that replaces the insert with some other action.
您有一个触发器用一些其他操作替换插入。

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.

即使代码中存在错误，使用using块进行数据库连接也会正确关闭它。

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;

#2

I would try wrapping your code in a try block, and see if you can catch a SqlExecption:

我会尝试在try块中包装你的代码，看看你是否可以捕获一个SqlExecption：

try {
    SqlCommand cmd = new SqlCommand(stmt, cn);
    cmd.ExecuteNonQuery();
} catch (SqlException ex) {
    Console.WriteLine(ex.Message);
}

Edit: It looks like you're missing your parameters for your INSERT statement, and you should probably look at using a SqlTransaction:

编辑：看起来你错过了INSERT语句的参数，你应该看看使用SqlTransaction：

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);

// Adding parameters to the insert statement:
cmd.Parameters.AddWithValue("@Price", price);
cmd.Parameters.AddWithValue("@User", user);
cmd.Parameters.AddWithValue("@Time", time);
cmd.Parameters.AddWithValue("@Customer", customer);
cmd.Parameters.AddWithValue("@Discount", discount);
cmd.Parameters.AddWithValue("@FullPrice", fullprice);

// Start a transaction so we can roll back if there's an error:
SqlTransaction transaction = cn.BeginTransaction();
cmd.Transaction = transaction;

try {
    cmd.ExecuteNonQuery();
    transaction.Commit();
} catch (SqlException ex) {
    transaction.Rollback();
    Console.WriteLine(ex.Message);
} finally {
    cn.Close();
}

#1