• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 586
  • Last Modified:

Q: "text" cannot convert from 'DBNull' to 'String'

With VB2005, I am using a DataReader to get items from a database, then show them on a Listview.

As the database has some 'null', so the error "text cannot convert from 'DBNull' to 'String'" pop up. I wish you could help me to fix it. Following is the code.

Dim objListViewItem As ListViewItem
        'Initialize a new instance of the data access base class
        Using objData As New WDABase
            Try
                'Get all projects in a DataReader object
                objData.SQL = "usp_SelectProjects"
                objData.InitializeCommand()
                objData.OpenConnection()
                objData.DataReader = objData.Command.ExecuteReader
                'See if any data exists before continuing
                If objData.DataReader.HasRows Then
                    'Clear previous list
                    lvwProjects.Items.Clear()
                    'Process all rows
                    While objData.DataReader.Read()

                        'Create a new ListViewItem
                        objListViewItem = New ListViewItem
                        'Add the data to the ListViewItem
                        objListViewItem.Text = _
                            objData.DataReader.Item("ProjectName")
                        objListViewItem.Tag = objData.DataReader.Item("ProjectID")
                        'Add the sub items to the listview item
                        objListViewItem.SubItems.Add( _
                            objData.DataReader.Item("ProjectDescription"))
                        objListViewItem.SubItems.Add( _
                            objData.DataReader.Item("SequenceNumber"))
                        objListViewItem.SubItems.Add( _
                            Format(objData.DataReader.Item("LastUpdateDate"), "g"))
                        'Add the ListViewItem to the ListView control
                        lvwProjects.Items.Add(objListViewItem)
                    End While
                End If
0
gavvic
Asked:
gavvic
2 Solutions
 
wildcard76Commented:
hi,

you may try

if not isdbnull(objData.DataReader.Item("ProjectName")) then
objListViewItem.Text = _
                            objData.DataReader.Item("ProjectName")

else

objListViewItem.Text = ""



you should apply the same to other fields in the database, you are getting this error because of a null field in the database...


hope this helps


cagatay
0
 
Arthur_WoodCommented:
another choice would be :

If objData.DataReader.IsDBNull(IndexOfField) then
    ' this item is a NULL
Else
    ' this item is NOT a Null
End If

where IndexOfField is the 0-based index of the column in the datareader that you want to test for a NUll value

AW
0
 
gavvicAuthor Commented:
These parts are the main problem when they are added to a ListView.
objListViewItem.SubItems.Add(objData.DataReader.Item("SequenceNumber"))
objListViewItem.SubItems.Add(Format(objData.DataReader.Item("LastUpdateDate"), "g"))

For example if a 'SequenceNumber' is null, the error happens. What code should I replace the ?????? as following? thanks again.

If not isdbnull(objData.DataReader.Item("SequenceNumber")) then
objListViewItem.Text =                             objData.DataReader.Item("ProjectName")
Else
??????
End if
0
 
Arthur_WoodCommented:
in this block:

If not isdbnull(objData.DataReader.Item("SequenceNumber")) then
objListViewItem.Text =                             objData.DataReader.Item("ProjectName")
Else
??????
End if

the Else clause would assign the Value that you want to see IF the value from the dataReader is in fact a NULL - and that is entirley up to you.  Perhaps you might consider setting objListViewItem.Text to "NULL Seq Number"  as a flag to indicate that the value is not 'VALID' and why not.  Just a thought.

AW

0
 
anwar ul haqueCommented:
Hi
I think you are confued with your logic, you think as Null is a value so it must appear in the list. No that is not the case Null means no value, so review your logic again and put some default value in case of null.

Regards
Anwar ul Haque
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now