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.InvalidCastExceptio n: Operator '=' is not defined for type 'DBNull' and string "Y". at Microsoft.VisualBasic.Comp ilerServic es.Operato rs.Conditi onalCompar eObjectEqu al(Object Left, Object Right, Boolean TextCompare) at CCCA.CCCAcontacts.getCCCAc ontacts(In t32 maximumRows, Int32 startRowIndex, String az, String keyword, String mylist) at CCCA.CCCAcontacts.getCCCAc ontacts(St ring az) at ASP.e3514603_e9a2_4d96_b21 7_96b5d5a6 df90__1091 130600.Pag e_Load(Obj ect 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.
System.InvalidCastExceptio
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
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)("Mai lingList") = "Y" Then
 person.ListMailingList = True
Else
 person.ListMailingList = False
End If
Try to do instead:
person.ListMailingList = iif(dt.Rows(currentIndex)( "MailingLi st") = System.DBNull.Value, False, Â dt.Rows(currentIndex)("Mai lingList") = "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"
For example, in your code:
If dt.Rows(currentIndex)("Mai
 person.ListMailingList = True
Else
 person.ListMailingList = False
End If
Try to do instead:
person.ListMailingList = iif(dt.Rows(currentIndex)(
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
Zhaolai🇺🇸
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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
Zhaolai🇺🇸
Have you tried code in my last post?
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:
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
That did it. Thanks.