How to insert data into table in linq to sql

Question

i have a table TransactionUpdate with columns TransactionUpdateId, OrderNumber,BalanceAmount,TotalOrderAmount,LinkToCustomerMasterId,LinkToLocationId,LastUpdateDate

i want to insert data into TransactionUpdate

i have tried this. but only once data is being inserted. after that data is not inserted in table

TransactionUpdate objTransaction = new TransactionUpdate();
     objTransaction.OrderNumber = objBooking.OrderNumber;
                            objTransaction.BalanceAmount = 0;
                            objTransaction.OrderTotalAmt = objBooking.TotalOrderAmt;
                            objTransaction.LinkToCustomerMasterId = objBooking.LinkCustomerMasterId;
                            objTransaction.LinkToLocationId = objBooking.LinktoLocationMasterId;
                            objTransaction.OrderType = "Send booking";
                            objTransaction.LastUpdaeDate = DateTime.Now;

                            int AddCOD = clsFranchies.AddCODCustomerOrderTransaction(objTransaction); 

method:AddCODCustomerOrderTransaction

public static int AddCODCustomerOrderTransaction(TransactionUpdate transaction)
        {
            try
            {
                using (WMSDataContext dc = new WMSDataContext())
                {
                    TransactionUpdate objtransactionAddCOD = new TransactionUpdate();
                    objtransactionAddCOD = transaction;
                    dc.TransactionUpdates.InsertOnSubmit(objtransactionAddCOD);
                    dc.SubmitChanges();
                    return 1;
                }
            }
            catch (Exception ex)
            {
                clsError.WriteError(ex);
                return -1;
            }
        }

I am using Linq to sql, clsFranchise is class file.

Answer 1

You have not set TransactionUpdateId when you are inserting a record.

So by default (if it is a integer) then it is taking TransactionUpdateId = 0. So here when you are adding 1st entry into table, record gets added with primary key 0. But when your are trying 2 time, primary key isn't allowing you to enter 2nd record with duplicate primary key value 0.

You can resolve this issue by 2 ways:
1) Set primary key to auto increment.
2) While inserting record set primary key value to new value. Like newValue = Max(TransactionUpdateId ) + 1;

For 2nd way: modify below function:

public static int AddCODCustomerOrderTransaction(TransactionUpdate transaction)
        {
            try
            {
                using (WMSDataContext dc = new WMSDataContext())
                {
                    TransactionUpdate objtransactionAddCOD = new TransactionUpdate();
                    objtransactionAddCOD = transaction;

            var newTransactionUpdateID = dc.TransactionUpdates.Max(s = >s.TransactionUpdateId);

            objtransactionAddCOD.TransactionUpdateId = newTransactionUpdateID == null ? 1: (newTransactionUpdateID + 1);
                    dc.TransactionUpdates.InsertOnSubmit(objtransactionAddCOD);
                    dc.SubmitChanges();
                    return 1;
                }
            }
            catch (Exception ex)
            {
                clsError.WriteError(ex);
                return -1;
            }
        }

Please let me know is this works for you ?