dataReader Conversion from type 'DBNull' to type 'String' is not valid.

Question

  I have exhausted my internet search for the answer to the below code problem.  It is
commented around the error line 12 of this text including comment lines starting at
Private Sub..  see:  playWords.PlayVerb = rdrPlayers("PlayVerb")  The values are in
comments in code below.  Help me!  Jere
 Private Sub LoadPlays(ByVal bases)
      Using Connect As SqlConnection = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\JERE\Documents\JSbaseball\Barn Storm Baseball\Baseball2012.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
         Dim strSelect As String = "SELECT * FROM Plays WHERE PlayNo = '" & bases & "';"
         Dim command As SqlCommand = New SqlCommand(strSelect, Connect)
         Connect.Open()
         Dim rdrPlayers As SqlDataReader = command.ExecuteReader()
         While rdrPlayers.Read()
            'PlayVerb is a string "OOO0RL7"
            'The table is indexed
            'I checked and the value is not NULL
            'Hover over  rdrPlayers("PlayVerb") yeilds "System DBNull"
            playWords.PlayVerb = rdrPlayers("PlayVerb")
            'error message"
            'Conversion from type 'DBNull' to type 'String' is not valid.
            'Possible solutions
            '   when cast from a # the value must be a # less than Infinity
            '   make sure the source type is convertable to the destination
            Me.TextBox1.Text = playWords.PlayVerb
            'add stats
         End While
         Connect.Close()
      End Using
   End Sub

---

Jere Small

Answer 1

First, you need to check that the database field is not null before putting it into a VB string:

        If IsDBNull(rdrPlayers("PlayVerb")) = False Then
            playWords.PlayVerb = rdrPlayers("PlayVerb")
        Else
            playWords.PlayVerb = ""
        End If

Second, you should use parameterized statements to help ensure your SQL doesn't break when a user inputs values AND that it's protected against SQL injection exploits.  For a VB.Net example code on how to do this (and an explanation), check out this blog post:

http://www.blakepell.com/Blog/?p=422

Answer 2

Jere,

Strang you did not found it, internet is full of this kind of samples

One of those

http://msdn.microsoft.com/en-us/library/system.convert.isdbnull.aspx

In your code

If not Convert.DBNullValue(rdrPlayers("PlayVerb")) then  playWords.PlayVerb = rdrPlayers("PlayVerb")

Be aware there are beside endless samples also endless way to accomplish this.

For instance where IIF is used.

---

Success
Cor

Answer 3

Please enable Option Strict On and fix the errors first.

What is the type of the field "PlayVerb" in the database? What do you want to do if the field is NULL in the database? If, for example, it is a String, and if you want to display an empty String in case the filed is NULL, this should work:

            Dim PlayVerb = rdrPlayers("PlayVerb")

            playWords.PlayVerb = If(PlayVerb Is DBNull.Value, String.Empty, PlayVerb.ToString)

BTW, why don't you reply to my emails?

---

Armin

Answer 4

The Field is not nit null it has  "OOO0RL7" in it,  as i said in the original comments.
i tried the following, got same messqage.                Dim PlayVerb = rdrPlayers("PlayVerb")
            playWords.PlayVerb = If(PlayVerb Is DBNull.Value, String.Empty, PlayVerb.ToString)

---

Jere Small

Answer 5

You also wrote

  "Hover over  rdrPlayers("PlayVerb") yeilds "System DBNull""

Either it is NULL (DBNull.Value) or it is "OOO0RL7", but not both at the same time.

---

Armin

Answer 6

I expanded the message that comes up when I hover over it and it said the following.  I tried
another field and it worked?  It has data in it!!!!
In order to evaluate an indexed property.  the property must be qualified and the 
arguments must be explicitly supplied by the user.

---

Jere Small

Answer 7

Armin, the solution might be in the database records.  There may be more than one record, one without a value and one with.  The do while loop would pick both of them up.  Suggest DELETE FROM mytable WHERE PlayVerb IS NULL; and then search code for something that doubles the INSERT function without the PlayVerb value.

Dan

Answer 8

There are no duplicates.  OOO0RR1  OOO0RR2 etc all are different in PlayNo.  But PlayVerb has

several Home Runs, but it's not the ndexed field.  just a field, like others have 1 or 2 or 0 in them, in them instead of NULL.  I wish they would have keep it like vb6, to many hick ups trying

to develope, it really slows deveopement way down.  I found a work around for control array problem that allow me to loop through the controls and put data into them and get it out.  But

not your way.  Mor like microsoft suggests with a twist.  I hesitate to provide an example.

Jere

---

Jere Small

Answer 9

Sorry dan, thought you were Armin.  I'm just frustated that's all.  I can access data if I change

it to te following but it gives me the "OOO0RR7" or waht ever it was.

Jere

playWords.PlayVerb = rdrPlayers("PlayVerb")

---

Jere Small

Answer 10

Jere any chance you look at my reply, the question you state is quite general to me?

---

Success
Cor

Answer 11

Cor I tried it and still dosen't work

jere

---

Jere Small

Answer 12

I know this might sound a little strange, but try replacing the 'While' loop with a "For i = 0 to dtreader.count - 1'.  That reader has a null row at the end that's not counted.

