Solved

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

Posted on 2014-02-19
10
649 Views
Last Modified: 2014-02-19
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

0
Comment
Question by:printmedia
  • 5
  • 4
10 Comments
 
LVL 21

Expert Comment

by:Craig Wagner
ID: 39871140
Use DBNull.Value
0
 

Author Comment

by:printmedia
ID: 39871149
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
 
LVL 21

Expert Comment

by:Craig Wagner
ID: 39871165
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
 

Author Comment

by:printmedia
ID: 39871171
Thanks.

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

Is it ok if the table has '' instead of NULLs?
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39871310
Can you show the code you use to insert data?
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:printmedia
ID: 39871341
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
 
LVL 21

Expert Comment

by:Craig Wagner
ID: 39871345
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
 

Author Comment

by:printmedia
ID: 39871560
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
 
LVL 21

Accepted Solution

by:
Craig Wagner earned 500 total points
ID: 39871571
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
 

Author Closing Comment

by:printmedia
ID: 39871622
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video discusses moving either the default database or any database to a new volume.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now