syntax error in sql statement...

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

Try

Code:

---

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

---

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

I think Hack put in too many single quote characters ;)

try this:

Code:

---

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

---

Quote:

---

Originally Posted by si_the_geek

I think Hack put in too many single quote characters ;)

try this:

Code:

---

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

---

---

Oops. :blush: I think you are right. :D

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:

Code:

---

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.