exception: Missing semicolon (;) at end of SQL statement [modified]

[benams]

I'm using msaccess(2007) database and I get the error message above while tryig to execute the following code:

OleDbCommand cmd = new OleDbCommand();
cmd.CommandText = "INSERT INTO tblCategory(CategoryName, OrderNum) " +
                  "VALUES(@CategoryName, @OrderNum) " +
                  "SELECT @@IDENTITY AS ID";
cmd.Parameters.Add(new OleDbParameter("@CategoryName", Category.CategoryName));
cmd.Parameters.Add(new OleDbParameter("@OrderNum", Category.Order));
cmd.Connection = DataAccess.CreateConnection("Malasot", false);
DataTable dt = new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);

if(cmd.Connection.State != ConnectionState.Open)
   cmd.Connection.Open();

adapter.Fill(dt); // \*\*\*the exception occurs here.\*\*\*

note: I have tryed putting semicolon between the 2 statements and it doesn't solve the problem.

modified on Monday, June 20, 2011 2:38 PM

[Parwej Ahamad]

as your error stating, just place semi colon after the insert statement.

cmd.CommandText = "INSERT INTO tblCategory(CategoryName, OrderNum) " +
"VALUES(@CategoryName, @OrderNum); " +
"SELECT @@IDENTITY AS ID";

Parwej Ahamad ahamad.parwej@gmail.com

[benams]

I've just tryed it, it didn't help

[AspDotNetDev]

How about adding a semicolon after the second SQL statement:

cmd.CommandText =
"INSERT INTO tblCategory(CategoryName, OrderNum) " +
"VALUES(@CategoryName, @OrderNum); " +
"SELECT @@IDENTITY AS ID;";

Help a brotha out and vote Managing Your JavaScript Library in ASP.NET as the best ASP.NET article of May 2011.

[Parwej Ahamad]

Error is same OR giving different one? Can you try once to execute two separate statement on by one because I have double that Access generating instantly Identity value. Thanks, Parwej

Parwej Ahamad ahamad.parwej@gmail.com

[benams]

I've tryed adding semicolon and the error message I get is: "Characters found after end of SQL statement." I guess it will work well if I execute 2 seperated commands, but my goal is to execute all in one command, if it's possible.

[Parwej Ahamad]

Hi Benames, I haven't try from side, but I had searched on Google and they clearly mentioned that we can not execute multiple statement in single query against Ms Access DB. So may be cause of the error. So if possible execute two separated statement to achieve your goal. Thanks, Parwej

Parwej Ahamad ahamad.parwej@gmail.com

[benams]

Ok, probably you are right. It goes well when I run it in 2 different commands.