Oracle Update Query From a C# interface

Question

I'm having an issue with an update query in C#. It's odd to me that I'm having an issue with update, but select, delete, and insert work.

  public void updateTeacherInfo(string SSN, string Classroom, string salary, string tenured, string phone)
    {

        OracleConnection conn = new OracleConnection("myconnectionstring;");

        conn.Open();
        OracleCommand cmd = new OracleCommand("Update Teachers Set classroom_number = :TRM, Salary = :TSALARY, Tenured = :TTENURE, Phone_numer = :TPHONE Where SSN = :TSSN", conn);
        OracleCommand commit = new OracleCommand("COMMIT", conn);
        cmd.Parameters.Add(new OracleParameter(":TSSN", SSN));
        cmd.Parameters.Add(new OracleParameter(":TRM", Classroom));
        cmd.Parameters.Add(new OracleParameter(":TSALARY", salary));
        cmd.Parameters.Add(new OracleParameter(":TTENURE", tenured));
        cmd.Parameters.Add(new OracleParameter(":TPHONE", phone));


       int i = cmd.ExecuteNonQuery();
       int k = commit.ExecuteNonQuery();

       MessageBox.Show(i + " rows affected");
       MessageBox.Show(k + " rows affected");


        conn.Close();

    }

Edit* the rest of the method to clear things up, and also, it is not throwing any errors, but does not update the database.

What is setting the values of the parameters your are using? — Brian
– Brian, Commented Apr 16, 2013 at 17:02
Exactly how is it not working: error message? Nothing updated? — Ed Gibbs
– Ed Gibbs, Commented Apr 16, 2013 at 17:10
My C# interface, this is a method being called from form1, the variables are correct because I've previously had it giving me a message box listing all of the parameters. — timrate
– timrate, Commented Apr 16, 2013 at 17:10
Sorry, nothing is updated, but no error message what so ever. — timrate
– timrate, Commented Apr 16, 2013 at 17:11
A long shot: what is your SSN data type in C#, and what is its data type in Oracle? Also, does the SSN in question have a leading zero? I've had trouble in the past dealing with the C# type being numeric (int, long, etc.) and the Oracle type being string (char, varchar2, etc.), or the other way around, and the trouble seems to intensify when the SSN has a leading zero or two. — Ed Gibbs
– Ed Gibbs, Commented Apr 16, 2013 at 17:42

Rohit Galgali · Accepted Answer · 2013-10-23 09:04:59Z

1

Put the Parameters.Add in proper sequence. In your update query

"Update Teachers Set classroom_number = :TRM, Salary = :TSALARY, Tenured = :TTENURE, Phone_numer = :TPHONE Where SSN = :TSSN"

:TRM is occuring first and likewise. So keep the Parameters.Add also in same sequence. It will work. The sequence will be:

cmd.Parameters.Add(new OracleParameter(":TRM", Classroom));
cmd.Parameters.Add(new OracleParameter(":TSALARY", salary)); 
cmd.Parameters.Add(new OracleParameter(":TTENURE", tenured));
cmd.Parameters.Add(new OracleParameter(":TPHONE", phone));
cmd.Parameters.Add(new OracleParameter(":TSSN", SSN));

answered Oct 23, 2013 at 9:04

Rohit Galgali

214 bronze badges

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

Collectives™ on Stack Overflow

Oracle Update Query From a C# interface

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related