Can I set a database value to null in VB using a StringBuilder?

Question

I have an application that when I run an update statement, on a given condition a value must be set to null.

I am using StringBuilder to create my sql string and I can't a value to set to null. Given that I've used the DBNull.Value and it's inserting a blank string ("") into the database instead of null can you insert a null value into a database using a stringbuilder and an execute query statement?

Here is what I am using. I have tried just .Append(DBNull.Value) but a query won't execute like that because it doesn't put anything there so the query ends up like

... , [lastRunProcessGUID] = where ... (using Nothing results in the same thing)

With psSql
    .Append("update [connection string] ")
    .Append("set [Status] = ")
    .Append("'" & newStatus & "' ")

    If newStatus.Equals("0") Then
        .Append(", [lastRunProcessGUID] = ")
        .Append("'" & DBNull.Value & "'")
    End If

    .Append(" where [workItemID] = ")
    .Append("'" & workItemID & "' AND")
    .Append("' [StepLogID] = ")
    .Append("'" & stepLogID & "'")
End With

I could be dead wrong (hence leaving it as a comment), but I think replace .Append("'" & DBNull.Value & "'") with .Append("NULL") - no text delimiters - Just explicitly stating the value NULL. — John Bustos
– John Bustos, Commented Apr 17, 2014 at 20:12
NO, you can't! Well, you can, but only if you don't care if your program gets hacked. And for that matter, your existing non-null parameters are still going to get you hacked, probably sooner rather than later. — Joel Coehoorn
– Joel Coehoorn, Commented Apr 17, 2014 at 20:14
Be VERY careful of SQL injection, when building dynamic SQL like this. — dodexahedron
– dodexahedron, Commented Apr 17, 2014 at 20:15
@JohnBustos Where you think variables like newStatus get populated from? All data comes from the user at some point. — Joel Coehoorn
– Joel Coehoorn, Commented Apr 17, 2014 at 20:16
the application is internal for the dev team so i'm not too worried about sql injection. but thanks for the concern. — B-M
– B-M, Commented Apr 17, 2014 at 20:23

T McKeown · Accepted Answer · 2014-04-17 20:19:15Z

2

Because you are building the SQL statement as a string just write the syntax as you would using Mgmt-Studio

if newStatus.Equals("0") Then
    .Append(", [lastRunProcessGUID] = NULL")
End If

DbNull.Value is more useful when performing logic when reading a value from the DB.

if ( rdr["col"] == DbNull.Value ) { ... }

I would change your code to use Parameterized SQL to prevent SQL Injection or use stored procedures instead of dynamic SQL generated from the application, if possible or appropriate.

edited Apr 17, 2014 at 20:19

answered Apr 17, 2014 at 20:12

T McKeown

12.9k1 gold badge27 silver badges35 bronze badges

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

Collectives™ on Stack Overflow

Can I set a database value to null in VB using a StringBuilder?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related