sql - datetime format changes upon oledbCommand.executeNonQuery

Question

i have an sql insert query in my website,which inserts a few strings and ints, and a datetime in "dd/MM/yyyy HH:mm:ss", and until today it worked great. however, from today, for some odd reason, during the executeNonQuery method of the query, the format of the datetime changes to "MM/dd/yyyy HH:mm:ss". i have no clue as for why this is happening, and it is driving me crazy. can anyone please shed some light on why this happens and how i can prevent this change? any help would be appreciated.

the query:

"INSERT INTO Orders(OrderDate,MemberID,CityID,OrderAdress,CreditCardID,OrderStatus)VALUES(#" + o.OrderDate + "#," + o.MemberID + ","+o.CityID+",'" + o.OrderAdress + "',"+o.CreditCardID+",'Not sent')" 

o is an object holding all of the data.

Answer 1

Big problem when trying to build a query when concatenating strings. This is a HUGE thing for exposure to SQL-Injection. The best way to do it is with using PARAMETERIZED queries and you can look all over and find them, you probably were just unaware of them.

Basically in your query, you use a "?" as a place-holder for the parameter you want, then add a parameter object with the actual value / data type and the OleDb querying will put it in its place and have proper data type so you don't have to worry about formatting the string from a date in a specific order.

Also, for names, what if you had a person's name of "O'Conner". You have just pre-terminated your query string and would fail otherwise. You would be severely scratching your head.

Having said all that, lets get back to your query, make it a little more readable, and parameterize it...

You refer to ms-access as the database and OleDb which implies you are writing in either C#, or VB, maybe other. I will demonstrate using C#, you could change as needed to your dev language.

using(OleDbConnection connection1 = new OleDbConnection( WhateverYourConnectionString ) 
{
   connection1.Open();  
   using(OleDbCommand sqlcmd = new OleDbCommand("", connection1))
   {
      // simplified query and you can see the "?" place-holders
      sqlcmd.CommandText =
@"INSERT INTO Orders
  ( OrderDate,
    MemberID,
    CityID,
    OrderAdress,
    CreditCardID,
    OrderStatus )
  VALUES
  ( ?,
    ?,
    ?,
    ?,
    ?,
    'Not sent' )";

      // Now, add your parameters in the SAME ORDER as the "?" in the query
      sqlcmd.Parameters.AddWithValue("parmForDate", o.OrderDate ); 
      sqlcmd.Parameters.AddWithValue("parmForMember", o.MemberID ); 
      sqlcmd.Parameters.AddWithValue("parmForCity", o.CityID ); 
      sqlcmd.Parameters.AddWithValue("parmForAddress", o.OrderAddress ); 
      sqlcmd.Parameters.AddWithValue("parmForCard", o.CreditCardID ); 
      // since the last parameter is fixed, you can put that in explicitly.
      // you can similarly put fixed field of other strings, numbers.

      // Now you can execute it
      sqlcmd.ExecuteNonQuery();
   }

   connection1.Close()
}