Issue Comparing Access Column


Can someone please explain to me when the "size" column wont work for comparison but I can replace the ID column in my code and it works perfectly fine. Perhaps my formatting of the access database column for Size isnt correct?

I am basically just trying to see if the key and value in my dictionary match the conditions in the access database and if so to write one text, if not write another. The error I keep getting when I have size in my code is:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll IErrorInfo.GetDescription failed with E_FAIL(0x80004005).

enter image description here

For Each KeyPair In dict
        Dim key As String
        Dim value As Integer

        key = KeyPair.Key

        value = KeyPair.Value

        Dim sqlQry As String
        sqlQry = "SELECT Item, Size FROM [Table] WHERE Item = '" & key & "'AND Size>" & value & " "

        Dim topDecision As String
        Dim cmd As OleDbCommand

        cmd = New OleDbCommand(sqlQry, myconnection)
        Dim myreader As OleDbDataReader
        myreader = cmd.ExecuteReader()

        If myreader.Read() Then
            topDecision = "Order"

            topDecision = "Dont"
        End If


asked on Stack Overflow Nov 29, 2020 by lerne8423 • edited Nov 29, 2020 by Ken White

1 Answer


Connections and some other database objects provided by use unmanaged code internally. They provide a .Dispose method where they release these resources. It is up to the coder to call the .Dispose method. Fortunately, .net provides Using...End Using blocks that handle this for us. Connections, commands and readers should be declared in the method where they are used so they can be properly closed and disposed.

Don't concatenate strings to build sql queries. Use parameters to avoid sql injection. We only need a single command and a single ParametersCollection. Only the values of the parameters change inside the loop.

A special note for OleDb parameters. The names of the parameters are ignored. The position of the parameter in the sql query should match the order that they are added to the ParametersCollection.

Declare KeyPair As KeyValuePair so you can access .Key and .Value properties.

I used a StringBuilder to collect the messages from your code. A StringBuilder is mutable (changeable) whereas a String is not. If I used a String the compiler would have to throw away a string and create a new one on each iteration. The garbage collector would be kept busy.

I used an interpolated string indicated by the $ before the string. It allows us to insert variables directly into the string if they are surrounded by braces { }.

If you follow this sample, be sure the text box at the end has Multiline = True.

Private Sub OPCode()
    Dim sqlQry = "SELECT Item, Size FROM [Table] WHERE Item = @Key AND Size > @Value;"
    Dim sb As New StringBuilder
    Using cn As New OleDbConnection("Your connection string"),
            cmd As New OleDbCommand(sqlQry, cn)
        cmd.Parameters.Add("@Key", OleDbType.VarChar, 100)
        cmd.Parameters.Add("@Value", OleDbType.Integer)


        For Each KeyPair As KeyValuePair(Of String, Integer) In dict
            cmd.Parameters("@Key").Value = KeyPair.Key
            cmd.Parameters("@Value").Value = KeyPair.Value
            Dim topDecision As String
            Using myreader = cmd.ExecuteReader()
                If myreader.Read() Then
                    topDecision = "Order"
                    topDecision = "Dont Order"
                End If
            End Using
            sb.AppendLine($"{KeyPair.Key} - {topDecision}")

    End Using
    TextBox1.Text = sb.ToString
End Sub
answered on Stack Overflow Nov 29, 2020 by Mary

User contributions licensed under CC BY-SA 3.0