MySql DB update Problem

Question

4.20/5 (2 votes)

See more:

Hi. I have a little problem.

I wrote programs in mysql database when I try to enter data into a table, I get this warning. I get this error when the database data section is NULL. The problem does not return empty.

Quote:

Error: DBNull type and for the string '=' operator is not defined

my update code:

VB

Dim Table As String = ini.ReadValue("COMM", "DBTable")
Dim Data As String = "CODE_NO='" + DataGridView1.Item(0, i).Value.ToString
Dim con As New MySqlConnection
Dim Comm As New MySqlCommand

Comm = New MySqlCommand("UPDATE " + Table + " SETKAY='" + Label4.Text + "'" + ", STATUS='Waiting', DATE='" + DateTime.Now.ToString + "'" + " WHERE " + Data + "'", con)

Database Table cell status= (NULL) ( not working )

<br />
Database Table cell status= " "

( Working )

Thanks everyone. But dont understand every solution. I want to tell with your pictures.
http://tinypic.com/view.php?pic=2ljhqwk&s=8#.VFaaw01yZ9A[^]
http://tinypic.com/view.php?pic=wircd5&s=8#.VFaa3U1yZ9A[^]

Posted 2-Nov-14 5:03am

EssenceGold

Updated 2-Nov-14 9:59am

CHill60

v6

Add a Solution

Comments

PIEBALDconsult 2-Nov-14 15:12pm

You should use "Improve question" to add that to the post.

[no name] 2-Nov-14 15:34pm

It would also help if you posted the whole subroutine that executes this block of code. As I think PIEBALDconsult is going to help you with this.

Regards.

3 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

PIEBALDconsult · Answer 1 · 2014-11-02T05:28:00

Solution 1

Please don't use string concatenation to form SQL statements; use a parameterized query. Every time. And particularly in a loop.

As you've seen, when a NULL is returned via ADO.net, it will appear as DBNull.Value/
As the message says, you can't just use that value with other datatypes.
You can test for that and replace the value with something more useful; a real NULL, an empty string, whatever makes sense in your application.

Posted 2-Nov-14 5:28am

PIEBALDconsult

Comments

[no name] 2-Nov-14 12:37pm

He is not using ado so why are you using that as an example for a mySQL issue? The variations even in terms of queries are very different.

PIEBALDconsult 2-Nov-14 12:47pm

DataGridView, MySqlConnection, and MySqlConnection indicate he's using ADO.net
"ADO.NET data providers automatically assign values of DBNull when a field does not have a valid value" -- MSDN

[no name] 2-Nov-14 12:57pm

Yes, I wasn't to clear, sounds like you are explaining this for OLE provider.

MySQL has specific methods for checking null values. If you want to update your answer, I will change my vote.

CHill60 2-Nov-14 13:04pm

Correct - MySQL does have specific methods for checking null values - like IFNULL that I posted in my solution :-P

PIEBALDconsult 2-Nov-14 13:09pm

"The top-level namespace and a number of the child namespaces together form the ADO.NET architecture and ADO.NET data providers" -- http://msdn.microsoft.com/en-us/library/gg145028(v=vs.110).aspx
All members of the System.Data tree or which derive from those classes or implement the Interfaces (including the MySQL connector/net and the OleDb provider) _are_ ADO.net

CHill60 · Answer 2 · 2014-11-02T05:33:00

Solution 3

Further to solution 1, have a look at the IFNULL[^] function in MySQL (the T-SQL equivalent of ISNULL[^])
So your code snippet could be

Dim Data As String = "IFNULL(CODE_NO,'')=@Item"

Also for reference Working with parameters in mysql[^]

Posted 2-Nov-14 5:33am

CHill60

Comments

CHill60 2-Nov-14 12:56pm

To answer your queries in order:
- Solution 1 suggested using parameterized queries and pointed out the NULL problem, therefore is relevant.
- How can you tell the OP is not using ADO? MySQL has a fully-managed ADO.net driver and MySqlConnection is how you would use it
- ADO is not a database type so I'm not sure what you meant by the next statement
- Downvoting happens all the time, sometimes justified, often not. Perhaps someone thought you were being rude, or had just repeated essentially what was in Solution 1. I wouldn't know, it wasn't me that downvoted your solution (although I suspect it was you that just downvoted my perfectly reasonable post)

PIEBALDconsult 2-Nov-14 12:58pm

You don't appear to know what ADO.net is even though you use it in your own solution.

[no name] 2-Nov-14 13:17pm

Clearly I do know the difference; I've wrote a whole article as an answer on another question, what I meant regarding solution 1, as I read it and explained in PIEBALDconsult answer what I meant. Mearly misplaced wording on my part as I've been coding for over 40 hours straight.... might be time for a break.

His answer does not exemplify checking MySQL db null values, and use of HasRows would be best advised to check if there is something to update before trying to update it. It reads like an OLE explanation/ to checking null values which are different whereas MySQL does this differently to OLE provider.

