CODEWITHSUNDEEP
sql-serversql-server-2008
NoviceToDotNet
NoviceToDotNet

Reputation: 10815

Insert DBNUll.value to database for a int field?

I am unable to insert null in my database for the OrderMealDealItemID when the OrderMealdealItemID is null in the database for the below query. 

  string insertStatementDressing = null;
  insertStatementDressing = @"Insert into OrderDressing(SrNo,DressingID,OrderItemID,OrderID,ProductID,OrderMealDealItemID,IsDeleted,IsApplied,Amount,IsPriceApplied) 
             values('" + odiDress.SrNo + "'," + odiDress.DressingID + "," + odiDress.OrderItemID + "," +
              latestOrderID_onLocal + "," + odiD.ProductID + "," +
              odiDress.OrderMealDealItemID??DBNull.Value
                                      + "," + odiDress.IsDeleted + "," + odiDress.IsApplied + "," + odiDress.Amount + "," + odiDress.IsPriceApplied + ")";

                        db_local.ExecuteStoreCommand(insertStatementDressing);

When i execute this query it get converts to follwing but not a complete executable string that can e accepted by database.

     Insert into OrderDressing(SrNo,
                               DressingID,
                               OrderItemID,
                               OrderID,
                               ProductID,
                               OrderMealDealItemID,
                               IsDeleted,
                               IsApplied,
                               Amount,
                               IsPriceApplied) 
                    values('02785d81-dc9f-4bf1-8050-7498bcb427c7',38,1,20,3,

Upvotes: 0

Views: 208

Answers (1)

DavidG
DavidG

Reputation: 119066

Because you are creating the SQL string manually you also need to create the NULL value manually. Try this:

insertStatementDressing = @"Insert into OrderDressing(SrNo,DressingID,OrderItemID,OrderID,ProductID,OrderMealDealItemID,IsDeleted,IsApplied,Amount,IsPriceApplied) 
values('" + odiDress.SrNo + "'," + odiDress.DressingID + "," + odiDress.OrderItemID + "," +
                                              latestOrderID_onLocal + "," + odiD.ProductID + "," +
                                              odiDress.OrderMealDealItemID.HasValue ? odiDress.OrderMealDealItemID.Value.ToString() : "NULL" + "," + odiDress.IsDeleted + "," + odiDress.IsApplied + "," + odiDress.Amount + "," + odiDress.IsPriceApplied + ")";

Note that you should avoid writing queries like this as they are vulnerable to SQL injection (among other issues)

Upvotes: 1

Related Questions