CODEWITHSUNDEEP
c#sqlmysqldatabase
Cyril Gupta
Cyril Gupta

Reputation: 13723

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

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?

Upvotes: 4

Views: 3294

Answers (6)

Cyril Gupta
Cyril Gupta

Reputation: 13723

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.

Upvotes: 8

Skyler
Skyler

Reputation: 341

Shouldn't the line look like this:

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

or can you omit the @ symbol?

Upvotes: 0

tpdi
tpdi

Reputation: 35141

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.

Upvotes: 0

BlueMonkMN
BlueMonkMN

Reputation: 25601

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.

Upvotes: 0

George Mastros
George Mastros

Reputation: 24498

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.

Upvotes: 0

Rob Farley
Rob Farley

Reputation: 15849

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.

Upvotes: 0

Related Questions