C# SQL Command not ended properly

Question

The following code works, however it doesn't commit the change in the database.

cnn.Open();
OleDbCommand cmd = cnn.CreateCommand();

cmd.Parameters.Add(new OleDbParameter(":var1", ds.Tables[0].Rows[i]["USERNAME"].ToString()));
cmd.Parameters.Add(new OleDbParameter(":var2","1"));
cmd.CommandText = "UPDATE JCOLEMAN.IBI_TEST SET FLAG=:var2 WHERE USERNAME=:var1";
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cnn.Close();

I changed the code to the snippet below and get the error

Additional information: ORA-00933: SQL command not properly ended

Code:

cnn.Open();
OleDbCommand cmd = cnn.CreateCommand();

cmd.Parameters.Add(new OleDbParameter(":var1", ds.Tables[0].Rows[i]["USERNAME"].ToString()));
cmd.Parameters.Add(new OleDbParameter(":var2","1"));
cmd.CommandText = "UPDATE JCOLEMAN.IBI_TEST SET FLAG=:var2 WHERE USERNAME=:var1; commit";
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cnn.Close();

So I thought I forgot the last ";"(as seen below) but that causes problems, too. Can anyone help me figure out why I can't get the code to accept the commit command?

cmd.CommandText = "UPDATE JCOLEMAN.IBI_TEST SET FLAG=:var2 WHERE USERNAME=:var1; commit;";

Have you tried using an OleDbTransaction directly instead of appending the commit as text? Also if this is oracle it might be better to use an Oracle specific ADO.NET db objects instead of the OleDb one which might be causing the problem. — Igor
– Igor, Commented Apr 4, 2016 at 19:45
I would use a Oracle specific ado.net library like Oracle Data Provider for .NET, NuGet install = Install-Package Oracle.ManagedDataAccess. This would probably better translate your commands to an appropriate Oracle platform syntax than Ole. — Igor
– Igor, Commented Apr 4, 2016 at 19:59
I thought OleDb didn't use named parameters, which means the parameters have to be in index order. Try switching the two parameter lines around. — LarsTech
– LarsTech, Commented Apr 4, 2016 at 20:02
@LarsTech - good point. I think Ole also uses ? for parameter placeholders. Another good reason to not use it when there is a more appropriate driver available. — Igor
– Igor, Commented Apr 4, 2016 at 20:05

Henrique · Accepted Answer · 2016-04-04 19:57:32Z

1

This happens because in Oracle you must put BEGIN END; blocks to represent a statement.

In this case you should use a transaction in your command like:

cnn.Open();
OleDbTransaction transaction = cnn.BeginTransaction();
OleDbCommand cmd = cnn.CreateCommand();
cmd.Transaction = transaction;

cmd.Parameters.Add(new OleDbParameter(":var1", ds.Tables[0].Rows[i]["USERNAME"].ToString()));
cmd.Parameters.Add(new OleDbParameter(":var2","1"));
cmd.CommandText = "UPDATE JCOLEMAN.IBI_TEST SET FLAG=:var2 WHERE USERNAME=:var1";
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
transaction.Commit();
cnn.Close();

edited Apr 4, 2016 at 19:57

answered Apr 4, 2016 at 19:45

Henrique

6104 silver badges18 bronze badges

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

5 Comments

Sherman Over a year ago

Do you really close the connection before committing the transaction?

Darw1n34 Over a year ago

I had to move the Commit() statement above the cnn.Close(); as @Henrique stated to not have any break, however the change was not committed. I'm a step closer, I will keep looking and see what else there is. Feel free to keep posing until I accept a full answer.

Henrique Over a year ago

I'm sorry, you must commit before close connection.

Darw1n34 Over a year ago

@Henrique this worked but sadly didn't commit the changes so I am headed back to the drawing board. Thanks for your help!

Henrique Over a year ago

@Darw1n34, sorry, i've googled and I found nothing about this, All of them say that all drivers have by default autocommit. None of the comments in your question worked?

Darw1n34 · Accepted Answer · 2016-04-05 14:43:05Z

The issue wasn't with the commit at all, it was with the use of the (:var2,1) line below:

cmd.Parameters.Add(new OleDbParameter(":var2","1"));

I removed it, replaced the variable with the hard coded flag '1' and it worked like a charm!

For future reference, the code below now works in place.

                cnn.Open();
                OleDbTransaction transaction = cnn.BeginTransaction();
                OleDbCommand cmd = cnn.CreateCommand();
                cmd.Transaction = transaction;

                cmd.Parameters.Add(new OleDbParameter(":var1", ds.Tables[0].Rows[i]["USERNAME"].ToString()));
                cmd.CommandText = "UPDATE JCOLEMAN.IBI_TEST SET FLAG=1 WHERE USERNAME=:var1";
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                transaction.Commit();
                cnn.Close();

Thank you @Henrique for pushing me in the right direction.

Collectives™ on Stack Overflow

C# SQL Command not ended properly

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related