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?

• Ryan Gates: What version of sql server are you using?
• t3hn00b: @RyanGates looking at his connection string - MS SQL 2012 (v11)
• t3hn00b: Where do you add the parameters to the INSERT command? Something in the line of cmd.Parameter.Add("Price", SqlDbType.Int).Value = 1;
• Mark Rotteveel: You aren't setting the parameters for your insert, is that intentional?
• Clem: it's all the same when I use cmd.Parameter.Add. I already tryed this.

Answer 1

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;

• Clem: That makes no differences.. Can you explain a little bit more of those exceptions?

Answer 2

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

• Clem: And now: ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.
• Jared Harley: @Klemzy Sorry about that, that was a typo on my part. You'll want to add the cmd.Transaction = transaction; line right after you create the transaction object. There's more about the SqlTransaction object here: msdn.microsoft.com/en-us/library/…
• Clem: No problems, thanks for help, but still..I added this line right there but still..no use. It still doesnt work.