Link to home
Create AccountLog in
Avatar of Britt Thompson
Britt Thompson🇺🇸

asked on

System.InvalidCastException: Operator '=' is not defined for type 'DBNull' and string "Y"
I've inherited a broken highly customized Sharepoint Services 3 site that has an attachment to an additional database in some of the webparts. The current section I'm having trouble repairing is a custom contacts search function that was created. It appears as if every time a null value is found from somewhere in the database I get the following message after most searches, but not all:

System.InvalidCastException: Operator '=' is not defined for type 'DBNull' and string "Y". at Microsoft.VisualBasic.CompilerServices.Operators.ConditionalCompareObjectEqual(Object Left, Object Right, Boolean TextCompare) at CCCA.CCCAcontacts.getCCCAcontacts(Int32 maximumRows, Int32 startRowIndex, String az, String keyword, String mylist) at CCCA.CCCAcontacts.getCCCAcontacts(String az) at ASP.e3514603_e9a2_4d96_b217_96b5d5a6df90__1091130600.Page_Load(Object Source, EventArgs E)  

I know I'm supposed to tell my CCCAcontacts.vb what to do with the dbnull values but my VB skills are pretty shabby and I haven't a clue how I'm supposed to do this. Below is the VB file.
Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Collections.Generic
Imports System.IO
Imports System.Web
Imports System.Configuration

Namespace CCCA



    Public Class CCCAcontacts

#Region " CCCA Contacts properties "


        Private _contactsList As List(Of CCCAperson)
        Public Property PeopleList() As List(Of CCCAperson)
            Get
                Return _contactsList
            End Get
            Set(ByVal Value As List(Of CCCAperson))
                _contactsList = Value
            End Set
        End Property

        Dim _listType As String
        Property ListType() As String
            Get
                Return _listType
            End Get
            Set(ByVal Value As String)
                _listType = Value
            End Set
        End Property

        Private _contactsCounter As Integer = 0
        Public Property ContactsCounter() As Integer
            Get
                Return _contactsCounter
            End Get
            Set(ByVal Value As Integer)
                _contactsCounter = Value
            End Set
        End Property
