Replacing a value with NULL in a table in SQL Server using vb.net

Question

My table will contain the following values. h1,h2,h3 are varchar fields with size 1

    **Register  Date        Year    Batch   h1      h2      h3**    
    1138M0321   02-08-2013  3       1       A       A       NULL
    1138M0323   02-08-2013  3       1       P       P       NULL
    1138M0324   02-08-2013  3       1       P       P       NULL    
    1138M0325   02-08-2013  3       1       P       P       NULL

I need to update one of these fields*(h1/h2/h3)* with NULL. But I can only add "" and not actually NULL How can I update the table with NULL?

Why can you only add ""? What is the rule, which rows and fields do you want to set to NULL? — Tim Schmelter
– Tim Schmelter, Commented Aug 2, 2013 at 7:50
depending on the condition I need to update H1 or H2 or H3. I can't make the value back to NULL which is the field's starting state when it doesn't hold any value — Random User
– Random User, Commented Aug 2, 2013 at 7:55
Depending on which condition, how should we show you the correct sql if we don't know the rule? What role plays the date field? Why can't you set the value back(?) to NULL? What have you tried, what was the result, what is the desired result? — Tim Schmelter
– Tim Schmelter, Commented Aug 2, 2013 at 7:57
Here is the rule.. WHERE year=" & year & " AND batch=" & batch & "" AND date= '02-08-2013' I tried DBNull.Value but still it doesn't place NULL — Random User
– Random User, Commented Aug 2, 2013 at 8:00

Antonio · Accepted Answer · 2013-08-02 08:12:03Z

4

The NULL value must be SQL, not a .NET value, so instead of trying things like:

"... SET h1 = " & NULL & " ... "

Simply use this in the query:

"... SET h1 = NULL ... "

Note that:

Null values cannot be used for information that is required to distinguish one row in a table from another row in a table, such as primary keys.

See MSDN Documentation.

edited Aug 2, 2013 at 8:12

answered Aug 2, 2013 at 8:05

Antonio

9,89215 gold badges63 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SteveB · Accepted Answer · 2013-08-02 08:02:21Z

2

Why use VB Nulls at all ...

UPDATE student_attendance_table SET h1 = NULL WHERE...

answered Aug 2, 2013 at 8:02

SteveB

1,5141 gold badge13 silver badges22 bronze badges

Comments

Farhan Mukadam · Accepted Answer · 2013-08-02 08:49:51Z

1

UPDATE student_attendance_table SET h3 = 'NULL' WHERE...

This will make your table look like this:

**Register  Date        Year    Batch   h1      h2      h3**    
    1138M0321   02-08-2013  3       1       A       A       'NULL'
    1138M0323   02-08-2013  3       1       P       P       'NULL'
    1138M0324   02-08-2013  3       1       P       P       'NULL'
    1138M0325   02-08-2013  3       1       P       P       'NULL'

And when you want to display the values of column h3. You can trim the single quotes and display NULL.

answered Aug 2, 2013 at 8:49

Farhan Mukadam

47010 silver badges25 bronze badges

Comments

Giannis Paraskevopoulos · Accepted Answer · 2013-08-02 07:59:21Z

0

If you do not have a NOT NULL constraint

"UPDATE student_attendance_table SET h1 = " & System.DBNull.Value & " WHERE...

edited Aug 2, 2013 at 7:59

answered Aug 2, 2013 at 7:50

Giannis Paraskevopoulos

18.5k2 gold badges52 silver badges72 bronze badges

2 Comments

Random User Over a year ago

How can I do this in vb.net there is no NULL when I try like this "UPDATE student_attendance_table SET h1 = " & NULL & " WHERE... there is only Nullable and DBNull If I use DBNull it's showing error

Giannis Paraskevopoulos Over a year ago

Can't you use System.DBNull.Value?

Twinkle · Accepted Answer · 2013-08-02 08:09:39Z

0

In case you want to stick to using NULL then it must be included within statement itself without any parameter, use like the classical way:

"UPDATE student_attendance_table SET h1 = NULL WHERE...

answered Aug 2, 2013 at 8:09

Twinkle

1165 bronze badges

Comments

VB.NET LEARNER · Accepted Answer · 2013-08-02 08:40:26Z

0

"Update Table Set Column = (NULL As Column Type) where Condition"

answered Aug 2, 2013 at 8:40

VB.NET LEARNER

7115 silver badges11 bronze badges

Collectives™ on Stack Overflow

Replacing a value with NULL in a table in SQL Server using vb.net

6 Answers 6

Comments

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related