-3

I know similar questions have been asked but here me out.

As stated in the title, an exception is thrown when trying to add data to a table that has an identity column as its primary key. Identity is ticked for the table and it auto-increments. The code:

public static void AddBooking(DateTime StartDate, DateTime EndDate, int PCost, int NumofCustomers, int Total, String Username, int CostID)
{
    using (var cn = new SqlCeConnection(TheDataConnection))             
    {
        using (var cmd = new SqlCeDataAdapter())
        {
            cn.Open();

            SqlCeCommand comm = new SqlCeCommand();
            comm.Connection = cn;

            comm.CommandText = "INSERT INTO tblCost (StartDay, EndDay, PCost, NumOfCustomers, Total) VALUES (null, @StartDate, @EndDate, @PCost, @NumOfCustomers, @Total)";

            comm.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = StartDate;
            comm.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = EndDate;
            comm.Parameters.Add("@PCost", SqlDbType.Int).Value = PCost;
            comm.Parameters.Add("@NumOfCustomers", SqlDbType.Int).Value = NumofCustomers;
            comm.Parameters.Add("@Total", SqlDbType.Int).Value = Total;

            SqlCeCommand comm2 = new SqlCeCommand();
            comm2.Connection = cn;
            comm2.CommandText = "INSERT INTO tblBookings (Username, CostID) VALUES (@Username, @CostID)";

            comm2.Parameters.Add("@Username", SqlDbType.NVarChar).Value = Username;
            comm2.Parameters.Add("@CostID", SqlDbType.BigInt).Value = CostID;
            
            comm.ExecuteNonQuery();
            comm2.ExecuteNonQuery();

            cn.Close();
        }
    }
}

The exception:

System.Data.SqlServerCe.SqlCeException: The count of column names and source expressions do not match. [ Column name count = 5,Source expression count = 6 ]

I have tried, adding 'Default' to the part of the query where the column would be, but then an exception stating the column cannot be edited is thrown.

Any help with this will be appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
LusunguPryme
  • 3
  • 1
  • 2
  • 2
    In this line `comm.CommandText = "INSERT INTO tblCost(StartDay, EndDay, PCost, NumOfCustomers, Total) VALUES (null, @StartDate, @EndDate, @PCost, @NumOfCustomers, @Total)";` can you remove `null` from the start of `VALUES`? – Simeon Oct 15 '22 at 17:35
  • Thanks @Simeon, the query is working now after remove the 'null' from the start of VALUES. Not exactly sure how tho, as I added the null there when try to fix the exception in the first place. – LusunguPryme Oct 15 '22 at 18:31
  • I'll add an answer so you can close the question – Simeon Oct 15 '22 at 18:42
  • Side note: SQL Server CE was end-of-life'd over a year ago, I strongly suggest you upgrade (probably to SQL Server Express or LocalDB) – Charlieface Oct 15 '22 at 20:25

1 Answers1

0

On this line comm.CommandText = "INSERT INTO tblCost (StartDay, EndDay, PCost, NumOfCustomers, Total) VALUES (null, @StartDate, @EndDate, @PCost, @NumOfCustomers, @Total)";

The specify 5 columns to insert into tblCost but list 6 values to insert (with null being the first)

that would insert null into StartDay column, @StartDate into EndDay column, and so on

Removing null from the start of your values statement properly aligns these values to their columns

Simeon
  • 797
  • 5
  • 14