DBNull is a type and cannot be used as an expression in VB.NET

Hi all.

I have the code below that checks to see if the Notes textbox is left blank. If it is left blank then I want it to insert NULL into that table field. But I'm getting an error saying DBNULL is a type and cannot be used as an expression. How can I get it to insert NULL, currently it enters a blank ("") if the Notes textbox has no data in it. Thank you in advance.

Dim NotevalueToPutInDatabase As String

        If (String.IsNullOrEmpty(txtNotes.Text)) Then
            NotevalueToPutInDatabase = DBNull
        Else
            NotevalueToPutInDatabase = txtNotes.Text
        End If

Open in new window

printmediaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Craig WagnerSoftware ArchitectCommented:
Use DBNull.Value
0
printmediaAuthor Commented:
I tried DBNull.Value and I get the error:

Value of type 'System.DBNull' cannot be converted to 'String'.

The textbox is inserting into datatype: nvarchar (500) that allows Nulls.
0
Craig WagnerSoftware ArchitectCommented:
This has nothing to do with what is in the database or how the column is defined in the database. You are trying to assign DBNull to a .NET string variable (which I didn't notice at first).

Simply assign null (Nothing in VB.Net I believe) to the string in your property.

If (String.IsNullOrEmpty(txtNotes.Text)) Then
    NotevalueToPutInDatabase = Nothing
Else
    NotevalueToPutInDatabase = txtNotes.Text
End If

Open in new window

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

printmediaAuthor Commented:
Thanks.

I did as you suggested and it didn't put NULL but ''.

Is it ok if the table has '' instead of NULLs?
0
CodeCruiserCommented:
Can you show the code you use to insert data?
0
printmediaAuthor Commented:
Here is the code:

Dim NotevalueToPutInDatabase As String

        If (String.IsNullOrEmpty(txtNotes.Text)) Then
            NotevalueToPutInDatabase = Nothing
        Else
            NotevalueToPutInDatabase = txtNotes.Text
        End If

        Dim con As New SqlConnection
        Dim cmd As New SqlCommand

        con.ConnectionString = "Data Source=myserver;Initial Catalog=mydb;Integrated Security=True"

        con.Open()

        cmd.Connection = con

        cmd.CommandText = "INSERT INTO ItemQuote( MasterItemNumber, Description, SLXID, QuoteDate, UnitCost, QuotedCost, Notes) VALUES ('" & txtMasterItemNo.Text & "', '" & txtDescription.Text & "', '" & txtSLXID.Text & "', '" & txtQuoteDate.Text & "', " & txtUnitCost.Text & ", " & txtQuotedCost.Text & ", '" & NotevalueToPutInDatabase & "')"

        cmd.ExecuteNonQuery()

        MsgBox("Item has been submitted")

        con.Close()

Open in new window

0
Craig WagnerSoftware ArchitectCommented:
That code would explain why you're getting an empty string in the database. When you concatenate all that together you're inserting '' into the column. You really should be using parameterized queries to prevent SQL injection attacks. I'll put together a simplified example in a few minutes.
0
printmediaAuthor Commented:
Ok thank you. But I thought SQL injection attacks were only for web forms not windows forms. This is an in-house form on our network not on the web.
0
Craig WagnerSoftware ArchitectCommented:
As I said before, you should always use parameterized queries to prevent SQL Injection. If you aren't sure what that is or why it's bad, Google "sql injection" and start reading.

Your code should be rewritten similar to the following.

Dim con As New SqlConnection
Dim cmd As New SqlCommand

con.ConnectionString = "Data Source=myserver;Initial Catalog=mydb;Integrated Security=True"

con.Open()

cmd.Connection = con

cmd.CommandText = "INSERT INTO ItemQuote( MasterItemNumber, Description, SLXID, QuoteDate, UnitCost, QuotedCost, Notes)" & _
    "VALUES (@MasterItemNo, @Description, @SLXID, @QuoteDate, @UnitCost, @QuotedCost, @Notes)"

cmd.Parameters.Add("@MasterItemNo", txtMasterItemNo.Text)
cmd.Parameters.Add("@Description", txtDescripion.Text)
cmd.Parameters.Add("@SLXID", txtSLXID.Text)
cmd.Parameters.Add("@QuoteDate", txtQuoteDate.Text)
cmd.Parameters.Add("@UnitCost", txtUnitCost.Text)
cmd.Parameters.Add("@QuotedCost", txtQuotedCost.Text)
cmd.Parameters.Add("@Notes", If(String.IsNullOrEmpty(txtNotes.Text), DBNull.Value, txtNotes.Text))

cmd.ExecuteNonQuery()

MsgBox("Item has been submitted")

con.Close()

Open in new window


You probably won't be able to cut-and-paste and have this just work because I don't know what your underlying datatypes are so some conversion may be necessary. For example, if UnitCost is an INTEGER in the database, you'll have to convert the value in txtUnitCost.Text to an int before adding it to the parameter.

cmd.Parameters.Add("@UnitCost", Convert.ToInt32(txtUnitCost.Text))

Open in new window


This will also solve the problem of inserting null into the Notes field.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
printmediaAuthor Commented:
Great thanks Craig.

I converted UnitCost and QuotedCost to decimal like this and it worked fine and it is now inserting NULL when the Notes textbox is left blank.

Thanks again for your help and I'll read up on sql injections.

cmd.Parameters.AddWithValue("@UnitCost", Decimal.Parse(txtUnitCost.Text))
        cmd.Parameters.AddWithValue("@QuotedCost", Decimal.Parse(txtQuotedCost.Text))

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.