System.InvalidCastException

Question

User-1662293041 posted

Hi 

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)

        VerifySortColumns(sortColumns)

        Dim sqlCmd As String = "SELECT KlantID, Naam, Voornaam FROM Klanten "

        If sortColumns.Trim() = "" Then
            sqlCmd &= "ORDER BY KlantID"
        Else
            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

        Try
            conn.Open()


            reader = cmd.ExecuteScalar

            Do While reader.Read()
                If count >= startRecord Then
                    If employees.Count < maxRecords Then
                        employees.Add(GetNorthwindEmployeeFromReader(reader))
                    Else
                        cmd.Cancel()
                    End If
                End If

                count += 1
            Loop
        Catch e As SqlException
            ' Handle exception. 
        Finally
            If reader IsNot Nothing Then reader.Close()
            conn.Close()
        End Try

        Return employees
    End Function

Answer 1

User991499041 posted

Hi qkay1982,

System.InvalidCastException

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())

Regards,

zxj

Answer 2

User753101303 posted

Hi,

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

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

Answer 3

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.

https://msdn.microsoft.com/en-us/library/ms227562(v=vs.85).aspx

  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
        Next
    End Sub

Here comes the error from.

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

Thanks in advance

Answer 4

User753101303 posted

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

Answer 5

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.

Answer 6

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

Answer 7

User-1662293041 posted

Hi PatriceSc,

Now its working again.

Thanks for the quick response.

Answer 8

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

Cathy