MySql Says Column can't be null for a column that is not null! [using named parameters]

Question

I am trying to execute an INSERT INTO query using .Net through the MySql/.NEt connector. The query makes uses of Parameters. It is rather simple:

INSERT INTO post (
ID, content, post_url, blogID, title, addedOn, 
updatedDate, commentsFeedURL, active, viewCount, 
commentCount, languageID, authorName, postDate, 
posRating, negRating, adult) 
VALUES(
@ID, @content, @post_url, @blogID, @title, @addedOn, 
@updatedDate, @commentsFeedURL, @active, @viewCount, 
@commentCount, @languageID, @authorName, @postDate, 
@posRating, @negRating, @adult)

When I run it (with all parameters duly assigned) I get an error

"Column 'post_url' cannot be null"

But it is not Null. This is the value in the parameter post_url

http://abcd.wordpress.com/2007/08/13/%e0%a4%a6%e0%a5%8b-%e0%a4%ae%e0%a4%bf%e0%a4%a8%e0%a4%9f-%e0%a4%95%e0%a4%be-%e0%a4%a7%e0%a5%8d%e0%a4%af%e0%a4%be%e0%a4%a8/

And this is the code I am using to assign the parameters to the SQL Query

cmd.Parameters.AddWithValue("post_url", postOld.URL);

What could be the reason that I am getting this behavior?

Answer 1

Just before you call the Execute method, can you check then to see what value is in each parameter? It's very easy to accidentally do something that messes up.

Answer 2

Do you have an insert trigger on that table? It could be that the trigger is trying to insert in to another table that has a post_url column that cannot be null.

Answer 3

Try moving post_url before content in the parameter and value lists to rule out the possibility that the driver passed the parameters to MySQL badly due to a complex value in @content.

Answer 4

Not being snarky here, but this is why test cases are a good thing. Write a unit test to validate that passing a non-null posturl will work, and passing a null one will get this error form the database.

Then write an integration test (or a mock) to prove that postOld.URL is non-null.

Answer 5

Shouldn't the line look like this:

cmd.Parameters.AddWithValue("@post_url", postOld.URL);

or can you omit the @ symbol?

Answer 6

Okay folks I finally found the right answer.

The problem was simply that in MySQL queries parameters are marked by '?' not '@'. Unfortunately many queries seemed to run fine (they weren't) using '@' so one finds this out later when there's trouble.

Thank you for all your answers. I re-wrote my query like this:

INSERT INTO post (ID, content, post_url, blogID, title, addedOn, updatedDate, commentsFeedURL, active, viewCount, commentCount, languageID, authorName, postDate, posRating, negRating, adult)" +
                            " VALUES(?ID, ?content, ?post_url, ?blogID, ?title, ?addedOn, ?updatedDate, ?commentsFeedURL, ?active, ?viewCount, ?commentCount, ?languageID, ?authorName, ?postDate, ?posRating, ?negRating, ?adult)

and it worked.