System.InvalidCastException RRS feed

  • Question

  • User-1662293041 posted


    I have in the past coding in vb.net, but now i make a website for my clients data.

    Can someone say wath i missed in this code?

    The error comes from the

    reader = cmd.ExecuteScalar

    i have search over the internet and find nothing that can help me.

    If you need more code please ask me.

    Thanks in advance.

    Public Function HaalAlleKlanten(sortColumns As String, startRecord As Integer,
                                        maxRecords As Integer) As List(Of Klant)
            Dim sqlCmd As String = "SELECT KlantID, Naam, Voornaam FROM Klanten "
            If sortColumns.Trim() = "" Then
                sqlCmd &= "ORDER BY KlantID"
                sqlCmd &= "ORDER BY " & sortColumns
            End If
            Dim conn As SqlConnection = New SqlConnection(_connectionString)
            Dim cmd As SqlCommand = New SqlCommand(sqlCmd, conn)
            Dim reader As SqlDataReader = Nothing
            Dim employees As List(Of Klant) = New List(Of Klant)()
            Dim count As Integer = 0
                reader = cmd.ExecuteScalar
                Do While reader.Read()
                    If count >= startRecord Then
                        If employees.Count < maxRecords Then
                        End If
                    End If
                    count += 1
            Catch e As SqlException
                ' Handle exception. 
                If reader IsNot Nothing Then reader.Close()
            End Try
            Return employees
        End Function

    Thursday, November 9, 2017 7:22 AM

All replies

  • User991499041 posted

    Hi qkay1982,


    SqlCommand.ExecuteScalar Method executes the query, and returns the first column of the first row in the result set returned by the query.

    An InvalidCastException exception is thrown when the conversion of an instance of one type to another type is not supported.

    For example, attempting to convert a Char value to a DateTime value throws an InvalidCastException exception.

    Next, we don't know the type of the KlantID field.

    It could be that you're getting an int returned, or perhaps it's string.

    The simplest way to find out is just to use:

    Dim rr As Object = cmd.ExecuteScalar

    and then look in the debugger.

    Also look at the type of the field in the database, that should really tell you what to expect.

    If KlantID is a 32-bit signed integer, you can use

    Dim klantID As Int32 = 0
    klantID = Convert.ToInt32(cmd.ExecuteScalar())



    Thursday, November 9, 2017 8:33 AM
  • User753101303 posted


    My understanding is that you actually want to use reader = cmd.ExecuteReader

    ExecuteScalar is to get directly a single value from a query...

    Thursday, November 9, 2017 8:38 AM
  • User-1662293041 posted

    Hi zxj,

    Thanks for the answer.

    The KlantId is indeed a int in the database.

    I have copy the code from this url and rewriten a little to my solution.


      Private Sub VerifySortColumns(sortColumns As String)
            If sortColumns.ToLowerInvariant().EndsWith(" desc") Then _
            sortColumns = sortColumns.Substring(0, sortColumns.Length - 5)
            Dim columnNames() As String = sortColumns.Split(",")
            For Each columnName As String In columnNames
                Select Case columnName.Trim().ToLowerInvariant()
                    Case "KlantID"
                    Case "naam"
                    Case "Voornaam"
                    Case ""
                    Case Else
                        Throw New ArgumentException("SortColumns contains an invalid column name.")
                End Select
        End Sub

    Here comes the error from.

    i have delete Case "KlantID" and now it is working.

    Thanks in advance

    Thursday, November 9, 2017 9:23 AM
  • User753101303 posted

    This code sample uses reader = cmd.ExecuteReader as suggested earlier. This is not what you want?

    Thursday, November 9, 2017 9:31 AM
  • User-1662293041 posted

    Hi PatriceSc

    This was the error i don't now why it is now working.

    Yesterday evening i have working trough this a whole night long.

    I have changed the executescalar to executereader and now its working good.

    Thanks in advance and for the quick response.

    Now i can work further.

    Thursday, November 9, 2017 9:34 AM
  • User753101303 posted

    Ah I missed you had a new error. It can be best to close this one and open a new thread rather than switching to other unrelated issues in the same thread.

    What if you show the column name in your error message ? Also it seems you are comparing lower case and uppercase

    Thursday, November 9, 2017 9:40 AM
  • User-1662293041 posted

    Hi PatriceSc,

    Now its working again.

    Thanks for the quick response.

    Thursday, November 9, 2017 9:59 AM
  • User-707554951 posted

    Hi qkay1982,

    It seems that you thread have been solved,

    If that the case, could you please close this thread via mark it as answer we will know your question is solved, we can focus on other unsolved issues, it saves our time. Thanks for your understanding.

    Best regards


    Friday, November 10, 2017 2:44 AM