Trouble adding things to SQL Server database c#

Question

In my project, the user can book an holiday and when the manager logs into their account, the manager can review the holidays booked by the users.

When the user books their holiday, the holiday information goes to the database I have created. In the database, I have set the primary key to holiday Number instead of the user Id because the user may add 1 or more holidays to the database. Trouble is, the holiday Number is always 0.

Here's my database:

Database Image

As you can see on the database the holiday number is 0 and when a user books a holiday it will not add to the database because the holiday number is 0.

How would I add to the holiday Number every time a user books an holiday?

e.g.

50  Richard 27/02/15    29/02/15    Not reviewed    0
50  Richard 27/02/15    29/02/15    Not reviewed    1
50  Richard 27/02/15    29/02/15    Not reviewed    2
50  Richard 27/02/15    29/02/15    Not reviewed    3

My code:

string connectionString = con;

using (SqlConnection connection = new SqlConnection(connectionString))
{
    int i = 0;
    string setAuthorised = "Not reviewed";

    SqlCommand cmd = new SqlCommand("INSERT INTO Holidays (Id, Firstname, Datefrom, Dateto, Authorised, HolidayNo) VALUES (@Id, @Firstname, @Datefrom, @Dateto, @Authorised, @HolidayNo)");

    cmd.CommandType = CommandType.Text;
    cmd.Connection = connection;

    cmd.Parameters.AddWithValue("@Id", idTb.Text);
    cmd.Parameters.AddWithValue("@Firstname", firstNameTb.Text);
    cmd.Parameters.AddWithValue("@Datefrom", dateFromTb.Text);
    cmd.Parameters.AddWithValue("@Dateto", dateToTb.Text);
    cmd.Parameters.AddWithValue("@Authorised", setAuthorised);
    cmd.Parameters.AddWithValue("@HolidayNo", i);

Two options, a) make Holiday Number an auto increment column or b) show us some code where you are creating the data, there must be an error. — ViRuSTriNiTy
– ViRuSTriNiTy, Commented Dec 29, 2015 at 8:44
You should check out Can we stop using AddWithValue() already? and stop using .AddWithValue() - it can lead to unexpected and surprising results... — marc_s
– marc_s, Commented Dec 29, 2015 at 10:03

enthusiast · Accepted Answer · 2015-12-29 09:00:21Z

2

i am assuming you are on sql server. make your holiday id column an identity column. for example -

CREATE TABLE YourHolidayTable
(HolidayNumber INT PRIMARY KEY IDENTITY(0,1),
--other columns 
)

this would be the easiest solution .. In your code you are always inserting 0 to holidayNumber. in db identity insert must be set to true.just comment out the holidaynumber part in your c# code

Use this code after you change your col to identity-

         string connectionString = con;
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            int i = 0;
            string setAuthorised = "Not reviewed";
            SqlCommand cmd = new SqlCommand("INSERT INTO Holidays (Id, Firstname, Datefrom, Dateto, Authorised) VALUES (@Id, @Firstname, @Datefrom, @Dateto, @Authorised)");
            cmd.CommandType = CommandType.Text;
            cmd.Connection = connection;
            cmd.Parameters.AddWithValue("@Id", idTb.Text);
            cmd.Parameters.AddWithValue("@Firstname", firstNameTb.Text);
            cmd.Parameters.AddWithValue("@Datefrom", dateFromTb.Text);
            cmd.Parameters.AddWithValue("@Dateto", dateToTb.Text);
            cmd.Parameters.AddWithValue("@Authorised", setAuthorised);

edited Dec 29, 2015 at 9:00

answered Dec 29, 2015 at 8:52

enthusiast

9997 silver badges11 bronze badges

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

2 Comments

Richard Jacobs Over a year ago

You sir are a genius! Thanks!!

enthusiast Over a year ago

would you mind to mark this as answer if this fixes your problem :) i need that as i am also new to stackoverflow , thanks ..

Mitchel Sellers · Accepted Answer · 2015-12-29 08:46:04Z

2

When you create your database table you will want to mark that column as an Identity Column. Quick sample below

CREATE TABLE dbo.YourTable(
   HolidayNumber INT NOT NULL IDENTITY(1,1),
   --Other Columns here
)

By adding the IDENTITY(1,1) portion it tells SQL Server to increment by 1, starting at one for each record

answered Dec 29, 2015 at 8:46

Mitchel Sellers

63.3k15 gold badges115 silver badges174 bronze badges

5 Comments

Richard Jacobs Over a year ago

Hi thanks for your reply, could you take a look at my code above because when I added the IDENTITY(1,1) to my database, nothing adds to the database when the user books the holiday anymore. Thanks!

Mitchel Sellers Over a year ago

When using an identity value, you do NOT insert the column. So just remove the HolidayNo column from your insert statement

Richard Jacobs Over a year ago

Thanks for the reply, nope still nothing.

Mitchel Sellers Over a year ago

Can you try a manual insertion via SQL. That code should be fine

Richard Jacobs Over a year ago

Hi, I've sorted it. It was: IDENTITY(0,1) which was the fix, thanks!

Collectives™ on Stack Overflow

Trouble adding things to SQL Server database c#

2 Answers 2

2 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related