Update statement working in SQL Developer but not in C#

Question

I have a small update query which works in SQL Developer.

UPDATE people
SET months = 8
WHERE number = 599

Fairly straight forward. And it works - this also works in C#. The problem is the moment I want to use Parameters (which works on number but not on months) it will stop working.

I have this code in C#:

 using (OracleConnection con = new OracleConnection(connectionString))
        {
            con.Open();
            OracleCommand command = con.CreateCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = "UPDATE people " +
                              "SET months = :months " +
                              "WHERE number = :number";
            command.Parameters.Add(":number", OracleDbType.Int32).Value = number;
            command.Parameters.Add(":months", OracleDbType.Int32).Value = months;

            command.ExecuteNonQuery();
        }

They are both of type Number in oracle, and I've tried changing the OracleDbType to Decimal, and pretty much everything without success. The odd thing is, that the :number parameters works, but months doesn't get updated (it won't crash, it just doesn't update). However if i change the :months parameter, to a static value like 7 - it will work.

Did you watch CommandText during the debug? What is the value of the months? — Farhad Jabiyev, Aug 09 '13 at 06:27
"UPDATE people SET months = :months WHERE number = :number" That's what the CommandText is. Looks alright i guess? If i were to changed months to a 7 - it'll work — Nicholas, Aug 09 '13 at 06:31

score 5 · Accepted Answer · answered Aug 09 '13 at 06:39

Alright, i found out why this wasn't working, it wasn't because of the colons (you can add colons in parameters without it being a problem):

command.Parameters.Add(":months", OracleDbType.Int32).Value = months;

The problem was that i was adding the parameters in a different order than I was using them.

So if in your SQL statement you are adding parameters in a specific order, you should follow that order when you add your OracleCommand.Parameters, like so:

using (OracleConnection con = new OracleConnection(connectionString))
    {
        con.Open();
        OracleCommand command = con.CreateCommand();
        command.CommandType = CommandType.Text;
        command.CommandText = "UPDATE people " +
                          "SET months = :months " +
                          "WHERE number = :number";
        command.Parameters.Add(":months", OracleDbType.Int32).Value = months;
        command.Parameters.Add(":number", OracleDbType.Int32).Value = number;

        command.ExecuteNonQuery();
    }

You can avoid this by setting command.BindByName = true; See: [C# parameterized queries for Oracle - serious & dangerous bug](http://stackoverflow.com/questions/3876856/c-sharp-parameterized-queries-for-oracle-serious-dangerous-bug) — Heslacher, Aug 09 '13 at 07:18
If I could upvote this more than once I'd do it a hundred times. Fighting this for hours. Thank you! — Michael, Jan 31 '22 at 00:45

score 3 · Answer 2 · answered Aug 09 '13 at 06:31

3

You should add parameters without leading ':' (look here), try this:

using (OracleConnection con = new OracleConnection(connectionString))
{
    con.Open();
    OracleCommand command = con.CreateCommand();
    command.CommandType = CommandType.Text;
    command.CommandText = "UPDATE people" +
                      "SET months = :months " +
                      "WHERE number = :number";
    command.Parameters.Add("number", OracleDbType.Int32).Value = number;
    command.Parameters.Add("months", OracleDbType.Int32).Value = months;

    command.ExecuteNonQuery();
}

Also you are missing space after :months in query.

answered Aug 09 '13 at 06:31

gzaxx

17,312
2
36
54

I found the problem, though this was not the solution. I'll post my own answer. Thanks though. The space was just a typo on stackoverflow, had spaces in my code. – Nicholas Aug 09 '13 at 06:35
@balls: In the future, consider posting code that actually resembles the one you are using. Otherwise people are unable to help you. – Daniel Hilgarth Aug 09 '13 at 06:39
This was exactly how my code is, with the one difference of the spaces. Thanks though. – Nicholas Aug 09 '13 at 06:40

score 0 · Answer 3 · answered Aug 09 '13 at 06:33

0

your command text should be

 command.CommandText = "UPDATE people SET months = :months WHERE number = :number";

Note the spaces i have added

answered Aug 09 '13 at 06:33

Ehsan

31,833
6
56
65

Update statement working in SQL Developer but not in C#

3 Answers3