(Please Help) OleDbConnection - Snytax Error in INSERT INTO statement

Question

int


 ID =1;
string


 name = "abc"


;
string


 parent ="Parent1"


;

SQLString = "INSERT INTO module(module_id, module_name, parent_module) VALUES('"


 + ID + "','"


 + name + "','"


 + parent + "');"


;

it gave me error as below

Snytax Error in INSERT INTO statement


Could anyone check for me what's wrong in the SQLString?
Please help

Answer 1

I would, personally, do it like this, to avoid any problems:

OleDbConnection connection = new OleDbConnection("connection string");
string strQuery = "insert into [module] ([module_id], [module_name], [parent_module]) values (?,?,?)";
OleDbCommand command = new OleDbCommand(strQuery, connection);

OleDbParameter[] parameters =
{
  new OleDbParameter("?", ID),
  new OleDbParameter("?", name),
  new OleDbParameter("?", parent)
};

command.Parameters.AddRange(parameters);

Historically, for me, the above code will work flawlessly.  This is, of course, only true if you have not misspelled any of the column names or the table name (has happened to me).

---

There are 10 types of people in this world, those who understand Binary, and those who don't.

Answer 2

I agree with anubisascends, however if you want to keep doing it the way you are then;

1. The ID field shouldn't be enclosed in single quotes (') as it's numeric (I'm assuming that module_id is an int in the database), generally only text/chars etc. need the single quotes.

2. You need to be aware that your code will crash if anyone inputs certain special characters, like the single quote. It is also open to sql injection attacks. You can solve the single quite issue by doing a .Replace("''", "'") on the name and parent values before embedding them in the sql string, but that's not your only problem... which is why you should do it the way anubis recommends.

3. I don't *think* you need the ; on the end of the sql string (the one before ", you need the one after it). That might depend on your database engine, but trying taking it out to see if it helps.

I created a table with the three fields you've referenced and ran the insert statement I expect your code to generate, and it worked fine in sql express. What database engine are you using ? Is 'module' a reserved word in that database engine ?

Can you log and post the actual string created, rather than the code the generates it ?

Answer 3

What Yort said is the reason for using the parameters instead of a straight 'string' value for your query.

The parameters will do the following:

Determine the type of object you are trying to insert into the database,
Parse any string values to make them 'valid' for database entry,
Make reading what you are doing easier

---

There are 10 types of people in this world, those who understand Binary, and those who don't.

Answer 4

I would, personally, do it like this, to avoid any problems:

OleDbConnection connection = new OleDbConnection("connection string");
string strQuery = "insert into [module] ([module_id], [module_name], [parent_module]) values (?,?,?)";
OleDbCommand command = new OleDbCommand(strQuery, connection);

OleDbParameter[] parameters =
{
  new OleDbParameter("?", ID),
  new OleDbParameter("?", name),
  new OleDbParameter("?", parent)
};

command.Parameters.AddRange(parameters);

Historically, for me, the above code will work flawlessly.  This is, of course, only true if you have not misspelled any of the column names or the table name (has happened to me).

---

There are 10 types of people in this world, those who understand Binary, and those who don't.

Hi, may anyone tell me what is '?' ?