CODEWITHSUNDEEP
c#oledbconnectionoledbcommand
Darw1n34
Darw1n34

Reputation: 332

C# SQL Command not ended properly

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;";

Upvotes: 0

Views: 652

Answers (2)

Darw1n34
Darw1n34

Reputation: 332

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.

Upvotes: 0

Henrique
Henrique

Reputation: 610

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();

Upvotes: 1

Related Questions