Reputation: 793
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.
Upvotes: 0
Views: 2374
Reputation: 793
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();
}
Upvotes: 5
Reputation: 32729
your command text should be
command.CommandText = "UPDATE people SET months = :months WHERE number = :number";
Note the spaces i have added
Upvotes: 0
Reputation: 17600
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.
Upvotes: 3