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()
}
