I am writing a vb.net application that is searching an Access 2010 database and have run into an issue I cannot seem to resolve. The below segment of code is the issue.
Using SQLcon 'public property containing OleDbConnection
Dim ds As New DataSet
Dim da As New OleDbDataAdapter
Dim cmd As New OleDbCommand
cmd.Connection = SQLcon
cmd.CommandType = CommandType.Text
Dim heightMin As Double = OverallHeight - 0.25
Dim heightMax As Double = OverallHeight + 0.125
cmd.CommandText = "SELECT * FROM [CAGE - BARREL] WHERE [Type]=@p1 AND [Valve Size]=@p2 AND [Cage Height] BETWEEN @p3 AND @p4"
cmd.Parameters.AddWithValue("@p1", CageType) '[Type]
cmd.Parameters.AddWithValue("@p2", ValveSize) '[Valve Size]
cmd.Parameters.AddWithValue("@p3", heightMin) '[Cage Height]
cmd.Parameters.AddWithValue("@p4", heightMax) '[Cage Height]
da = New OleDbDataAdapter(cmd)
da.Fill(ds, "[CAGE - BARREL]")
@p2="10.50" 'this is a text field
@p3=heightMin=6 'this is a number/double
@p4=heightMax=6.375 'this is a number/double
I cannot reproduce the problem (either). That said, I would favor using some form of
>= AND <= in place of
Id CageType ValveSize Height 1 A XS 0.25 2 A S 0.51 3 A S2 0.55 4 A M 0.95 5 A L 1.26 6 B S 0.58
Dim sql1 = "SELECT * FROM Cage WHERE CageType=@p1 AND ValveSize=@p2 AND Height BETWEEN @p3 AND @p4" Dim sql2 = "SELECT * FROM Cage WHERE ValveSize=@p2 AND Height BETWEEN @p3 AND @p4" Dim t As String = "A" Dim v As String = "S" Dim minH As Double = 0.45 Dim maxH As Double = 0.65 Dim dt As New DataTable Using dbcon As New OleDbConnection(ACEConnStr) Using cmd As New OleDbCommand(sql2, dbcon) dbcon.Open() ' only used with 'sql1': 'cmd.Parameters.Add("@p1", OleDbType.VarChar).Value = t cmd.Parameters.Add("@p2", OleDbType.VarChar).Value = v cmd.Parameters.Add("@p3", OleDbType.Double).Value = minH cmd.Parameters.Add("@p4", OleDbType.Double).Value = maxH dt.Load(cmd.ExecuteReader) dgv1.DataSource = dt End Using End Using
I assume there is something odd with your data or data types. There are 2 SQL statements listed just for testing and reduce the amount of test data needed.
"SELECT * FROM Cage WHERE ValveSize=@p2 AND Height > @p3 AND Height <= @p4" returns the same results.
Note that things like
OleDbCommand allocate resources and ought to be disposed when you are done with them. The
Using statement does that for us. I also used
Add rather than
AddWithValue so that the datatype can be specified, leaving VB/OleDB no room to guess at whats going on. Also, there is no need for a temp Adapter or
DataSet - you can fill a
DataTable directly with a
Note also that
OleDb does not use named parameters as such - they are simply positional placeholders. With
OleDb you must
Add them in the exact order they appear in the SQL. Yours does, but this explains why the code above can just comment out the
@p1 parameter and still work.