Update datetype date

Question

I am getting this exception: "Fatal error encountered during command execution."

My current code looks like this:

internal void UpdateLastLogin(string userEmail)
    {
        DateTime today = DateTime.Now;           

        using (_msqlCon = new MySqlConnection(_connectionString))
        {
            _msqlCon.Open();

            _query = "UPDATE Regs SET `LastLogin` = ?today WHERE Email = ?userEmail";
            _command = new MySqlCommand(_query, _msqlCon);               

            _command.ExecuteNonQuery();
        }
    }

The column: LastLogin has Datatype date What I tried at first before searching more info, was:

_query = "UPDATE Regs SET LastLogin = ?today WHERE Email = ?userEmail";

I have also tried:

_query = "UPDATE Regs SET `LastLogin` = `?today` WHERE Email = ?userEmail";

Notice that the difference is the quot marks on the columns and the variable. Where am I going wrong?

Ps. The column Email has type VarChar

Why you using it as `?today`? Maybe your command thinks this `?today` as a parameter? MySQL has `CURDATE()` to get's current date by the way. — Soner Gönül, Apr 06 '15 at 12:06
Oh yeah, you might be right that it maybe takes ?today as a parameter. This is normally how I do when the variable is a parameter. I thought that this was the syntax when we are dealing with variables, and not just parameters — user1960836, Apr 06 '15 at 12:10

Soner Gönül · Accepted Answer · 2015-04-06T12:23:00.510

1

You can't use a variable in your command like that. Your syntax is wrong.

Define them as a parameter in your command and add your command their parameter name and values.

_query = "UPDATE Regs SET `LastLogin` = @today WHERE Email = @mail";
_command = new MySqlCommand(_query, _msqlCon);
_command.Parameters.AddWithValue("@today", today);
_command.Parameters.AddWithValue("@mail", userEmail);

From: 5.1.4 Working with Parameters

Note

The parameter is preceded by an '@' symbol to indicate it is to be treated as a parameter.

edited Apr 06 '15 at 12:23

answered Apr 06 '15 at 12:10

Soner Gönül

97,193
102
206
364

There is no need to pass it as a parameter in the method. I want to use it as a variable. How can I tell the mysql that it is a variable and not parameter? – user1960836 Apr 06 '15 at 12:11
1

@user1960836 Yes, **YOU NEED!**. You _should_ define your parameter names in your command and create your parameter name and it's value and add this parameter to your command. [Parameterized queries](http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/) are the best way to pass your local variables in your command. Otherwise, string concatenations are open for [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection) attacks. – Soner Gönül Apr 06 '15 at 12:17
Thanks for the headsup and link. I guess I'll pass it as a parameter – user1960836 Apr 06 '15 at 12:26
But regarding the syntax @ vs ?. This is not wrong, for me ? works while @ doesn't – user1960836 Apr 06 '15 at 12:28
@user1960836 As Andy says, some database provider/systems are supports `?` like [PHP PDO](http://php.net/manual/en/pdo.prepare.php) **but only** as `?` not as `?today`. Is MySQL one of them? I don't certainly know that but Andy says; `?` itself stands for unnamed parameter in MySQL.. So.. – Soner Gönül Apr 06 '15 at 12:34
@user1960836 Also this question says, that's true. [What is the question mark's significance in mysql?](http://stackoverflow.com/questions/675010/what-is-the-question-marks-significance-in-mysql) – Soner Gönül Apr 06 '15 at 12:36

score 0 · Answer 2 · answered Apr 06 '15 at 12:11

0

Standard MySqlCommand parameters should begin with @ sign, not ?.

So your query text should be "UPDATE Regs SET LastLogin = @today WHERE Email = @userEmail"

Also, if you're using parameters in your query text - you have to specify them explicitly by using _command.Parameters.Add.

answered Apr 06 '15 at 12:11

Andrey Korneyev

26,353
15
70
71

The @ doesn't work for me, here the ? works. I know this is a problem for some. I know someone who use the @ cus the ? doesn't work for them. I don't know what causes this, but my guess is that it is different versions or so – user1960836 Apr 06 '15 at 12:13
Yeah I know I must use the _command.Parameters.Add for parameters, but this is not a parameter from the method – user1960836 Apr 06 '15 at 12:14
I guess the I should not use the _command.Parameters.Add, is that right? – user1960836 Apr 06 '15 at 12:14
@user1960836 Hmm.... now I'm not sure what you're trying to achieve. If "*this is not a parameter from the method*" - then what `?userEmail` supposed to be and how it should be handled by DB engine? – Andrey Korneyev Apr 06 '15 at 12:16
JFI, @ is for SQL Server, need to use ? for MySql. – wonderbell Apr 06 '15 at 12:19
1

@user1960836 Also, as far as I know, `?` itself stands for unnamed parameter in MySQL, so construction like `?some_name` has no sense. `@` is a prefix for named parameter. – Andrey Korneyev Apr 06 '15 at 12:21
@AndyKorneyev sorry for the missunderstanding. I am using `_command.Parameters.AddWithValue("userEmail", userEmail);` for the parameter. I thought you were talking about the date variable, which is not passed as a parameter, and for that reason, I dont do `_command.Parameters.Add` for the date – user1960836 Apr 06 '15 at 12:22

Update datetype date

2 Answers2