[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2014-02-19
10
Medium Priority
?
940 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

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!

Question has a verified solution.

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

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

650 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