CODEWITHSUNDEEP
c#ms-accessado.net
Programming Newbie
Programming Newbie

Reputation: 1227

Syntax error in INSERT INTO statement while using DateTime

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

Upvotes: 1

Views: 2277

Answers (3)

Conrad Frix
Conrad Frix

Reputation: 52645

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);

Upvotes: 4

phoog
phoog

Reputation: 43036

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.

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171361

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#

Upvotes: 1

Related Questions