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?

@RyanGates looking at his connection string - MS SQL 2012 (v11) — t3hn00b
– t3hn00b, Commented Feb 14, 2013 at 15:12
Where do you add the parameters to the INSERT command? Something in the line of cmd.Parameter.Add("Price", SqlDbType.Int).Value = 1; — t3hn00b
– t3hn00b, Commented Feb 14, 2013 at 15:21
You aren't setting the parameters for your insert, is that intentional? — Mark Rotteveel
– Mark Rotteveel, Commented Feb 14, 2013 at 15:22
it's all the same when I use cmd.Parameter.Add. I already tryed this. — Clem
– Clem, Commented Feb 14, 2013 at 15:28

Guffa · Accepted Answer · 2013-02-14 15:26:07Z

4

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.

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;

edited Feb 14, 2013 at 15:26

answered Feb 14, 2013 at 15:19

Guffa

703k111 gold badges760 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Clem Over a year ago

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

Jared Harley · Accepted Answer · 2013-02-14 15:41:00Z

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

edited Feb 14, 2013 at 15:41

answered Feb 14, 2013 at 15:18

Jared Harley

8,3474 gold badges41 silver badges48 bronze badges

3 Comments

Clem Over a year ago

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 Over a year ago

@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 Over a year ago

No problems, thanks for help, but still..I added this line right there but still..no use. It still doesnt work.

Collectives™ on Stack Overflow

Insert statement into SQL Server database

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related