Insert statement into SQL Server database

Question

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.

My INSERT statement looks like this:

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:

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.

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?

Answer 1

I would try wrapping your code in a try block, and see if you can catch a 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:

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

Answer 2

There is three possible scenarios for an insert like that:

1. The insert succeeds.
2. You get an exception.
3. 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.

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;