Syntax error in INSERT INTO statement while using DateTime

Question

I am getting a "Syntax error in Insert INTO statement but I cannot figure out why.

I compared the statement to others I have made that work find, and can find no structural difference. The only difference is that in this one, I am trying to save a DateTime in my MS Access database.

I have my database field in MS Access set for Date/Time with a general format, which is MM/DD/YY HH:MM:SS AM (or PM). I have my DateTime format set the same.

When I set a break point and follow it through, I can see that the DateTime format being passed is correct and matches the general format in MS Access.

I'm sure the problem is right in fromt of me but I'll be darned if I can see it. Here is my code:

//method to save user input to database
public static void SaveData(ProgramLoginBOL busObject)
{
    try
    {
        String sSQLCommand = "INSERT INTO ProgramLogin ( " +
            "UserName, DateTime) VALUES ('" + busObject.UserName + 
            "','" + busObject.DtDate + "')";

        if (aConnection.State == ConnectionState.Closed)
        {
            aConnection.Open();
        }

        OleDbCommand cmd = aConnection.CreateCommand();
        cmd.CommandText = sSQLCommand;
        // Execute the SQL command
        cmd.ExecuteNonQuery();
        aConnection.Close();

        MessageBox.Show("Data Saved");                 
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.ToString());
        MessageBox.Show("Error! Data was not saved.");
    }
}

As I stated, debugging shows that busObject.DtDate matches the MS Access date/time format

You have a SQL injection vulnerability. You need to use parameters. — SLaks
– SLaks, Commented Apr 27, 2012 at 15:36

Conrad Frix · Accepted Answer · 2012-04-27 15:43:23Z

4

One way to solve the problem is to use a Parameterized query instead. Doing this leaves it up to the provider to worry about about types, and you don't have to worry about delimiting your string values with single quotes (helps with names like O'Donnel) or you date values with #. As a bonus it avoids any SQL injection attacks.

To do this use ? placeholders for the parameters

   string sSQLCommand = "INSERT INTO ProgramLogin ( " +
            "UserName, DateTime) VALUES (?,?)"

then later add the parameters

   cmd.Parameters.AddWithValue("?", busObject.UserName);
   cmd.Parameters.AddWithValue("?", busObject.DtDate);

edited Apr 27, 2012 at 15:43

answered Apr 27, 2012 at 15:38

Conrad Frix

52.8k13 gold badges99 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Programming Newbie Over a year ago

Although this is not a homework assignment I am using my school textbook as a guideline to create my program and it shows nothing like this. Is this how all database inserts should be made in order to protect what the poster called "SQL injection vulnerability?"

Conrad Frix Over a year ago

@ProgrammingNewbie. Using parameterized queries is one way to protect yourself from SQL Injection. With Access you can do it using Query Definitions with parameters. With other databases you can use Stored Procedures instead. Also of course if you totally trust of the values then its less of an issue

D'Arcy Rittich · Accepted Answer · 2012-04-27 15:39:58Z

1

With Access, you must surround the date with the # character.

So, change your command to:

String sSQLCommand = "INSERT INTO ProgramLogin (UserName, DateTime) VALUES ('" + busObject.UserName + "',#" + busObject.DtDate + "#)";

Format your date string like this for the best results:

#2012-04-21 13:21:25#

answered Apr 27, 2012 at 15:39

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

Comments

phoog · Accepted Answer · 2012-04-27 15:41:12Z

1

The display format you've assigned to the Access Date/Time column has no bearing on the correct string format for a date/time value in a query.

You can call the appropriate overload of ToString on the DateTime value, to format it as Access requires, or, as SLaks commented, you can use a parameter. Using a parameter is more secure, and it also means that you don't need to worry about how the value is formatted. The OleDb provider will take care of that for you.

answered Apr 27, 2012 at 15:41

phoog

43.2k6 gold badges84 silver badges125 bronze badges

1 Comment

Programming Newbie Over a year ago

Thank you very much for your help. I thought the formats had to match. Since using a parameter is more secure then I will use that.

Collectives™ on Stack Overflow

Syntax error in INSERT INTO statement while using DateTime

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related