CODEWITHSUNDEEP
c#sqloledb
BOOnZ
BOOnZ

Reputation: 848

SQL UPDATE statement not working

I am calling a static function to replace a value in .accdb file; to find a value of oldOA, then replace it with newOA.

    public static bool updateOA(string oldOA, string newOA)
    {
        ...
        command.Parameters.AddWithValue("@oldOA", oldOA);
        command.Parameters.AddWithValue("@newOA", newOA);

        command.CommandText = "SELECT [OA_ID] FROM [Operating_Authority_Table] WHERE [OA_Name] = [@oldOA]";
        long value = long.Parse(command.ExecuteScalar().ToString());
        command.Parameters.AddWithValue("@OA_ID", value);

        command.CommandText = "UPDATE [Operating_Authority_Table] SET [OA_Name] = [@newOA] WHERE [OA_ID] = " + value;

        try
        {
            command.ExecuteNonQuery();
            message.show(1, "", "true");
            
            return true;
        }
        catch
        {
            message.show(1, "", "false");
            return false;
        }
    }

Initially i simply used:

    command.CommandText = "UPDATE [OA_Table] SET [OA_Name] = [@newOA] WHERE [OA_Name] = [@oldOA]";

But i was told that I should get the OA_ID, which is the PK and update it from there, there resulting in the above code, but although the function still returns true, the value in the database still isn't updated..

EDIT

command.CommandText = "UPDATE [Operating_Authority_Table] SET [OA_Name] = @newOA WHERE [OA_Name] = @oldOA";

I updated to this statement already, though it still returns true, the value does not get updated. Am i making any syntax mistakes?

Upvotes: 1

Views: 1135

Answers (2)

ashutosh raina
ashutosh raina

Reputation: 9314

update Operating_Authority_Table set OA_Name = @newOA where OA_ID = (select OA_ID from Operating_Authority_Table where OA_Name = @oldOA)

My guess is this can solve your problem

Upvotes: 1

p.campbell
p.campbell

Reputation: 100657

What happens when you remove the square brackets from the parameters?

Perhaps include your value as a parameter as well?

UPDATE [Operating_Authority_Table] 
SET [OA_Name] = @newOA 
WHERE [OA_ID] = @myValue;

command.Parameters.AddWithValue("@newOA", newOA);
command.Parameters.AddWithValue("@myValue", value);

Upvotes: 0

Related Questions