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
– Zohar Peled, Commented May 18, 2017 at 9:17
Well, you're using different order when declaring parameters, try place @Paragon in second order instead. — Tetsuya Yamamoto
– Tetsuya Yamamoto, Commented May 18, 2017 at 9:18
@TetsuyYamamoto hmm I think that was it lol, you can post it as answer if you want me to accept it — solujic
– solujic, Commented May 18, 2017 at 9:25
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
– Zohar Peled, Commented May 18, 2017 at 9:36

Community · Accepted Answer · 2017-05-23 12:34:39Z

3

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?

edited May 23, 2017 at 12:34

CommunityBot

11 silver badge

answered May 18, 2017 at 9:38

Tetsuya Yamamoto

25.1k8 gold badges43 silver badges63 bronze badges

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

2 Comments

solujic Over a year ago

Thank you I overlooked this, can't believe I wrote so many querys without paying attention to this and I got this far xD

Zohar Peled Over a year ago

Well, that's even better then the answer I was going to write, so +1.

Nalaurien · Accepted Answer · 2017-05-18 09:29:50Z

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, 2017 at 9:29

Nalaurien

2583 silver badges11 bronze badges

1 Comment

solujic Over a year ago

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")...

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related