Set id in sqlce table to auto grow?

Question

I am a beginner programmer (high school student), and my teacher gave me the task of making a primary key inside the sqlce table, automatically increasing each time I insert a new row into my database (I don’t think he knows how to do it :)

Here is my current code:

 using (SqlCeConnection con = new SqlCeConnection(conString)) { con.Open(); using (SqlCeCommand com = new SqlCeCommand("INSERT INTO Occupational VALUES(@employedBeforeBool, @nameofEmployer, @Addressemployed,@specificsAtEmployer,@employmentOnDischarge, @yearsExperienceWithEmployer, @desireToReturnToEmployer, @farmingExperienceBool, @tradesorapprentiship, @whattradesorapprentiship, @SoldierID, @Occupational_ID)", con)) { com.CommandText = "SET IDENTITY_INSERT Occupational ON"; com.Parameters.AddWithValue("@employedBeforeBool", Soldier1.employedBeforeBool); com.Parameters.AddWithValue("@nameofEmployer", Soldier1.nameOfEmployer); com.Parameters.AddWithValue("@Addressemployed", Soldier1.addressOfEmployer); com.Parameters.AddWithValue("@specificsAtEmployer", Soldier1.specificsAtEmployer); com.Parameters.AddWithValue("@employmentOnDischarge", Soldier1.employmentOnDischarge); com.Parameters.AddWithValue("@yearsExperienceWithEmployer", Soldier1.yearsExperienceWithEmployer); com.Parameters.AddWithValue("@desireToReturnToEmployer", Soldier1.desireToReturnToEmployer); com.Parameters.AddWithValue("@farmingExperienceBool", Soldier1.farmingExperienceBool); com.Parameters.AddWithValue("@tradesorapprentiship", Soldier1.tradeOrApprenticeship); com.Parameters.AddWithValue("@whattradesorapprentiship", Soldier1.whattradesorapprentiship); com.Parameters.AddWithValue("@SoldierID", SoldierID); //com.Parameters.AddWithValue("@Occupational_ID", counter); com.ExecuteNonQuery(); } 

My program does not crash when I run it, and it looks like it inserts, but nothing is displayed inside the table.

Answer 1

Your current code overwrites the INSERT INTO query with the text SET IDENTITY . There is only one CommandText property.

And the creation of an automatic column increment is performed when creating the table, you do not need to do anything during insertion.

If you used the constructor to create the table, go back and set the IDENTITY property to true for the Key column.

Answer 2

As Henk said, you cannot have two different CommandTexts for a given SQLCECommand. While you define the field / column as Identity when building the table, you need to make sure that you display any link to your identifier column (your @Occupational_ID) inside the command itself.

However, there are two very important facts that were not previously indicated:

• You need two separate SQLCECommands (one for enabling Identity and one for the actual Insert). Add the following to your using statement:

  SqlCeCommand identChangeCommand = new SqlCeCommand (con); identChangeCommand.CommandText = "SET IDENTITY_INSERT Professional Software";

• You need to add the initial value of your identity column inside the Insert. Since you use an integer as a data type, you can choose any integer value to start with. Each insertion after that will add one to the previous value. Inserted:

  using (SqlCeCommand com = new SqlCeCommand ("INSERT LABOR VALUES (1000, @employedBeforeBool, ...

PS make sure that you execute ExecuteNonQuery () both commands. Hope this helps!