update setting null value

Question

0.00/5 (No votes)

See more:

On my site some users give their fake address. I want to set those addresses to null value keeping rest of data. my code for this change is simple; when I identify that an address is fake, I use the following code
SqlCommand clear1 = new SqlCommand("UPDATE PersonalData SET Address='" + DBNull.Value + "' WHERE UserName='"+ username + "'",connection );

but execution of this NonQuery does not return null value but gives a blank in the address field.

It is necessary for me to set it to null value because my grid view is set to ignore null values of address and I do not want this kind of data to be displayed on my site.

Apparently I am making mistake in update command to set null value.

Kindly help me to update the selected fields to null value. Any other option by which I can make sure that these selected addresses are not displayed in the grid view, will be equally good.

Many thanks to all for their kind help.

Posted 19-Jan-14 1:09am

Member 10235977

Add a Solution

Comments

Sampath Lokuge 19-Jan-14 7:36am

Why can't you remove that address column from your grid ?

Member 10235977 19-Jan-14 19:44pm

I cannot remove address column because it has to be set to null only when address is fake, not in all cases. Address is an important parameter to make sure that user is genuine. Only fake addresses have to be set to null.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2014-01-19T01:36:00

Solution 1

Please, don't do that!
Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead. This will not only help save your DB, it will also cure your problem...

C#

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand cmd = new SqlCommand("UPDATE myTable SET PersonalData=@PD WHERE UserName=@UN", con))
        {
        cmd.Parameters.AddWithValue("@UN", username);
        cmd.Parameters.AddWithValue("@PD", DBNull.Value);
        cmd.ExecuteNonQuery();
        }
    }

Posted 19-Jan-14 1:36am

OriginalGriff

Comments

Member 10235977 24-Jan-14 8:08am

Many thanks OriginalGriff
It has solved not only this problem but many other problems related to update including how to handle apostrophe sign.
Just for the sake of completion, when there are more than one fields to update, I am using the command
"UPDATE myTable SET Fld1=@Fld1, Fld2=@Fld2, Fld3=@Fld2 WHERE UserName=@UN", con
Is there any other way to group when there are more than one fields to update.

OriginalGriff 24-Jan-14 9:44am

If you have a large number of rows to update, then you could use an SqlDataAdapter. It has an UpdateCommand property, and can take a DataTable as the row source.
See here: http://msdn.microsoft.com/en-us/library/kbbwt18a(v=vs.80).aspx