Increment by 1 ASP.NET

Question

I have a little problem in ASP.NET. I'm trying to get the MAX number in a column and then increment it by 1. Like this in example:

SqlConnection con= new SqlConnection(ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString);
con.Open();

string db = "instert into FJ(C1, C2, C3) values (@c1 , @c2 ,@c3)";
SqlCommand com = new SqlCommand(db, con);
com.Parameters.AddWithValue("@c1", TextBox1.Text);
com.Parameters.AddWithValue("@c2", com .CommandText = "declare @a float set @a = (select MAX(C2) from FJ)+1 INSERT INTO [FJ]([C2]) VALUES (@a)");
com.Parameters.AddWithValue("@c3", TextBox3.Text);

com.ExecuteNonQuery();
con.Close();

The thing that column C2 is incremented by 1 but other columns are null. If I comment C2 line TextBox1 and 3 are added to the database. What is the problem?

P.S. I'm not allowed to edit the database. I need to make it in ASP.NET code.

Because I'm not allowed to edit the database. I need to make it in ASP.NET code. That's what my supervisor told me. :/ — aldoblack, Mar 01 '14 at 14:50
The problem with that is that with concurrency (multiple concurrent uses), this can _easily_ fail if not managed by the DB. Right tool for the right job - if you are not allowed to change the DB, work with someone who _can_. — Oded, Mar 01 '14 at 14:52
But is there any way how to increment by one without making other columns to be NULL? — aldoblack, Mar 01 '14 at 14:54
In the table design - making the column an auto-incrementing IDENTITY column. — Oded, Mar 01 '14 at 14:55

score 2 · Accepted Answer · edited May 23 '17 at 12:03

the C2 increment can be set in the Database side. no need to these calculations.

How can I make a primary key as AUTOINCREMENT

Update : then you are doing it wrong . you are changing the command in :

com.Parameters.AddWithValue("@c2", com .CommandText = "declare @a float set @a = (select MAX(C2) from FJ)+1 INSERT INTO [FJ]([C2]) VALUES (@a)");

that's why other columns are null.

it should be :

SqlConnection con= new SqlConnection(ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString);
con.Open();

string db = "declare @a float; set @a = (select MAX(C2) from FJ)+1;instert into FJ(C1, C2, C3) values (@c1 , @a ,@c3)";
SqlCommand com = new SqlCommand(db, con);
com.Parameters.AddWithValue("@c1", TextBox1.Text);
com.Parameters.AddWithValue("@c3", TextBox3.Text);

com.ExecuteNonQuery();
con.Close();

I know primary key but I'm not allowed to edit the database. I need to make it in ASP.NET code. That's what my supervisor told me. :/ — aldoblack, Mar 01 '14 at 14:50
It doesn't work. Now the C2 column is NULL and TextBox 1 and 3 are added to the database. — aldoblack, Mar 01 '14 at 15:01

score 0 · Answer 2 · answered Mar 01 '14 at 16:02

Well, I have experienced a similar situation.

Using an identity is always a good solution, but, when an INSERT operation fails, it looses the new ID generated!

Looking into your T-SQL, I see a problem: What will happen when two operations starts on the "same" time, i.e., when the last value for the C2 column be, let's say 10? There will be either two rows with the 11 value or if the column has a unique index the INSERT that started first will have the new value and the second will fail... It would cause several damages, I think.

What I suggest is to change the INSERT statement to be this way:

INSERT INTO FJ( C1, C2, C3 )
SELECT @c1, ISNULL( MAX( C2 ) + 1, 1 ), @c3
  FROM FJ;

This way will be safer.

See ya!

Increment by 1 ASP.NET

2 Answers2