(As to how i know it anyway.) I have wrote hundreds of examples on MySQL on many other websites, so I think its fair to say I know the difference, clearly by previous postings.

Sorry for the confusion in my wording though.

PIEBALDconsult 2-Nov-14 13:21pm

Yet none of that has anything to do with the question at hand.
The question deals with a DBNull in C# code, and not any particular database system.

[no name] 2-Nov-14 13:50pm

I disagree; Solution one is not clearly exemplified which led me to assume that he was referring to OLE provider. Because there is a lot wrong with this unasked question.

HasRows should be checked, before trying to update something that may not be there. Secondly, this will result in a null reference exception anyway if the update is executed on a table with no values.

The whole construction of the query string building is wrong on so many levels. See the reply I made on my own solution to you regarding the DATA variable you are referring to in your own solution.

PIEBALDconsult 2-Nov-14 13:54pm

"before trying to update something that may not be there. Secondly, this will result in a null reference exception anyway if the update is executed on a table with no values." That seems untrue in all databases I have used, it would just affect 0 rows.

[no name] 2-Nov-14 15:00pm

Again; debatable. We do not know how the OP is getting or putting data. If OP is interfacing with MySQL directly, the desired affect will be as I described.

PIEBALDconsult 2-Nov-14 15:11pm

"We do not know how the OP is getting or putting data" We do know it's "poorly". :D

Sheepings · Answer 3 · 2014-11-02T05:32:00

Solution 2

I don't mean to come off rude, but come on? A look at your code and you clearly don't see anything wrong?

A) No Parameters are being used to protect you against SQL injection and cross scripting etc. . .
B) Knowing the difference between using the basic fundamental symbols of the language you are writing in is a must. + VS &
C) Basic knowledge of MySQL is imperative to know what you are doing. In your case; you don't. . .

VB

Dim Query As String = "Update YourDB.my_table_name SET Value1=@V1, Value2=@V2 Where Value3=@Condition;" 
Parameters.AddWithValue("@V1", "Value Of 1")
Parameters.AddWithValue("@V2", "Value Of 2")
Parameters.AddWithValue("@Condition", 0)

Then Executenonquery() to initiate your action on your command.

You can see further explanation on MySQL covered in this Answer, also answered by me. I hope it helps get you started in the right direction. But you are going to have to do some research and study the basics before going further with this.

Posted 2-Nov-14 5:32am

Sheepings

Updated 2-Nov-14 5:37am

v3

Comments

PIEBALDconsult 2-Nov-14 13:12pm

While that's all true, none of it appears to relate directly to the question.

[no name] 2-Nov-14 13:28pm

lol Of course it doesn't; My solution is based entirely on speculation to address an unasked question. Op never asked any question PIEBALDconsult, and I don't see any use of where the OP is using SET to set values.

I also see that he is trying to use his DATA Variable which concludes of: "CODE_NO='" + DataGridView1.Item(0, i).Value.ToString - I highly doubt that should be part of his query: CODE_NO= and by using + he is making an addition to add it as a string.

When in fact the item he is returning is Integer, and a conversion from int to string is not needed here if the OP was using parameters or a properly concatenated query string.

PIEBALDconsult 2-Nov-14 13:42pm

"the item he is returning is Integer"
Where do you get that?!
I agree that the OP really needs the benefit of our experience to fix more than his immediate problem, and that parameters will be a big part of that.

[no name] 2-Nov-14 15:03pm

Where do i get that? lol

Don't you know what tostring does?
http://msdn.microsoft.com/en-us/library/system.object.tostring%28v=vs.110%29.aspx

If you look at what he is doing in the DATA Variable (defined as a String and not an Integer) - OP whole concept on approach is wrong and obviously the fundamentals of the basics has been skipped here, and teaching the use of parameters will seem to be the least of all problems.

We are not even seen half of the required code, as ' i ' is not even declared that i can see for the Datagridview.

PIEBALDconsult 2-Nov-14 15:09pm

Of course I know what ToString does, but his use of ToString gives you no clue as to what datatype the actual value is. As I'm sure you know, there are plenty of noobs who use ToString on strings! It could also be a GUID. While there is a better than 50% chance that it's an integer (it could also be an integer in a string), we just don't know.

[no name] 2-Nov-14 15:22pm

' there are plenty of noobs who use ToString on strings! '
That's true too, and I've seen it a hundred times.

Just to let you know but the OP seemed to have added pics which explains a little more about the table structure used. But I've been working almost 40 hours and need a dam good rest. :p

So I will leave you to comment back to him to work out a new solution.

Speak to you soon.

PIEBALDconsult 2-Nov-14 15:24pm

"Sleep well and dream of large women." (Or whatever you happen to like.)

[no name] 2-Nov-14 15:30pm

haha! I'm not that fortunate to dream of such a thing; most of my dreams are about code or drowning :)

Pathetic right?!

MySql DB update Problem

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0

MySql DB update Problem

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us