Error With Dynamically Created SQL Insert statement

Question

May I know what's wrong with my statement? I receive a syntax error. Been trying to find out what's wrong all day. :(

cmd.CommandText = "INSERT INTO LogIn(Username,Password) VALUES('" + AddUsernameTextBox.Text + "','" + AddPasswordTextBox.Text + "')";

Answer 1

cmd.CommandText = "INSERT INTO LogIn([Username],[Password]) VALUES('" + AddUsernameTextBox.Text + "','" + AddPasswordTextBox.Text + "')";

This code will help if the error is due to reserved keywords :- username and password. Please quote the error if this is not the case .

Answer 2

  command.CommandText = "INSERT INTO Login([Username],[Password]) VALUES(@Username, @Password)";

  //Not sure how you create your commands in your project
  //here I'm using the ProviderFactory to create instances of provider specific DbCommands.

  var parameter = dbProviderFactory.CreateParameter();
  parameter.DbType = System.Data.DbType.String;
  parameter.ParameterName = "@Username";
  parameter.Value = AddUsernameTextBox.Text;
  command.Parameters.Add(parameter);

  parameter = dbProviderFactory.CreateParameter();
  parameter.DbType = System.Data.DbType.String;
  parameter.ParameterName = "@Password";
  parameter.Value = AddPasswordTextBox.Text;
  command.Parameters.Add(parameter);

Below is a more complete code sample of using ConnectionStringSettings and DbProviderFactory etc. This is not going to solve your problem, but this is the way to do data access if you're using ADO.NET core as you seem to be doing in your sample.

  ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["SomeConnectionName"];
  if (connectionStringSettings == null)
    throw new Exception("Application config file does not contain a connectionStrings section with a connection called \"SomeConnectionName\"");
  DbProviderFactory dbProviderFactory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName);
  using (var dbConnection = dbProviderFactory.CreateConnection())
  {
    dbConnection.ConnectionString = connectionStringSettings.ConnectionString;
    dbConnection.Open();
    using (var command = dbConnection.CreateCommand())
    {
      command.CommandText = "INSERT INTO Login([Username],[Password]) VALUES(@Username, @Password)";

      var parameter = dbProviderFactory.CreateParameter();
      parameter.DbType = System.Data.DbType.String;
      parameter.ParameterName = "@Username";
      parameter.Value = AddUsernameTextBox.Text;
      command.Parameters.Add(parameter);

      parameter = dbProviderFactory.CreateParameter();
      parameter.DbType = System.Data.DbType.String;
      parameter.ParameterName = "@Password";
      parameter.Value = AddPasswordTextBox.Text;
      command.Parameters.Add(parameter);

      var dbTransaction = dbConnection.BeginTransaction();
      try
      {
        command.ExecuteNonQuery();
        dbTransaction.Commit();
      }
      catch (Exception)
      {
        dbTransaction.Rollback();
        throw;
      }
    }
  }

the app.Config file that the code above relies on would look like this the following. Of course only the connectionStrings section in the config file is important in this context

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="SomeConnectionName" providerName="System.Data.OleDb" connectionString="Your Provider Specific Connection String" />
  </connectionStrings>
</configuration>

Answer 3

The Best way is to use Parameters: '@' By this your code will look much more clearer and easy to understand. And makes your Application more secure.

try this code:

            using (var con = new OleDbConnection(_constring))
            {
                con.Open();
                using (
                    var cmd =
                        new OleDbCommand(
"UPDATE LogIn SET Username=@Username, Password=@Password WHERE (ID = @Id)",
                            con))
                {
                    try
                    {

                        cmd.Parameters.AddWithValue("@Username",EditUsernameTextBox.Text);
                        cmd.Parameters.AddWithValue("@Password",EditPasswordTextBox.Text);
                        cmd.Parameters.AddWithValue("@Id",IDTextBox.Text);


                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        throw;
                    }
                    finally
                    {
                        con.Close();
                    }

                }

Regards!

Answer 4

Please protect the single quotes. Also, you may need a closing semicolon in the Access SQL string.

cmd.CommandText = "INSERT INTO LogIn(Username,Password) VALUES('" + AddUsernameTextBox.Text.Replace("'","''") + "','" + AddPasswordTextBox.Text.Replace("'","''") + "');";

It is of course only 100% better to use parameterized queries; from you other questions is this C#/Visual Studio against MS Access through OLE/Jet?

Answer 5

Is ID column an integer? If not you need to wrap values in single quotes, too. Also, try removing the parentheses.

Answer 6

Most likely, the value in IDTextBox.text is not numeric...

But like Daniel points out, this is very vulnerable to SQL inject..

What would happen if I typed:

' ; DROP TABLE login

in the EditUserNameTextBox field

Answer 7

Check if this works. You were missing single quotes for the value in your WHERE statement:

"UPDATE
    LogIn
SET
    Username = '" + EditUsernameTextBox.Text + "'
    ,Password = '" + EditPasswordTextBox.Text + "'
WHERE
    (ID = '" + IDTextBox.Text + "')";

Plus, make sure, as Daniel White mentioned, you take care of any SQL Injection.

Answer 8

You missed a pair of apostrophes, if your ID is non-numeric:

WHERE (ID ='" + IDTextBox.Text + "')";

Answer 9

Do the values of EditUsernameTextBox.Text or EditPasswordTextBox.Text themselves have quotes? This will bollix up the SQL.

If so, you'll need to escape them. or don't use string concatenation as pointed out already...

And have you printed the statement out to see what it looks like as requested...?