Invalid Syntax Near ',' - SQL C# Error

Question

I keep getting the following error: (C# WinForms)

"Invalid syntax near ','"

I have the following code:

    // Initialize and instantiate a new reader object.
    SqlDataReader slrr = null;
    // Send command.
    System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("SELECT ActivationCode FROM CAccounts WHERE ActivationCode=" +
    _activationcode, connection);

    slrr = command.ExecuteReader();

    // read result(s) of command.
    while (slrr.Read())
    {
        if (slrr["ActivationCode"].ToString() == _activationcode.Text)
        {
            stat.Text = "It appears that these details have already been registered.";
            Properties.Settings.Default.GU = false;
            Properties.Settings.Default.Save();
        }
        else
        {
            System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand(
                "INSERT INTO CAccounts (FirstName, LastName, Country, Gender, EmailAddress, ActivationCode, ClientID, IsActivated) VALUES ('" +
                _firstname.Text + "', '" + _lastname.Text + "', '" + _country.Text + "', '" + gender + "', '" +
                _email.Text + "', '" + _activationcode.Text + "', '" + _clientid.Text + "', '" + "yeh'", connection);

            comm.ExecuteNonQuery();

            stat.Text = "Product Activation succeeded.";
            Properties.Settings.Default.GU = true;
            Properties.Settings.Default.FirstName = _firstname.Text;
            Properties.Settings.Default.LastName = _lastname.Text;
            Properties.Settings.Default.Country = _country.Text;
            Properties.Settings.Default.Gender = gender;
            Properties.Settings.Default.DateOfBirth = _dateofbirth.Text;
            Properties.Settings.Default.EmailAddress = _email.Text;
            Properties.Settings.Default.ActivationID = _activationcode.Text;
            Properties.Settings.Default.ClientID = _clientid.Text;
            Properties.Settings.Default.IsActivated = true;
            Properties.Settings.Default.Save();
        }
    }
}
catch (Exception exception)
{
    // Catch the exception and throw an error.
    stat.Text = exception.Message;
}

I have absolutely no idea what I've done wrong. Can somebody please help me?

Answer 1

Think about the line of code where you construct the INSERT command. What do you think will happen if any of the fields contains an apostrophe?

You guessed it, the statement becomes invalid.

You can solve this by using SqlCommand.Parameters. See the example on that page.

Of course, the same applies to the SELECT command near the top of your code snippet.

Answer 2

Instead of:

"', '" + "yeh'", connection);

It should be:

"', '" + "yeh')", connection);

You forgot to close your VALUES parenthesis, that's why.

Answer 3

If IsActivated is some sort of a Boolean value, is 'yeh' valid? I would start by building the SQL command into a string variable and printing it out. There is something going wrong in all of the concatenation. Printing out the SQL string before you pass it into the command object should make the error obvious.

I'm not a C# expert, but isn't there a better way to take a list of strings, surround them in quotes, and join the result using "," as a separator? Something like the following Python snippet makes building SQL strings a lot less error prone:

>>> s = str.join(', ', ("'{0}'".format(x) for x in ['bob', 'alex', 'guido']) )
>>> print s
'bob', 'alex', 'guido'

Then again, you are infinitely better off letting someone else construct the SQL so that you avoid little embarrassments caused by SQL injection.