?
Solved

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

Posted on 2014-08-05
5
Medium Priority
?
311 Views
Last Modified: 2014-08-06
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
0
Comment
Question by:powerztom
  • 3
5 Comments
 

Author Comment

by:powerztom
ID: 40242770
error message
0
 
LVL 12

Accepted Solution

by:
Harish Varghese earned 2000 total points
ID: 40242989
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 40243443
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
 

Author Closing Comment

by:powerztom
ID: 40243521
GREAT ADVICE tHANKS
0
 

Author Comment

by:powerztom
ID: 40244314
CodeCruiser thanks for insight it was helpul I didn't see your post or I would have awarded you points.Sorry Bro.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

862 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