syntax error in sql statement...

[bnathvbdotnet]

I am writing a "insert" sql statement.

and I get exception as "Syntax error (missing operator) in query expression '6/20/2006 9:30:45 AM'."

The following is the sql statement:

string ssql = "INSERT INTO AT_ARC_ADVANCES(AT_ARC_DATA_DATE,AT_ARC_MATURITY) VALUES ";

ssql = ssql + "("+ DateTime.Now + ','+ "2 Years" + ")";

try
{
accessCommand.CommandText = ssql;
accessConnection.Open();
accessCommand.ExecuteNonQuery();
}
catch (Exception ex)
{

Console.WriteLine(ex.Message);
}

[Hack]

Try

string ssql = "INSERT INTO AT_ARC_ADVANCES (AT_ARC_DATA_DATE,AT_ARC_MATURITY) VALUES ";
ssql = ssql & "('" & DateTime.Now & "','" & "'2 Years'" & "')";

[bnathvbdotnet]

Nope. Didn't work. the error is "Syntax error (missing operator) in query expression '''2 Years'''."

[si_the_geek]

I think Hack put in too many single quote characters

try this:

ssql = ssql & "('" & DateTime.Now & "','2 Years')";

[Hack]

I think Hack put in too many single quote characters

try this:

ssql = ssql & "('" & DateTime.Now & "','2 Years')";

Oops. I think you are right.

[jmcilhinney]

And here's just one more example of why you should never build SQL statements using string concatenation. If you ever want to include a variable value in an SQL statement then you should always use a parameter:

string ssql = "INSERT INTO AT_ARC_ADVANCES(AT_ARC_DATA_DATE, AT_ARC_MATURITY) VALUES (@AT_ARC_DATA_DATE, '2 Years')";
accessCommand.CommandText = ssql;
accessCommand.Parameters.AddWithValue("@AT_ARC_DATA_DATE", DateTime.Now);

Having said that, I'm guessing you may actually want DateTime.Today, which has the time component zeroed, rather than DateTime.Now. Also, there is the GETDATE function in T-SQL that gets the current date so you could just use that in your SQL code instead of inserting a value from your VB code, assuming that it is supported by Access, which I think is the database you're using.