C# & SQL "Syntax error in INSERT INTO statement"

[Haquo]

Hello Everybody,
I’m currently working on my end of year project and I got stuck on a very trivial thing. When I’m trying to add a new user to a database (local) I keep getting “Syntax error in INSERT INTO statement" I don’t really see where is the problem so I really hope You could spot it for me.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using Domain;

namespace Transport_Management_System_Tools
{
    class sqlConnector
    {

        OleDbConnection connection;
        OleDbCommand command;
        public void connectToDatabase()
        {
            connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database.accdb;Persist Security Info=False");
            command = connection.CreateCommand();
        }
        public sqlConnector()
        {
            connectToDatabase();
        }

        public void AddUser(Users User)
        {
            try
            {
                command.CommandText = "INSERT INTO Users (FirstName,LastName,DOB,UserName,PassWord,Administrator,Manager) VALUES ('" + User.FirstName + "','" + User.LastName + "','" + User.DOB + "','" + User.Username + "','" + User.Password + "','" + User.Administrator + "','" + User.Manager + "')";
                command.CommandType = System.Data.CommandType.Text;
                connection.Open();
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }


    }
}

[GhostWolf]

Do you need to .ToString() the User fields?

Does the database you're writing to accept single-quote delimiters for dates?

[Haquo]

Hello GhostWolf, thank You for interest.
I am using AcessDatabase (2007-2010)

Table name: Users
Properties:
ID:AutoNumber
FirstName:Text
LastName:Text
DOB: Date/Time
UserName:Text
PassWord:Text
Administrator :Yes/No
Manager: Yes/No

And this is the button that takes the values from the textboxes and checkboxes(admin,manager)

private void BtnCreateNewUser_Click(object sender, EventArgs e)
        {

            Users User = new Users();

            String name;
            String surname;
            DateTime DOB;
            String username;
            String password;
            Boolean administrator;
            Boolean manager;

            name = txtBoxName.Text;
            surname = txtBoxSurname.Text;
            DOB = Convert.ToDateTime(txtBoxDOB.Text);
            username = txtboxUsername.Text;
            password = txtBoxPassword.Text;
            administrator = chkBoxAdmin.Checked;
            manager = chkBoxManager.Checked;

            User.FirstName = txtBoxName.Text;
            User.LastName = txtBoxSurname.Text;
            User.DOB = Convert.ToDateTime(txtBoxDOB.Text);
            User.Username = txtboxUsername.Text;
            User.Password = txtBoxPassword.Text;
            User.Administrator = chkBoxAdmin.Checked;
            User.Manager = chkBoxManager.Checked;

            sqlConnector.AddUser(User);
        }

[GhostWolf]

Wow, you're much further into C# than I am; I'm going to have to copy your code to use as an example.

One thing you might try, though: change

"','" + User.DOB + "','"

to

"',#" + User.DOB + "#,'"

That was a problem I always encountered with Access databases, (when building SQL statements in VB6, anyway).

[GhostWolf]

Darn!! I must have hit some button too quickly, 'cause I also wanted to suggest that you use User.DOB.ToString().

[Ketsuekiame]

Users is a special table you need to wrap it in brackets to tell SQL you have defined your own version.

insert into [Users] (FirstName, La....)

Additionally, you should look into using Parameters. Not doing so will lead to an SQL Injection Attack which could destroy your database or reveal private user details.

[Haquo]

GhostWolf
I was trying to change
User.DOB = Convert.ToDateTime(txtBoxDOB.Text); to
User.DOB.ToString() = Convert.ToDateTime(txtBoxDOB.Text);
but that gives me format exception.
Im afraid that changing the code to "',#" + User.DOB + "#,'"
also didn't do it.

[Haquo]

Ketsuekiame
I try to change code to [Users] even change the table name to something else to be sure I’m not hitting a protected keyword but I am afraid it’s still not it. Yes I should be using parameters but I didn't use them as its first time I use SQL and C# so I didn’t even know about their existence until last week :) I also assume that using the parameters will not make much difference since the entire project will be used on the local PC.

[GhostWolf]

Try:

"',#" + User.DOB.ToString() + "#,'"

or

"','" + User.DOB.ToString() + "','"

Your original code attempts to concatenate a DateTime into your Insert statement. I suspect that one of the above might solve the problem. (I'm new to C# myself, so I'm not sure whether you'll need to provide the format for the ToString() method.)

[gusano79]

Rather than try and guess what's wrong, let's see what SQL is getting executed. Set a breakpoint on the line command.CommandType = System.Data.CommandType.Text and run the project... when it stops, see what the value of command.CommandText actually is. That should be illuminating.

Also, do get into parameterized queries sooner rather than later; I would require that of anyone writing ADO.NET on the job.

[Haquo]

GhostWolf

I did try that, and also User.DOB.ToShortDateString(). It concats it but i think that’s not where the problem is. If You have a look on a code bellow its very similar to this and it works perfectly without any concatenation. The difference is that code bellow dos not use bool, so i think there may be something wrong with them.

    public void Insert(Driver d)
        {
            try
            {
                command.CommandText = "INSERT INTO Driver (FirstName,LastName,DrivingLicense,DrivingLicenseExpiration,CPC) VALUES('" + d.FirstName + "','" + d.LastName + "','" + d.DrivingLicense + "','" + d.DrivingLicenseExpiration + "','" + d.CPC + "')";
                command.CommandType = System.Data.CommandType.Text;
                connection.Open();
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
                //

            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

[GhostWolf]

Gusano's got a good suggestion.

I'm so used to running into date problems when I'm trying new languages/databases that I didn't even notice the Booleans! You might try removing the quotes around them in the string you're building.

If I remember correctly, Access stores Boolean as 0=False and -1=True - so it might be giving you an error trying to insert a string into those fields.

[Haquo]

I fixed it. I didn't do any changes other than
command.CommandText = "INSERT INTO UsersList (FirstName,LastName,UserDOB,UserUsername,UserPassword,UserAdmin,UserManager) VALUES('" + User.FirstName + "','" + User.LastName + "','" + User.DOB + "','" + User.Username + "','" + User.Password + "'," + User.Administrator + "," + User.Manager + ")";

Whatever fieldname I used, it was the one making problems. Like always sloppy naming standards give sloppy results. Thanks everybody for help ! Especially GhostWolf. Thanks for baring with me all this time :)