SQL Update statement doesn't update values when adding multiple columns otherwise it does

Question

No exceptions, everything gets executed but the update doesn't happen!

Everything works ok when I have just @JIR parameter but now I added @Paragon and the update doesn't do it's job. No exception whatsoever data passed is OK...

I don't see anything wrong with this query does anyone know what could possibly be going wrong?

private static void InsertJIR(FisDnevni racun)
        {
            using (OleDbConnection con = new OleDbConnection(RegistarBlagajna.Modul.VezaNaBazu.ConnectionString))
            {
                try
                {
                    con.Open();
                    OleDbCommand cmd = new OleDbCommand(@"
                    UPDATE FisDnevni
                    SET [JIR] = @JIR, 
                    [Paragon] = @Paragon                                
                    WHERE BrojRacuna = @BrojRacuna"
                    , con);
                    cmd.Parameters.AddWithValue("@JIR", racun.JIR.Substring(0,37));
                    cmd.Parameters.AddWithValue("@BrojRacuna", racun.BrojRacuna);
                    cmd.Parameters.AddWithValue("@Paragon", racun.Paragon);
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
                catch (Exception)
                {
                    throw;
                }
            }
        }

Try using `Add` instead of `AddWithValue`. Also, try to add the parameters in the same order as they appear in the query. (this shouldn't matter but can't hurt). Also, `OleDbCommand` is also an `IDisposable`, you should use it inside a `using` statement. One last thing - why are you using try...catch when all you do in your catch is `throw`? — Zohar Peled, May 18 '17 at 09:17
Well, you're using different order when declaring parameters, try place `@Paragon` in second order instead. — Tetsuya Yamamoto, May 18 '17 at 09:18
@TetsuyYamamoto hmm I think that was it lol, you can post it as answer if you want me to accept it — solujic, May 18 '17 at 09:25
@Zohar well damn I didn't know I had to write seperate using for Command too, write and answer if you want the me to accept it or do you want me to answer myself? xD... — solujic, May 18 '17 at 09:27
So I understand the problem is solved then. I'll put together an answer later on. If I forget to do it within the next 24 hours, feel free to answer your own question. — Zohar Peled, May 18 '17 at 09:36
Possible duplicate of [how to update a table using oledb parameters?](http://stackoverflow.com/questions/2675610/how-to-update-a-table-using-oledb-parameters) — The Bearded Llama, May 18 '17 at 09:42

score 3 · Accepted Answer · edited May 23 '17 at 12:34

This is a good old problem - ensure the query parameters in OleDbParameter are declared in proper order like this:

using (OleDbConnection con = new OleDbConnection(RegistarBlagajna.Modul.VezaNaBazu.ConnectionString))
{
    try
    {
        con.Open();
        using (OleDbCommand cmd = new OleDbCommand(@"UPDATE FisDnevni SET [JIR] = @JIR, [Paragon] = @Paragon WHERE BrojRacuna = @BrojRacuna", con)
        {
             cmd.Parameters.AddWithValue("@JIR", racun.JIR.Substring(0,37));
             // this must be the second parameter instead of third one
             cmd.Parameters.AddWithValue("@Paragon", racun.Paragon);
             cmd.Parameters.AddWithValue("@BrojRacuna", racun.BrojRacuna);
             cmd.ExecuteNonQuery();
             con.Close();
        }
    }
    catch (Exception)
    {
        throw;
    }
}

Note that OLE DB .NET Provider doesn't recognize named parameters for OleDbCommand when CommandType is set to Text, but it apparently does recognize the parameter order, hence as long as they're passed in proper order, it'll accepted as query parameter.

Related issue:

how to update a table using oledb parameters?

Thank you I overlooked this, can't believe I wrote so many querys without paying attention to this and I got this far xD — solujic, May 18 '17 at 09:43
Well, that's even better then the answer I was going to write, so +1. — Zohar Peled, May 18 '17 at 10:48

score 0 · Answer 2 · answered May 18 '17 at 09:29

0

maybe i've just never used it, but why is there an @ here?

OleDbCommand(@"

i also havent worked with C# in a while but is multi-line concatenation possible without a + or something?

thirdly why are you using [] on these column names? i don't see any special characters or spaces.

this post is sounding a lot more dikkish than i mean it to be, im not trying, just legit curious

answered May 18 '17 at 09:29

Nalaurien

258
3
11

The wonders of writing querys in C#, when I put the @ sign in front of query I don't need concatenation with +... I'm using [ ] for nothing in perticular but aesthetics :D and to distinguish the column names (even though [] helps if you use column name similar to system name for example "time")... – solujic May 18 '17 at 09:41

SQL Update statement doesn't update values when adding multiple columns otherwise it does

2 Answers2