CODEWITHSUNDEEP
c#sqlms-access
HelpNeeder
HelpNeeder

Reputation: 6490

How do I insert data into MS Access file using SQL?

I am having a problem with inserting new data into MS Access 2007 file. It say I am having an incorrect SQL statement.

What is the problem here? I am not good at SQL. Please point out my error.

            try
            {
                // Open database connection.
                objOleDbConnection.Open();
                objOleDbCommand.CommandText = 
                    "INSERT INTO PersonalData (Type, UserName, Password) VALUES ('" + cmbType.Text + "','" + txtUserName.Text + "','" + txtPassword.Text + "')";
                // Execute creating table command.
                objOleDbCommand.ExecuteNonQuery();
            }

Upvotes: 2

Views: 4488

Answers (4)

XIVSolutions
XIVSolutions

Reputation: 4502

There a few places where you might encounter problems here.

  1. Type, Username, and Password are all (I think) MS Access keywords. While they seem to work when used within Access istself (like in the Querybuilder, for example), they seem to throw exceptions when used from Client Code. Surround the fieldnames in your SQL Statement with square brackets, so that Access treats them as literals.

  2. I strongly recommend using SQL Parameters for your in-line SQL, and then using ADO.NET Parameters to set the values. Google "SQL Injection Attack" to learn why. Plus, it's just good practive (there are some limited exceptions).

EDIT: Note that with OleDb, the parameters must appear in the same order as the fliednames in the list. THis is not the case with ADO & SQLClient. With Access, however, having your parameters out of order will create difficult-to-find problems . . .

Your SQL would then look like this:

INSERT INTO ([Type], [Username], [Password]) VALUES ( @Type, @UserName, @Password )

And your code might resemble THIS (I took some liberties here . . .

private void InsertUserData(int Type, String UserName, String Password)
{
    // The "Using" block handles object creation and disposal - 
    // handy for unmanaged resources like database connections:
    using(OleDbConnection cn = new OleDbConnection(YourConnectionString))
    {
        using(OleDbCommand cmd = new OleDbCommand())
        {
            cmd.Connection = cn;

            // 1.  Note the use of Parameters here. This will hinder attempts to 
            // compromise your app with SQl Injection and/or faulty user input. 

            // 2. Also note that, since "Type", "Username", and "Password" are all 
            // MS Access keywords, there is a potential for problems when 
            // used as fieldnames. Therefore we enclose them 
            // in square brackets [] in the "INSERT INTO" Clause:
            String SQL =
                "INSERT INTO PersonalData([Type], [UserName], [Password]) " +
                "VALUES(@Type, @UserName, @Password)";

            // Set the CommandText Proprty:
            cmd.CommandText = SQL;

            // Now create some OleDb Parameters:
            OleDbParameter prmType = new OleDbParameter("@Type", Type);
            OleDbParameter prmUserName = new OleDbParameter("@UserName", UserName);
            OleDbParameter prmPassword = new OleDbParameter("@Password", Password);

            // Add the params to the parameters collection:
            cmd.Parameters.Add(prmType);
            cmd.Parameters.Add(prmUserName);
            cmd.Parameters.Add(prmPassword);

            try
            {
                cn.Open();
                cmd.ExecuteNonQuery();
                cn.Close();
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }
}

Hope that helps . . .

Upvotes: 3

HansUp
HansUp

Reputation: 97131

Both Type and Password are reserved words. See Problem names and reserved words in Access.

If you must keep those as the field names, surround them with square brackets in your INSERT statement so the database engine will know to interpret them as fields:

"INSERT INTO PersonalData ([Type], UserName, [Password]) VALUES ...

On that same web page, follow the link for Database Issue Checker Utility. That utility can warn you about problems with reserved words in your application, and other potential troublesome issues.

Edit: If PersonalData includes additional fields which are required and do not have default values assigned, you must include those fields with values in your INSERT statement, or it will definitely fail.

Upvotes: 4

competent_tech
competent_tech

Reputation: 44971

To start with, you need to put quotes around your text data:

@"INSERT INTO PersonalData (Type, UserName, Password) VALUES (" + cmbType.SelectedIndex + ",'" + txtUserName.Text + "','" + txtPassword.Text + "')";

However, you would be much better off converting this to use parameters, since you won't have to worry about embedded quotes:

objOleDbCommand.CommandText = @"INSERT INTO PersonalData (Type, UserName, Password) VALUES (?, ?, ?)";

objOleDbCommand.Parameters.Add("Type", cmbType.SelectedIndex); 
objOleDbCommand.Parameters.Add("UserName", txtUserName.Text);
objOleDbCommand.Parameters.Add("Password", txtPassword.Text);

Upvotes: 5

Amadan
Amadan

Reputation: 198486

Let's say txtUserName.Text is Foo, and txtPassword.Text is bar. Then you're getting 

INSERT INTO PersonalData (Type, UserName, Password) VALUES (3,foo,bar)

instead of the syntactically correct

INSERT INTO PersonalData (Type, UserName, Password) VALUES (3,'foo','bar')

Since you don't have columns foo and bar, you are getting an error - is my assumption. Things get worse if you have baz,moo instead of foo. Or, gods forbid, Bobby Tables.

Upvotes: 3

Related Questions