I want to insert null value in database if that variable is blank, but in command string it insert blank instead of null. so give error "
Incorrect syntax near ','
". i am posting my code with Command string and error message in following. thanks in advance.
What I have tried:
for (int i = 0; i < datProjectCostDetail.Rows.Count; i++)
{
int? PageSizeID;
double? RatePerPage;
int? TotalPage;
if (datProjectCostDetail.Rows[i][6].ToString() == "")
{PageSizeID = null; }
else
{PageSizeID = (int) datProjectCostDetail.Rows[i][6]; }
if (datProjectCostDetail.Rows[i][9].ToString() == "")
{ RatePerPage = null; }
else
{ RatePerPage = (double) datProjectCostDetail.Rows[i][9]; }
if (datProjectCostDetail.Rows[i][10].ToString() == "")
{ TotalPage = null; }
else
{ TotalPage = (int) datProjectCostDetail.Rows[i][10]; }
cmd = new SqlCommand("Insert into TblProjectCostDetail values(" + ProjectID + ", " + datProjectCostDetail.Rows[i][0] + ", '" + datProjectCostDetail.Rows[i][2].ToString() + "', " + datProjectCostDetail.Rows[i][3] + ", '" + datProjectCostDetail.Rows[i][5].ToString() + "', " + PageSizeID + ", '" + datProjectCostDetail.Rows[i][8].ToString() + "', " + RatePerPage + ", " + TotalPage + ", '" + datProjectCostDetail.Rows[i][11].ToString() + "', " + datProjectCostDetail.Rows[i][12] + " )", con, trans);
cmd.ExecuteNonQuery();
OutPut of Command Query
Insert into TblProjectCostDetail values(1, 1, '09/Sep/2016', 1, 'Miscellaneous Payment', , '', , , 'Discount', 600 )