?
Solved

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

Posted on 2014-02-19
10
Medium Priority
?
879 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

770 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