#End Region


        ''' <summary>
        ''' returns a List of all CCCA contacts
        ''' </summary>
        Public Sub getCCCAcontacts()
            getCCCAcontacts(Integer.MaxValue, 0, "all")
        End Sub

        ''' <summary>
        ''' returns a List of CCCA contacts by a-z
        ''' </summary>
        Public Sub getCCCAcontacts(ByVal az As String)
            Dim myaz As String = HttpUtility.HtmlDecode(az)
            getCCCAcontacts(Integer.MaxValue, 0, myaz)
        End Sub

        ''' <summary>
        ''' returns a List of CCCA contacts by keyword
        ''' </summary>
        Public Sub getCCCAcontacts(ByVal az As String, ByVal keyword As String)
            Dim myaz As String = HttpUtility.HtmlDecode(az)
            getCCCAcontacts(Integer.MaxValue, 0, "all", keyword)
        End Sub

        ''' <summary>
        ''' returns a List of CCCA contacts by List
        ''' </summary>
        Public Sub getCCCAcontacts(ByVal az As String, ByVal keyword As String, ByVal list As String)
            Dim myaz As String = HttpUtility.HtmlDecode(az)
            'getCCCAcontacts(Integer.MaxValue, 0, "", keyword, list)
            getCCCAcontacts(Integer.MaxValue, 0, az, keyword, list)
        End Sub



        ''' <summary>
        ''' returns a pagable List of CCCA contacts
        ''' </summary>
        ''' <param name="maximumRows"></param>
        ''' <param name="startRowIndex"></param>
        ''' <remarks>
        ''' Views used on Wilson SQL Server CCCA Database:
        ''' VWcccaContacts --> VWcccaContacts1 --> VWvisContacts --> VWvisContacts1 --> (Vision DB)
        ''' </remarks>
        Public Sub getCCCAcontacts(ByVal maximumRows As Integer, ByVal startRowIndex As Integer, ByVal az As String, Optional ByVal keyword As String = "", Optional ByVal mylist As String = "")
            'Public Sub getCCCAcontacts(ByVal maximumRows As Integer, ByVal startRowIndex As Integer, ByVal az As String, Optional ByVal keyword As String = "")

            Dim ContactsSQL As String = ""


            If keyword = "" Then
                If az = "" Then
                    If mylist = "announcement" Then
                        ContactsSQL = "SELECT * FROM [VWcccaContacts] where announcement = 'Y' order by LastName, FirstName"
                        _listType = "Contacts: Announcement List"
                    ElseIf mylist = "newsletter" Then
                        ContactsSQL = "SELECT * FROM [VWcccaContacts] where newsletter = 'Y' order by LastName, FirstName"
                        _listType = "Contacts: Newsletter List"
                    ElseIf mylist = "mailinglist" Then
                        ContactsSQL = "SELECT * FROM [VWcccaContacts] where mailinglist = 'Y' order by LastName, FirstName"
                        _listType = "Contacts: Mailing List"
                    ElseIf mylist = "partyinvitation" Then
                        ContactsSQL = "SELECT * FROM [VWcccaContacts] where partyinvitation = 'Y' order by LastName, FirstName"
                        _listType = "Contacts: Party Invitation List"
                    Else 'all contacts
                        ContactsSQL = "SELECT * FROM [VWcccaContacts] order by LastName, FirstName"
                        _listType = "All Contacts"
                    End If
                ElseIf az = "latest" Then
                    ContactsSQL = "SELECT TOP 10 * from vwcccacontacts order by createdate desc"
                    _listType = "Latest Contacts"
                ElseIf az = "dups" Then
                    ContactsSQL = "SELECT dbo.VWcccaContactsDups.dups, dbo.VWcccaContacts.* FROM  dbo.VWcccaContacts INNER JOIN dbo.VWcccaContactsDups ON dbo.VWcccaContacts.firstlast = dbo.VWcccaContactsDups.firstlast order by LastName, FirstName"
                    _listType = "Duplicates"
                ElseIf az = "all" Then
                    ContactsSQL = "SELECT * FROM [VWcccaContacts] order by LastName, FirstName"
                    _listType = "All Contacts"
                Else
                    ContactsSQL = "SELECT * FROM [VWcccaContacts] where lastname like '" & az & "%' order by LastName, FirstName"
                    _listType = "Contacts: " & UCase(az)
                End If
            ElseIf az = "palm" Then
                Dim firstn As String = Left(keyword, 1)
                Dim lastn As String = Right(keyword, keyword.Length - 1)
                ContactsSQL = "SELECT * FROM [VWcccaContacts] where finitial = '" & firstn & "' AND lastname like '" & lastn & "%' order by LastName, FirstName"
                _listType = "Search Results"
            Else
                ContactsSQL = "SELECT * FROM [VWcccaContacts] where fullname like '%" & keyword & "%' order by LastName, FirstName"
                _listType = "Search Results"
            End If


            Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("CCCAconnect").ConnectionString)

                'Place the data in a DataTable
                Dim myCommand As New SqlCommand(ContactsSQL, myConnection)
                Dim myAdapter As New SqlDataAdapter(myCommand)
                myConnection.Open()
                Dim dt As New DataTable
                myAdapter.Fill(dt)
                Dim resultslist As New List(Of CCCAperson)()
                Dim currentIndex As Integer = startRowIndex
                Dim itemsRead As Integer = 0
                Dim totalRecords As Integer = dt.Rows.Count

                'Populate List from DataTable
                While itemsRead < maximumRows AndAlso currentIndex < totalRecords
                    Dim person As New CCCAperson()

                    person.visPersonID = dt.Rows(currentIndex)("ContactID").ToString()
                    person.FullName = dt.Rows(currentIndex)("FullName").ToString()
                    person.FirstName = dt.Rows(currentIndex)("FirstName").ToString()
                    person.LastName = dt.Rows(currentIndex)("LastName").ToString()
                    person.Title = dt.Rows(currentIndex)("Title").ToString()
                    person.Phone = dt.Rows(currentIndex)("Phone").ToString()
                    person.HomePhone = dt.Rows(currentIndex)("HomePhone").ToString()
                    person.Fax = dt.Rows(currentIndex)("Fax").ToString()
                    person.CellPhone = dt.Rows(currentIndex)("CellPhone").ToString()
                    person.Email = dt.Rows(currentIndex)("Email").ToString()
                    person.CompanyName = dt.Rows(currentIndex)("CompanyName").ToString()
                    person.AddressName = dt.Rows(currentIndex)("AddressName").ToString()
                    person.Category = dt.Rows(currentIndex)("Category").ToString()
                    person.SubCategory = dt.Rows(currentIndex)("SubCategory").ToString()
                    person.visModUser = dt.Rows(currentIndex)("ModUser").ToString()
                    person.visStatus = dt.Rows(currentIndex)("Status").ToString()

                    If dt.Rows(currentIndex)("CLorVE").Equals("C") Then
                        person.visCVid = dt.Rows(currentIndex)("ClientID").ToString()
                    Else
                        person.visCVid = dt.Rows(currentIndex)("VendorID").ToString()
                    End If
                    If dt.Rows(currentIndex)("ClientID").ToString = "" And dt.Rows(currentIndex)("VendorID").ToString = "" Then
                        person.visType = "N"
                    Else
                        person.visType = dt.Rows(currentIndex)("CLorVE").ToString()
                    End If

                    person.visModUser = dt.Rows(currentIndex)("ModUser").ToString()
                    person.visModDate = dt.Rows(currentIndex)("ModDate").ToString()

                    If dt.Rows(currentIndex)("Announcement") = "Y" Then
                        person.ListAnnouncement = True
                    Else
                        person.ListAnnouncement = False
                    End If

                    If dt.Rows(currentIndex)("MailingList") = "Y" Then
                        person.ListMailingList = True
                    Else
                        person.ListMailingList = False
                    End If

                    If dt.Rows(currentIndex)("NewsLetter") = "Y" Then
                        person.ListNewsletter = True
                    Else
                        person.ListNewsletter = False
                    End If

                    If dt.Rows(currentIndex)("PartyInvitation") = "Y" Then
                        person.ListPartyInvitation = True
                    Else
                        person.ListPartyInvitation = False
                    End If


                    resultslist.Add(person)
                    itemsRead += 1
                    currentIndex += 1
                End While
                myConnection.Close()

                'Assign values to Properties
                _contactsList = resultslist
                _contactsCounter = totalRecords
            End Using
        End Sub


    End Class

End Namespace

Open in new window

Avatar of MikeQc
MikeQc🇨🇦
When comparing values to "Y", make sure this value is not null first using "VALUE = System.DBNull.Value".

For example, in your code:
If dt.Rows(currentIndex)("MailingList") = "Y" Then
  person.ListMailingList = True
Else
  person.ListMailingList = False
End If

Try to do instead:

person.ListMailingList = iif(dt.Rows(currentIndex)("MailingList") = System.DBNull.Value, False,  dt.Rows(currentIndex)("MailingList") = "Y")

Do it for NewsLetter, PartyInvitation and Announcement too.
This should fix your problem of Operator '=' is not defined for type 'DBNull' and string "Y"
ASKER CERTIFIED SOLUTION
Avatar of Zhaolai
Zhaolai🇺🇸
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of Britt Thompson
Britt Thompson🇺🇸

ASKER

I believe all I need to do is check this block for IsDBNull and return "N" for the null values to resolve the issue. If i remove this and its corresponding "IF mylist =..." the issue goes away. Basically, I need to know how to check this this for null values and pass a replacement value to a string?
If dt.Rows(currentIndex)("Announcement") = "Y" Then
                        person.ListAnnouncement = True
                    Else
                        person.ListAnnouncement = False
                    End If

                    If dt.Rows(currentIndex)("MailingList") = "Y" Then
                        person.ListMailingList = True
                    Else
                        person.ListMailingList = False
                    End If

                    If dt.Rows(currentIndex)("NewsLetter") = "Y" Then
                        person.ListNewsletter = True
                    Else
                        person.ListNewsletter = False
                    End If

                    If dt.Rows(currentIndex)("PartyInvitation") = "Y" Then
                        person.ListPartyInvitation = True
                    Else
                        person.ListPartyInvitation = False
                    End If

Open in new window

Avatar of Zhaolai
Zhaolai🇺🇸
Have you tried code in my last post?
Avatar of MikeQc
MikeQc🇨🇦
If fact, if the only thing is that you want another value to be set to True or False, you can compare your value to Null first (if so, set it to false, if not, set it to yourValue = "Y").
For example using any of these syntaxes:
If dt.Rows(currentIndex)("Annoucement") = SystemDBNull.Value then
 person.ListAnnoucement = False
else
 person.ListAnnoucement = dt.Rows("Annoucement") = "Y"
end if

' or
person.ListAnnoucement = iif(dt.Rows(currentIndex)("Annoucement") = System.DBNull.Value, False,  dt.Rows(currentIndex)("Annoucement") = "Y")

' or
If dt.Rows(currentIndex)("Annoucement") = SystemDBNull.Value then
 person.ListAnnoucement = False
elseif dt.Rows(currentIndex)("Announcement") = "Y" Then
 person.ListAnnouncement = True
Else
 person.ListAnnouncement = False
End If
 person.ListAnnoucement = dt.Rows("Annoucement") = "Y"
end if

Open in new window

Avatar of Britt Thompson
Britt Thompson🇺🇸

ASKER

That did it. Thanks.