1

I keep getting an error when I attempt to insert values into a Access database.

The error is syntactic, which leads to the following exception:

OleDbException was unhandled Syntax error in INSERT INTO statement.

private OleDbConnection myCon;

public Form1()
{
    InitializeComponent();
    myCon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\File.mdb");
}

private void insertuser_Click(object sender, EventArgs e)
{
    OleDbCommand cmd = new OleDbCommand();
    myCon.Open();
    cmd.Connection = myCon;
    cmd.CommandType = CommandType.Text;

    cmd.CommandText = "INSERT INTO User ([UserID], [Forename], [Surname], " +
                                        "[DateOfBirth], [TargetWeight], [TargetCalories], [Height]) " +
                      "VALUES ('" + userid.Text.ToString() + "' , '" +
                                    fname.Text.ToString() + "' , '" +
                                    sname.Text.ToString() + "' , '" +
                                    dob.Text.ToString() + "' , '" +
                                    tarweight.Text.ToString() + "' , '" +
                                    tarcal.Text.ToString() + "' , '" +
                                    height.Text.ToString() + "')";

    cmd.ExecuteNonQuery();
    myCon.Close();
}
Improve this question
4
  • 2
    Can you post the error you are receiving? Commented Jan 7, 2011 at 16:15
  • 1
    are you getting an exception? Is the application crashing? Enclose the statements in a try..catch block, catch the exception and post the exception message. Commented Jan 7, 2011 at 16:17
  • Hi this is the exception message: Syntax Error in INSERT INTO statement OleDbException was unhandled Commented Jan 7, 2011 at 16:46
  • I don't work in C#, so don't know how it interacts with your database interface layer, but Jet/ACE uses # as the delimiter for the string representation of date values, not '. Changing that might help. At the very least, write out the SQL string being sent and see if you can run it in interactive Access. Commented Jan 8, 2011 at 3:33

6 Answers 6

Reset to default
8

Well, you haven't specified what the error is - but your first problem is that you're inserting the data directly into the SQL statement. Don't do that. You're inviting SQL injection attacks.

Use a parameterized SQL statement instead. Once you've done that, if you still have problems, edit this question with the new code and say what the error is. The new code is likely to be clearer already, as there won't be a huge concatenation involved, easily hiding something like a mismatched bracket.

EDIT: As mentioned in comments, Jet/ACE is vulnerable to fewer types of SQL injection attack, as it doesn't permit DML. For this INSERT statement there may actually be no vulnerability - but for a SELECT with a WHERE clause written in a similar way, user input could circumvent some of the protections of the WHERE clause. I would strongly advise you to use parameterized queries as a matter of course:

  • They mean you don't have to escape user data
  • They keep the data separate from the code
  • You'll have less to worry about if you ever move from Jet/ACE (whether moving this particular code, or just you personally starting to work on different databases)
  • For other data types such as dates, you don't need to do any work to get the data into a form appropriate for the database

(You also don't need all the calls to ToString. Not only would I expect that a property called Text is already a string, but the fact that you're using string concatenation means that string conversions will happen automatically anyway.)

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

13 Comments

@Howard: With no more detail than that? Oh well - that's still better than not even knowing that much. When your SQL is parameterized, it may well be easier to see the problem - or it may just go away anyway, if the error was due to your data containing quotes.
Oh, great expert of SO, please give us a rundown of the extent of the dangers of SQL Injection with a Jet/ACE data store. You might want to search SO before replying with that...
@David-W-Fenton: It seems to me that some classes of SQL injection attack aren't applicable here (those involving DML), but other classes could be (those with straight "WHERE X='"+input+'" code. (I've read your post at pcreview.co.uk/forums/thread-4021211.php which mentioned this, and the related SO posts). In addition, parameterized SQL keeps data separate from code, which I view to be general cleaner. Finally, I can easily see someone porting this code from Jet/ACE to another database platform without reconsidering every possible danger.
Also forgot to mention that it means you don't need to worry about "cleaning" or escaping the input. I view parameterized queries as the best way to cope with, say, a surname of O'Reilly. I dare say there are Jet/ACE-specific routines to perform escaping - but what are the benefits of those over using a technique which will be broadly portable across databases? (You may need to change between named and additional parameters, but the basic approach will be the same.)
@Saeid: That doesn't actually seem related at all.
|
4

I posted this as a comment to the duplicate question at: Syntax error in INSERT INTO statement in c# OleDb Exception cant spot the error

Put brackets [] around the table name "User". It's a reserved word in SQL Server.

"User" is also a reserved word in Access (judging by the provider in your connection string).

But I completely agree with Jon--if you fix your current implementation, you are just opening up a big security hole (against your User table, no less!)

Improve this answer

Comments

1

This problem may occur if your database table contains column names that use Microsoft Jet 4.0 reserved words.

Change the column names in your database table so that you do not use Jet 4.0 reserved words.

Improve this answer

1 Comment

I had to put [] around table and column names in the query to allow the reserved word to be accepted.
0

If TargetWeight, Height, and TargetCalories are floating-point or integer values, they don't need to be surrounded by quotes in the SQL statement.

Also, not directly related to your question, but you should really consider using a parameterized query. Your code is very vulnerable to SQL injection.

Improve this answer

Comments

0 
public decimal codes(string subs)
    {
        decimal a = 0;


        con_4code();
            query = "select SUBJINTN.[SCODE] from SUBJINTN where SUBJINTN.[ABBR] = '" +                         subs.ToString() + "'";
            cmd1 = new OleDbCommand(query, concode);
            OleDbDataReader dr = cmd1.ExecuteReader();

here is error in dr it says syntax error ehile in DBMS its working Well

            if (dr.Read())
            {
                a = dr.GetDecimal(0);
                MessageBox.Show(a.ToString());
            }
            return a;



    }
Improve this answer

1 Comment

OleDbDataReader dr = cmd1.ExecuteReader(); on this point its showing its a syntax error while the simple quary working good.........
0

After this

cmd.CommandText="INSERT INTO User ([UserID], [Forename], [Surname], [DateOfBirth], [TargetWeight], [TargetCalories], [Height]) Values ('" + userid.Text.ToString() + "' , '" + fname.Text.ToString() + "' , '" + sname.Text.ToString() + "' , '" + dob.Text.ToString() + "' , '" + tarweight.Text.ToString() + "' , '" + tarcal.Text.ToString() + "' , '" + height.Text.ToString() + "')";

check what this contains, maybe [DateOfBirth] has illegal format

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.