Answer 13

Jere, 

First of all unset that setting of you to Option Explicit Off, using that is from the time VB1 had to be compatible with Basic1

I made this code for you, can you use it complete and then report which errors are shown.

Also about your sentence of the easy way of VB6. You use in fact a method which would never been used anymore in VB to get one message. 

Option Strict On
Option Explicit On
Imports System.Data.SqlClient
Public Class Form1
    Private Playwords As Dummy
    'Here it seems you are using Option Explicit On because your original code should never run
    Private Sub LoadPlays(ByVal Bases As String)
        Using Connect As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\JERE\Documents\JSbaseball\Barn Storm Baseball\Baseball2012.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
            Dim strSelect As String = "SELECT * FROM Plays WHERE PlayNo = @base"
            Using command As SqlCommand = New SqlCommand(strSelect, Connect)
                Try
                    Connect.Open()
                    command.Parameters.AddWithValue("@base", Bases)
                    Dim rdrPlayers As SqlDataReader = command.ExecuteReader()
                    While rdrPlayers.Read()
                        If Not rdrPlayers("Playerv") Is DBNull.Value Then
                            Playwords.PlayVerb = CStr(rdrPlayers("PlayVerb"))
                            Me.TextBox1.Text = Playwords.PlayVerb
                        End If
                    End While
                Catch Ex As Exception
                    MessageBox.Show(Ex.Message)
                End Try
            End Using
        End Using
    End Sub
    Class Dummy
        Public Property PlayVerb As String
    End Class
End Class

If you have tested it and it runs I will show code which is much easier and faster to use for this case.

---

Success
Cor

Answer 14

cor, I tried the code and it jumps over the code at the if statement.  No error message.  Is it saying, no data was found that meets the criteria or somethng else.  I have no duplicates or null values.  I just finished un-installing visual studio and re-installing it and same problem. 

If Not rdrPlayers("Playverb") Is DBNull.Value Then
       playWords.PlayVerb = CStr(rdrPlayers("PlayVerb"))
       Me.TextBox1.Text = playWords.PlayVerb
End If

---

Jere Small

Answer 15

Dan, I tried it but there is no rdrPlayers.count available.      For i = 0 to rdrPlayers.count    Jere

---

Jere Small

Answer 16

If the condition is not met, there is a NULL value in the field. Any chance you have two different databases? One used in your application, the other one you are looking at to prove there is no NULL value? 

(BTW, you still didn't reply to may emails)

---

Armin

Answer 17

I changed @base, using your code, to  playno,

   Private Sub LoadPlays(ByVal Bases As String)
        Using Connect As New SqlConnection("My Connection stuff")
 Dim strSelect As String = "SELECT * FROM Plays WHERE PlayNo = playno"
            Using command As SqlCommand = New SqlCommand(strSelect, Connect)
                Try
                    Connect.Open()
                    command.Parameters.AddWithValue("@bases", Bases)
                    Dim rdrPlayers As SqlDataReader = command.ExecuteReader()
                    While rdrPlayers.Read()
                        If Not rdrPlayers("Playverb") Is DBNull.Value Then
                            playWords.PlayVerb = rdrPlayers("PlayVerb")
                            Me.TextBox1.Text = playWords.PlayVerb
                        End If
                    End While
                Catch Ex As Exception
                    MessageBox.Show(Ex.Message)
                End Try
            End Using
        End Using
    End Sub

  now it works thanks

---

Jere Small

Answer 18

I changed @base, using your code, to  playno,

 Dim strSelect As String = "SELECT * FROM Plays WHERE PlayNo = playno"

Now you don't filter anymore. You get all records. Every record meets the condtion playno = playno. Now that you can get even more records, the problem can not be solved merely by this.

---

Armin

Answer 19

That's exactly what I got, all the records.  Now I guess I have to do something like

if bases = playwords.playno then

  'got it

end if

But I have severel thousand records (plays) for bases empty, runner on first, hit and run, bunt etc

so this is really slow for a higher  PlayNo.  Open to better way.

---

Jere Small

Answer 20

No matter if you filter by code or in the SQL, you will have the same problem related to the NULL problem. I just doesn't make sense that you don't have the problem anymore by not filtering in the SQL. But, well, if it works for you...

---

Armin

Answer 21

I have another problem.  When I get a match (I stepped thru code and checked if a match) the if statement dosen't work.  so      If Trim(playWords.PlayNo) = Trim(Bases) Then  dosen't recognize the match.  Jere

While rdrPlayers.Read()
        If Not rdrPlayers("Playverb") Is DBNull.Value Then
               playWords.PlayVerb = ""
               playWords.PlayVerb = rdrPlayers("PlayVerb")
               playWords.PlayNo = rdrPlayers("PlayNo")
               If Trim(playWords.PlayNo) = Trim(Bases) Then
                      Me.TextBox1.Text = playWords.PlayVerb
                      Sam.speak(TextBox1.Text)
                      Connect.Close()
                     Exit Sub
              End If
       End If
End While

---

Jere Small

Answer 22

I found it all is good

Jere

---

Jere Small