Solved

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

Posted on 2014-02-19
10
733 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

789 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