Need to pull info from MS SQL SERVER INTO A LISTVIEW CONTROL THAT HAS DBNULL VALUES

I am pulling data from MS SQL SERVER INTO A LISTVIEW CONTROL HOWEVER THERE ARE SOME NULLS That are causing my program to not function right and I don't know how to declare whatever it is I need to declare in order to get this data pull to have some null values.

here is the code I want keep it Option Exlicit and Option Strict

 Public Sub FilllListview()
        Dim playersList As List(Of Players)
        Try
            playersList = PlayersSQL.GetPlayers
            If playersList.Count > 0 Then
                Dim players As Players
                For i As Integer = 0 To playersList.Count - 1
                    players = playersList(i)
                    lvPlayers.Items.Add(players.Index)
                    lvPlayers.Items(i).SubItems.Add(
                        CInt(players.ID).ToString)
                    lvPlayers.Items(i).SubItems.Add(
                        CStr(players.Firstname))
                    lvPlayers.Items(i).SubItems.Add(
                        CStr(players.Lastname))
                    lvPlayers.Items(i).SubItems.Add(
                        CStr(players.Jersey))
                    lvPlayers.Items(i).SubItems.Add(
                        CStr(players.Position))
                    lvPlayers.Items(i).SubItems.Add(
                       CStr(players.NFLExperience))
                    lvPlayers.Items(i).SubItems.Add(
                       CStr(players.Status))
                    lvPlayers.Items(i).SubItems.Add(
                        CStr(players.Height))
                    lvPlayers.Items(i).SubItems.Add(
                        CStr(players.Weight))
                    lvPlayers.Items(i).SubItems.Add(
                        CStr(players.College))
                Next
            Else
                MessageBox.Show("All playerss are paid in full.",
                    "No Balance Due")
                Me.Close()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, ex.GetType.ToString)
            Me.Close()
        End Try
    End Sub

Open in new window

EE Help please
powerztomAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

powerztomAuthor Commented:
error message
0
Harish VargheseProject LeaderCommented:
I suspect, the error is not thrown from above function; it should be from PlayersSQL.GetPlayers() method. The message indicates that one column retrieved from database has a NULL value (which is called DBNull in .net) and there is no equivalent value for that in .net. You may follow below approach:

1. Identify the columns that may return a NULL value. You can run the SQL (that you use in GetPlayers method) from SSMS to see which columns have NULL values. (Ideally you should be analysing your base tables/stored procedures to judge which columns may return NULL values - now and in future).

2. You may be executing the SQL in GetPlayers() method using ExecuteReader method or using SQL Data Adapter to fill a dataset. In either case, there will be a loop that copy values from the dataset or datareader to a Players object. Use an IF condition there to check if the value is NULL and if so, assign appropriate value to the object variable (instead of NULL).

Here is an example (c# code, but you can translate to VB.Net):
if (dr["Height"] != DBNull.Value)
    players.Height = Convert.ToDecimal(dr["Height"]);
else
    players.Height = 0;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CodeCruiserCommented:
This error can be any of your statements which are doing conversion such as

CStr(players.Firstname))

I would suggest you write a simple function and then use that

Public Function GetString(Value As Object) As String
If String.IsNullOrEmpty(Value) = False) Then
    Return Value
Else
    Return ""
End If


Then change
CStr(players.Firstname))

to
GetString(players.Firstname))
0
powerztomAuthor Commented:
GREAT ADVICE tHANKS
0
powerztomAuthor Commented:
CodeCruiser thanks for insight it was helpul I didn't see your post or I would have awarded you points.Sorry Bro.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.