syntax error in the INSERT INTO statement

Question

I am trying to insert some values in User table, there are 4 attributes in

User-User_Name varchar(20)  
User_Password varchar(20)  
Entry_Date timestamp  
Entry_Time timestamp

I have tried a lot but I'm not able to find out the problem.

using (OleDbConnection connection = new OleDbConnection(cs))
{

    string cb1 = "INSERT INTO User([User_Name],[User_Password],[Entry_Date],[Entry_Time]) VALUES (@val1,@val2,@val3,@val4)";

    using (OleDbCommand comm = new OleDbCommand(cb1, connection))
    {
        connection.Open();
        comm.Parameters.AddWithValue("@val1", txtUserName.Text);
        comm.Parameters.AddWithValue("@val2", txtPassword.Text);
        comm.Parameters.AddWithValue("@val3", DateTime.Now.ToString("d"));
        comm.Parameters.AddWithValue("@val4", DateTime.Now.ToString("t"));

        comm.ExecuteNonQuery();
     }
}

Try with just `DateTime.Now` - you are now strying to store a string value in a timestamp column — Hans Keﬆing, Sep 05 '15 at 19:20

score 0 · Answer 1 · answered Sep 05 '15 at 19:27

0

User is a reserved keyword in transact sql. Enclose it in square brackets like INSERT INTO [User] ....

answered Sep 05 '15 at 19:27

Kryptonian

860
3
10
26

score 0 · Answer 2 · edited May 23 '17 at 10:26

USER is a reserved keyword in OLE DB Provider. You need to use it with square brackets like [USER] I know User and USER are not the same but that means your database server is not case sensitive for this options.

Your Entry_Date and Entry_Time columns are timestamp but you try to add string representations of your DateTime parts.

Best way is to change your Entry_Date column to Date type and provide these columns your current local time with theirs .Date and .TimeOfDay properties.

And don't use AddWithValue method. It may generate unexpected results sometimes. Use Add method overloads to specify your parameter type and it's size.

For the last, do not save passwords as a plain text. Read Best way to store password in database

using (var connection = new OleDbConnection(cs))
using (var comm = new connection.CreateCommand())
{
    comm.CommandText = @"INSERT INTO [User]([User_Name],[User_Password],[Entry_Date],[Entry_Time]) 
                         VALUES (@val1,@val2,@val3,@val4)"
    comm.Parameters.Add("@val1", txtUserName.Text);
    comm.Parameters.Add("@val2", txtPassword.Text);
    comm.Parameters.Add("@val3", DateTime.Now.Date);
    comm.Parameters.Add("@val4", DateTime.Now.TimeOfDay);

    connection.Open();
    comm.ExecuteNonQuery();
}

thanks a lot soner, it works.Thank you for the suggestions.. You are the best!!!!!!!!!! :) — Arian Sajid, Sep 06 '15 at 07:19

syntax error in the INSERT INTO statement

2 Answers2