CODEWITHSUNDEEP
c#mysqldatetime
Cfaniak
Cfaniak

Reputation: 409

DateTime.Now to mysql datetime

i got problem with a query, got something like this 

command.CommandText = "SELECT " +
        "COUNT(a.`id`) " +
   "FROM " +
        "`messageaccess` a " +
   "WHERE " +
        "a.`Users_LOGIN` = '" + Settings.UserLogin + "' " +
        "AND a.`Status` = '" + Enums.MessageStatus.New + "' " +
        "AND a.`FOLDER` = '" + Enums.MessageFolder.INBOX + "'" +
        "AND a.`ShowAlert` = '" + Enums.YesNo.No + "'" +
        "AND a.`Postponed` <= " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "";

but sql throws me exception You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '14:40:37' at line 1

tried diffrent combinantions but nothing works :(

Upvotes: 0

Views: 4592

Answers (5)

David Conde
David Conde

Reputation: 4637

try using this line instead:

"AND a.`Postponed` <= NOW()"

and it should work with the native MySql function for the current time.

Upvotes: 1

Claudio Redi
Claudio Redi

Reputation: 68400

You shouldn't build your query appending strings. This is not very safe (sql injection) and you're not taking advantage of the ADO .NET capabilities to set the correct format according the parameter type.

You should use parametrized queries.

Upvotes: 0

Jon Skeet
Jon Skeet

Reputation: 1499740

The simple answer is not to embed values directly into the SQL to start with.

Use a parameterized SQL statement, specify the parameter value as DateTime.Now, and all will be well:

  • Your SQL will be easier to read (as it'll just be the code, not the data)
  • You won't need to worry about formatting of things like numbers and dates
  • You won't be vulnerable to SQL injection attacks

Upvotes: 3

Heinzi
Heinzi

Reputation: 172200

Have a look at named parameterized queries. They take care of these formatting issues for you.

Upvotes: 0

Timwi
Timwi

Reputation: 66573

You forgot the quotation marks around the date/time thing.

Upvotes: 2

Related Questions