-4

I'm trying to insert a NULL value to a variable of type string. Please let me know the possible ways to do it. I googled about this problem and found that DBNul.value can be assigned to the particular variable that has to be inserted as NULL. When i tried it errored out saying DB.Null cannot be converted to type system.string.

help-info.de
  • 6,695
  • 16
  • 39
  • 41
Kals
  • 9
  • 1
  • 1
  • 3
  • It would help to see what you have. It's not clear here whether you're trying to build an SQL command, or what you want the results to look like. There's clearly something else going on here. Otherwise, why not just `myStringVar = null;`? – Joel Coehoorn Oct 28 '16 at 17:53
  • I m trying to insert a null record to a column by SQL. I have not tried myStringVar = null yet. – Kals Oct 28 '16 at 18:04
  • Please find the below snippet... i m using SqlCommand....i m reading the value of flag from a file and trying to insert into database.... i m unable to paste the whole code here...but below is the actual part where i m facing issue Dim flag As String if flag = "" query = insert into table (id, name, state, flag) values (1, xx, NY, NULL) – Kals Oct 28 '16 at 18:38
  • Please put your question information into your question and delete the comment. You can put UPDATE above it to show you have added it in addition to your original question. – Ccorock Oct 28 '16 at 19:05
  • **NEVER** use string concatenation to put values into SQL statements! That's a good way to find out a year later that your app was hacked six months ago. – Joel Coehoorn Oct 28 '16 at 21:15

3 Answers3

2

You can assign a NULL value to a string in by setting it to nothing

dim MyString as string
MyString = nothing

A VBNull.Value is generally used to apply a null value when using a database insertion, or to verify if the recieved value is null.

EDIT: As you made clear you want to add a null value to a database, this can be achieved by setting the null value directly in the command object:

Dim Command As New OleDb.OleDbCommand 'Use the proper database command
Command.CommandText = "INSERT INTO Table (Column1, Column2) Values (@Value1, @Value2)"
Command.Parameters.AddWithValue("@Value1", If(string.IsNullOrEmpty(MyString1), DBNull.Value, MyString1))
Command.Parameters.AddWithValue("@Value2", If(string.IsNullOrEmpty(MyString2), DBNull.Value, MyString2))
Quima
  • 894
  • 11
  • 21
  • This will insert a blank space into the database not a null value as the OP is requesting. – Ccorock Oct 28 '16 at 17:57
  • I could have probably misunderstood his question, but i think he is trying to apply a value to a variable, not to a database, isn't it? – Quima Oct 28 '16 at 17:58
  • If i use My string = nothing....just blank box appears against that column in Database which would affect if i query for NULL values for the column – Kals Oct 28 '16 at 17:59
  • AugustoQ...i m trying to insert into database – Kals Oct 28 '16 at 18:00
  • @Kals Edited the answer for a SQL insertion, please, check it out. – Quima Oct 28 '16 at 18:23
1

Using Linq to SQL or the similar Entity Framework, you can store DBNull using Nothing

I have a simple table with...

CREATE TABLE [dbo].[Table_1](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [TestID] [bigint] NOT NULL,
    [Description] [nchar](10) NULL,

and using this code...

Sub Main()
    Using dc As New DataClasses1DataContext
        Dim testNothing = New Table_1
        testNothing.Description = Nothing
        testNothing.TestID = 1
        dc.Table_1s.InsertOnSubmit(testNothing)
        Dim testEmpty = New Table_1
        testEmpty.Description = ""
        testEmpty.TestID = 2
        dc.Table_1s.InsertOnSubmit(testEmpty)
        dc.SubmitChanges()
    End Using
End Sub

the contents of the table is...

ID    TestIDDescription
1      1        NULL         
2      2                          

djv
  • 15,168
  • 7
  • 48
  • 72
0

You want to use Nothing.

myStringVar = Nothing

WilRogJr
  • 26
  • 5