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.

CodePudding user response：

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