Errors when inserting date and time into QODBC query C#

Question

I am getting an error

ERROR [42500] ERROR: 3020 - There was an error when converting the date value "0000-00-48. In the field "salesOrder Transaction Date

The date value I am trying to insert is 4/4/2018.

My code

DateTime JobDate = Wintac_JobDate;

string addSalesOrder = "INSERT INTO SalesOrderLine (CustomerRefListID, TemplateRefListID," +
                       " SalesOrderLineItemRefListID, SalesOrderLineDesc,SalesOrderLineQuantity, SalesOrderLineRate, " +
                       "SalesOrderLineSalesTaxCodeRefListID, Memo, SalesOrderLineInventorySiteRefListID, SalesOrderLineInventorySiteLocationRefListID" +
                       ", TxnDate, ShipAddressAddr1, ShipAddressAddr2, ShipAddressAddr3, ShipAddressAddr4, ShipAddressAddr5, FQSaveToCache)" +
                       "VALUES('" + QBCustomerListID + "','" + templateLID + "', '" + LID + "', '" + Description + "', " + Quantity + ", " + 120 +  "," +
                                    " '" + SalesTax + "', '" +Wintac_WipNo+"','"+LaborSite+"','"+LaborSiteLocation+"',"+
                                    "?,'" + shipAdr1+ "','" + shipAdr2 + "','" + shipAdr3 + "','" + shipAdr4 + "','" + shipAdr5 + "'," +
                                    ""+ FQSaveToCache + ")";


OdbcCommand sqlcmd2 = new OdbcCommand(addSalesOrder, quickbookscon2);

sqlcmd2.CommandType = CommandType.Text;
sqlcmd2.CommandTimeout = 180;

MessageBox.Show(JobDate.ToShortDateString());
sqlcmd2.Parameters.Add("P7", OdbcType.DateTime).Value = JobDate

if (Quantity != 0)
{
   if (sqlcmd2.ExecuteNonQuery() == 1)
   {
       if(FQSaveToCache == 0)
          MessageBox.Show(" added successfully.");
   }
}
  sqlcmd2.Dispose()

I have tried converting the variable Job Date

• Date Time
• short date string
• long date string
• entering the variable directly into the query

Any help would be appreciated.

Answer 1

I think the main problem is on that line;

sqlcmd2.Parameters.Add("P7", OdbcType.DateTime).Value = JobDate.ToLongDateString()

You try to insert string representation on a DateTime typed column. That's quite wrong. You need to directly pass your DateTime value instead of passing it string representation. To learn this as a habit, please read Bad habits to kick : choosing the wrong data type

Other than this, I saw a few problem also in your code:

1. You should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.
2. Use using statement to dispose your connection and commmand automatically instead of callind Dispose method manually which you didn't even consider to do in your code.