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).
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 & " "
Console.WriteLine(sqlQry)
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"
Else
topDecision = "Dont"
End If
myreader.Close()
Next
Connections and some other database objects provided by ADO.net 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)
cn.Open()
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"
Else
topDecision = "Dont Order"
End If
End Using
sb.AppendLine($"{KeyPair.Key} - {topDecision}")
Next
End Using
TextBox1.Text = sb.ToString
End Sub
User contributions licensed under CC BY-SA 3.0