specified cast is not valid

Question

Here is a code that retrieve values from the database, but my problem is that it throws out an exception saying "InvalidCastException was unhandled specified cast is not valid". I am now confused what went wrong, The code and the table stated below.

Here is the code:

Public connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source =" & Application.StartupPath & 
                "\TestData.accdb; Persist Security info = false"
Public Conn As New OleDbConnection

Private Sub TestForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Loard
    Conn.ConnectionString = connstring
    Conn.Open()

    LoadValue( )

End Sub 

Private Sub LoadValue( )
    Dim i As Integer    
    Dim cmd As OleDbCommand = New OleDbCommand

    With cmd
        .CommandText = "SELECT MAX(Guard_ID) FROM Guard"
        .CommandType = CommandType.Text
        .Connection = Conn
        .ExecuteNonQuery()

        Dim reader As OleDbDataReader = cmd.ExecuteReader


        If reader.Read Then

            TextBox1.Text = reader.GetString(0)
        i = TextBox1.Text + 1
        TextBox1.Text = i

            reader.Close()
        End If
    End With

End Sub

The table reference:

enter image description here

Exception Error:

enter image description here

I am really confused now on why the code does not work, any help and advice will be gladly accepted. Thanks in advance.

does the `Conn` object exist? What line does the error get thrown on? — hammus, May 23 '14 at 04:47
i have completed the code including conn object. error gets thrown on line "TextBox1.Text = reader.GetString(0)" — Marc Intes, May 23 '14 at 04:56
What is the result of reader.GetFieldType(0)? That will show you the type returned. It may be DbNull if the Guard table has no rows. — shf301, May 23 '14 at 05:28
`Select MAX` on a Text column is dubious: which is the greater of `"Foo"` and `"Bar"`? `rdr.Item(0).ToString` may resolve the immediate issue (it will return `String.Empty` for an empty table), but you should turn on Option Strict. — Ňɏssa Pøngjǣrdenlarp, May 23 '14 at 05:46

Jade · Accepted Answer · 2014-05-23T10:48:57.497

try this,

Private Sub LoadValue()
    Dim i As Integer
    Dim cmd As OleDbCommand = New OleDbCommand

    With cmd
        .CommandText = "SELECT MAX(Guard_ID) FROM Guard"
        .CommandType = CommandType.Text
        .Connection = Conn
        .ExecuteNonQuery()

        Dim reader As OleDbDataReader = cmd.ExecuteReader


        If reader.Read Then

            Dim tmpVal As Object = reader.Item(0)
            TextBox1.Text = IIf(IsDBNull(tmpVal), "0", tmpVal.ToString())

            i = CInt(TextBox1.Text) + 1
            TextBox1.Text = i.ToString()

            reader.Close()
        End If
    End With

End Sub

specified cast is not valid

1 Answers1

Linked