How to set NCHAR to null with an update statement (C# sqlcommand)

Question

I'm wondering how to set an nchar column to null with an update statement. I found this: NULL value for int in Update statement

which says you have to cast to set non-int fields to NULL.

So I tried the following:

commandText = 
  @"UPDATE Brukere 
      SET Engangskode = CAST(NULL AS NCHAR) 
    WHERE Navn = @navn AND Mobilnr = @mobilnr";

However, when I execute this it still won't update the column to NULL. Am I casting wrong, or is it something else? Any help would be appreciated :)

Longer code snip if needed: http://pastebin.com/8auKuk6Q

No need for any casts - just use `UPDATE Brukere SET Engangskode = NULL WHERE Navn = @navn AND Mobilnr = @mobilnr`. That should work **just fine** if the column actually allows `NULL` ... — marc_s, Sep 04 '15 at 15:25
@marc_s See reply to Mihai below. Also not sure why you edited that. I get that you prefer that formatting, but it's not the format of my original code. — user2875994, Sep 04 '15 at 15:27
The answer that says _by default in SQL Server, NULL is an INT_ is incorrect. — juharr, Sep 04 '15 at 15:29
And what happens if you use `= NULL` directly? Do you get an error - if so **what** error?? — marc_s, Sep 04 '15 at 15:29
@juharr Thanks for the heads up. Weird that it's lying around as the accepted answer with loads of upvotes, then. But I've tried without casting, as mentioned in my comment bellow to mihai. — user2875994, Sep 04 '15 at 15:30
From your Pastebin code, I believe your C# code is just wrong - not the SQL .... — marc_s, Sep 04 '15 at 15:30
@marc_s No error (if you look at my original code, I've set a breakpoint in the catch block just so I always get a break if an error has occured). The table just isn't updated with the field set to NULL. — user2875994, Sep 04 '15 at 15:31
You're not setting the `command.CommandText` to the update statement. Changing the `commandText` local variable doesn't change the `command.CommandText`. — juharr, Sep 04 '15 at 15:32
@juharr Oh. Thanks a bunch! Assumed the set commandtext was by reference, not copy. Set it up as an answer and I'll accept it (should probably include that it's because it's not by reference, for future question visitors). — user2875994, Sep 04 '15 at 15:35
@user2875994 It is a reference, but you are changing the local variable to reference a new value and that doesn't change any other references. Also as marc_s says it would be better to just create a separate `SQLCommand`. — juharr, Sep 04 '15 at 15:38
Also it's generally considered best practice on SO to post your code vs posting a link to it. — juharr, Sep 04 '15 at 15:43
@juharr I'll keep that in mind. I just assumed it would be a bit "spammy", and pastebin is a pretty neutral site. — user2875994, Sep 04 '15 at 15:44
I'd say that's OK if there's a lot of code and you only post the relevant parts in the question and a link to the complete code. — juharr, Sep 04 '15 at 15:45
@juharr That's kinda what I tried to do. I assumed the rest of the code wasn't directly relevant, but I included it in case it turned out to be so you didn't have to ask me to post it (which turned out to be the case). — user2875994, Sep 04 '15 at 15:47

score 2 · Accepted Answer · answered Sep 04 '15 at 15:34

2

The problem is that you are setting the local variable commandText to the update statement instead of the command.CommandText. Change it to the following

command.CommandText = "UPDATE Brukere SET Engangskode=NULL WHERE Navn=@navn AND Mobilnr=@mobilnr";

And I think it will work with or without the casting.

answered Sep 04 '15 at 15:34

juharr

31,741
4
58
93

I simply did this after changing the string: `command.CommandText = commandText;` As mentioned above, I thought it was by reference, not by value. Thanks for your help. – user2875994 Sep 04 '15 at 15:36
Scratch the reference bit. – user2875994 Sep 04 '15 at 15:40
It is by reference. You are setting the reference of `commandText` to a new `string`. That doesn't effect any other references. And because `string` is immutable you cannot change the object that they are both referencing either. – juharr Sep 04 '15 at 15:41
Yeah I understand, read your reply above. I was under the impression that if you changed the value of a variable, you changed it in the same memory location (so all references to it would see the updated value). I guess the case is that you create a new value at a new memory location and change the reference to it, thus not updating other references. – user2875994 Sep 04 '15 at 15:45
1

Yes, strings are kinda special in that a string literal creates a new object without using the `new` keyword and a ctor. – juharr Sep 04 '15 at 15:48
Aaahhh, I see. So would my past interpretation be correct for other datatypes. Say if it was an int? – user2875994 Sep 04 '15 at 15:50
1

Well `int` is a value type, so no. You might want to read up on reference types and values types in C#. http://jonskeet.uk/csharp/references.html – juharr Sep 04 '15 at 15:53
Just a few questions to see if I understood this correctly, I hope you don't mind. 1) You can't pass a reference to a value type (hence why you say it doesn't work for int). You'd have to wrap it in a class then pass it if you wanted to pass it by reference. Correct? 2) For example: I make an object myself storing a string. This is then sent by reference to something else. If I then directly change the string in the object, would the reference sent to "something else" also point to the updated object, or a copy of it containing the old string? – user2875994 Sep 04 '15 at 16:03
It might be better if you just ask a new question rather than continuing this in the comments. – juharr Sep 04 '15 at 16:04
I guess I'll just test it then. Just thought I could get a quick answer while I had your attention, hehe. Well, have a good day. – user2875994 Sep 04 '15 at 16:19

score 2 · Answer 2 · answered Sep 04 '15 at 15:35

I would recommend NOT to "re-use" the SqlCommand in your example - create a new, specific command for the UPDATE statement, something like this:

using (SqlConnection con = new SqlConnection(connectionString))
{
    con.Open();

    string bruker = Request.Cookies["Navn"].Value;
    string mobilnr = Request.Cookies["Mobilnr"].Value;

    string commandText = "SELECT Engangskode FROM Brukere WHERE Navn=@navn AND Mobilnr=@mobilnr";

    bool correctCode = false;

    try
    {
        using (SqlCommand command = new SqlCommand(commandText, con))
        {
            .....

            if (correctCode)
            {
                // DO NOT "reuse" the previous SqlCommand - create a new, specific one!
                string updateQuery = "UPDATE Brukere SET Engangskode = NULL WHERE Navn = @navn AND Mobilnr = @mobilnr;";

                using (SqlCommand updateCmd = new SqlCommand(updateQuery, con)
                {
                    updateCmd.Parameters.Add("@navn", SqlDbType.NVarChar, 20).Value = bruker;
                    updateCmd.Parameters.Add("@mobilnr", SqlDbType.NChar, 10).Value = mobilnr;

                    updateCmd.ExecuteNonQuery();

                    Response.Redirect("Kvittering.aspx",  false);
                }
            }
        }                                    
    }
    catch( .... )
    {
        .......
    }
}

Reusing it works fine. See my reply to Juharr above :) Have an upvote, though. — user2875994, Sep 04 '15 at 15:38

score 0 · Answer 3 · answered Sep 04 '15 at 15:25

0

No need to cast. Juse set column = null. NO quotes, tho!

answered Sep 04 '15 at 15:25

Mihai Ovidiu Drăgoi

1,307
1
10
16

No casting results in the same thing, sadly. That's what I tried initially. I guess I should've mentioned. – user2875994 Sep 04 '15 at 15:26
Then either the where is filtering out all the rows or the column definition doesn't allow nulls. – Mihai Ovidiu Drăgoi Sep 04 '15 at 15:32
If you look at the larger code snippet, I'm using the exact same WHERE search earlier in the code, and there it finds the correct row. So the filtering should be correct. And the column does allow NULLs – user2875994 Sep 04 '15 at 15:33

How to set NCHAR to null with an update statement (C# sqlcommand)

3 Answers3