C# + MySQL + ExecuteNonQuery returns '1' on UPDATE but changes not in DB

Question

i have a problem where i can't apparently find a solution even after hours of debugging.

I have a simple SQL command where i want to update a row with some value(the value is a text of about ~5mb), after executing the SQL Command, the 'ExecuteNonQuery()' on the C# code side, returns '1' and no exception but the changes are sometime reflected in the Database row and sometime not.

I tried debugging to see if the values that will replace the old one are correct and they are. I am using MySQL 5.5.11.

Could it be a MySQL setting or something?

Losing my mind on this problem, if you have any idea it would be greatly appreciated.

EDIT, include code:

The code is as simple as this:

cmd.CommandText = "UPDATE user SET data = @data WHERE id = @id";

then i add params to the DBCommand object for the SQL Query. The 'data' is about ~5mb big.

this command always returns '1' even if the changes are not reflected in the database(sometime it works, sometime it doesnt):

cmd.ExecuteNonQuery();

Thanks in advance!

Post some code, so we can: 1) see exactly what you are doing, and how you are doing it, and 2) so we could perhaps reproduce the same issue. — Arran
– Arran, Commented Jun 10, 2012 at 18:32
I had a similar problem and I've removed the column and recreated it. Don't askme why, but it worked. The MySql drivers have a few problems with MS products. — Ricardo Souza
– Ricardo Souza, Commented Jun 10, 2012 at 18:34

Henry Roeland · Accepted Answer · 2012-06-10 21:43:14Z

1

I don't know if the mysql provider uses autocommit? If not then you have to call commit on the Transaction object you can get with BeginTransaction on the connection object.

answered Jun 10, 2012 at 21:43

Henry Roeland

4925 silver badges20 bronze badges

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

1 Comment

Armkreuz Over a year ago

Thanks for the reply, i dont know but i am not using transaction object, so i don't know why sometime it works and sometime not?

Ferrmolina · Accepted Answer · 2015-05-12 22:27:13Z

0

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx

The Microsoft .NET Framework Data Provider for SQL Server does not support the question mark (?) placeholder for passing parameters to a SQL Statement or a stored procedure called by a command of CommandType.Text. In this case, named parameters must be used.

For example:

SELECT * FROM Customers WHERE CustomerID = @CustomerID

Edit: Just noticed this was MySQL, and while i can't find anything simliar about it quickly, i would suggest you use named parameters anyways

edited May 12, 2015 at 22:27

Ferrmolina

2,7692 gold badges33 silver badges48 bronze badges

answered Jun 10, 2012 at 18:45

Phil

1,9732 gold badges29 silver badges61 bronze badges

1 Comment

Armkreuz Over a year ago

Thanks for the reply, but i wrote the code example from the top of my head, actually the code use @param, i'll edit my OP.

Geoduck · Accepted Answer · 2015-09-21 00:14:33Z

0

I had this problem and it was related to autocommit.

Problem: earlier in the app lifespan autocommit was set to 0 by another process in the app, and failed. The connection with autocommit turned off is then reused by the connection pool.

That will lead to this type of error at unpredictable times.

answered Sep 21, 2015 at 0:14

Geoduck

9,0212 gold badges29 silver badges27 bronze badges

1 Comment

Geoduck Over a year ago

note that it will happen even if not using transactions. The connection has been set so nothing is committed without a COMMIT.

Collectives™ on Stack Overflow

C# + MySQL + ExecuteNonQuery returns '1' on UPDATE but changes not in DB

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related