Increment value after 1000 without auto increment SQL

Question

I want to increment User_ID with respect to 1000. When a user enters the system, it will count it. But I do not want to use identity increment because I already made ID identity increment. I do this just to be able to change it in backup at any time. I create first user in SQL Server. Id is 1000.

Here is my code:

string query1 = "ALTER TABLE UserInfo WHERE User_ID ORDER BY User_ID ASC SET IDENTITY_CACHE=ON";

int i;           

cmd = new SqlCommand(query1);
conn.Open();

// I get an error here: "Connection property has not been initialized."
i = Convert.ToInt32(cmd.ExecuteScalar()) + 1; 
conn.Close(); 

// "ALTER TABLE UserInfo WHERE User_ID ORDER BY User_ID ASC
string query = "INSERT INTO UserInfo(User_ID, Name, Surname, Birth_Date, Reg_Date) VALUES (@User_ID, @Name, @Surname, @Birth_Date, @Reg_Date)";
cmd = new SqlCommand(query, conn);

cmd.Parameters.AddWithValue("@User_ID", i);
cmd.Parameters.AddWithValue("@Name", TextBox1.Text);
cmd.Parameters.AddWithValue("@Surname", TextBox2.Text);
cmd.Parameters.AddWithValue("@Birth_Date", TextBox3.Text);
cmd.Parameters.AddWithValue("@Reg_Date", DateTime.Now.ToString());

conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

the edited part is:

string query1 = "SELECT User_ID FROM UserInfo ORDER BY User_ID DESC"; //User_ID is increment one more with respect to 1000

            cmd = new SqlCommand(query1, conn);

        
            conn.Open();
        int i = Convert.ToInt32(cmd.ExecuteScalar());
        i = i + 1;
            conn.Close();

IT WORKS

You have to set `cmd.Connection = conn;`, your `ALTER TABLE` statement looks very wrong, and to be honest I have not understood what your question is. — KekuSemau, Jul 02 '21 at 06:22
@KekuSemau or pass it in the constructor and write fewer lines of code — Cleptus, Jul 02 '21 at 06:23
@Cleptus oh yes, and ironically the OP already does that with the second command. — KekuSemau, Jul 02 '21 at 06:31
@KekuSemau added your ALTER TABLE comment into my answer with other advices — Cleptus, Jul 02 '21 at 06:40
Even if your code did retrieve the latest id correctly, two requests could pull the same id at the same time, and then try and insert two users with the same id. — ProgrammingLlama, Jul 02 '21 at 06:44
@Llama Indeed, it would be much better using a Sequence to avoid that problem, but a unique key in the User_ID column could prevent poor data into the table. — Cleptus, Jul 02 '21 at 06:53
But how can i increment it with respect to 1000 with using ExecuteScalar()? I edited my code btw — cervidaesj, Jul 02 '21 at 07:15
Does this answer your question? [Setting Identity to on or off in SQL server](https://stackoverflow.com/questions/15065253/setting-identity-to-on-or-off-in-sql-server) — Cleptus, Jul 02 '21 at 07:20
No, I have already an identity value which is ID. ID and User_ID are different. ID is identity value but User_ID should increase by one after 1000 thanks to my for loop. — cervidaesj, Jul 02 '21 at 07:32
Bc I used in ID, also I want to act on it later in any urgency situation — cervidaesj, Jul 02 '21 at 08:39
I'm not sure what you thought that `ALTER TABLE` was supposed to do, but the syntax is not correct, and `IDENTITY_CACHE` seems to be irrelevant — Charlieface, Jul 02 '21 at 10:36
_I want to act on it later_ It seems you don't really have any particular reason for your approach and you don't really understand the purpose and standard usage of a synthetic key. If you intend to write code the create and manage this value, you will need more advanced skills for doing that safely. As a beginner, let the database engine do that with an identity column or a sequence. — SMor, Jul 02 '21 at 11:57
And stop using [addwithvalue](http://www.dbdelta.com/addwithvalue-is-evil/). — SMor, Jul 02 '21 at 11:58
Thanks for your recommendation but I guess I couldn't explain what I meant. Also you are really rude. I changed whole sql sytnax and add i=i+1 and that was what I want.. I found it thanks everyone! All you can see the solution at the bottom :) — cervidaesj, Jul 02 '21 at 12:32

Cleptus · Accepted Answer · 2021-07-02T06:37:38.027

1

Your error description does not match your question title or description. You should fix that.

Cause of the problem: That being said, the SqlCommand does require a connection and you never assign the connection to the command.

Proposed solution: The easiest way would be using the constructor that requires also the connection SqlCommand(cmdText, connection) documentation.

cmd = new SqlCommand(query1, conn);

Not related to your problem but, it will likely bit your ass sonner or later, a couple things you should know/consider:

The ALTER TABLE statement does not allow the WHERE clause, check the TSQL ALTER TABLE documentation. This has already been mentioned in the question comments by @KekuSemau.
SqlConnection does implement IDisposable and your code does not call Dispose() nor use the using statement. I would advise using the using statement.

edited Jul 02 '21 at 06:37

answered Jul 02 '21 at 06:21

Cleptus

3,446
4
28
34

I add it but again I get an error in same location like "System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'CHANGE'.", Also I changed the sytnax like: "ALTER TABLE UserInfo CHANGE User_ID ORDER BY User_ID ASC SET IDENTITY_CACHE=ON"; – cervidaesj Jul 02 '21 at 07:03
I said in the question your `ALTER TABLE` statement was wrong, and linked you the documentation. Should be similar to `ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF`. It is not a table level configuration but a database level one. – Cleptus Jul 02 '21 at 07:14
I believe you are using the wrong command, you are using a database configuration and that will affect all other tables. I have linked a related question with exactly what you want to get in the question comments. The answer I suggest is `SET IDENTITY_INSERT table_name ON|OFF` – Cleptus Jul 02 '21 at 07:32
In the beginning of the code, i inserted SqlCommand cmd; therefore it is okay. If I delete User_ID in "query" it works. – cervidaesj Jul 02 '21 at 07:34

Increment value after 1000 without auto increment SQL

1 Answers1