CODEWITHSUNDEEP
c#mysql
Armkreuz
Armkreuz

Reputation: 149

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

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.

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!

Upvotes: 2

Views: 2100

Answers (3)

Geoduck
Geoduck

Reputation: 9005

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.

Upvotes: 0

Phil
Phil

Reputation: 1973

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

Upvotes: 0

Henry Roeland
Henry Roeland

Reputation: 492

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.

Upvotes: 1

Related Questions