Mike Mike - 3 months ago 8x
Vb.net Question

SQL BETWEEN Query returning incorrect results

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]")
End Using

When I run the SQL statement directly in Access with a particular set of values I get 3 results as expected. When I run it through my program, I get 6 where 3 of the values are outside of my bounds for the BETWEEN statement. I've double checked the data types for the field in Access and the variables in vb.net. I know I'm missing something. Anyone out there have any ideas where I'm going wrong?


The values in use are as follows.

@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

Values returned by the .net program include 5,5 and 3.688 which are outside of the bounds above and 6, 6, and 6.188 which are correct.


I cannot reproduce the problem (either). That said, I would favor using some form of >= AND <= in place of BETWEEN.

Test data:

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)

        ' 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

        dgv1.DataSource = dt
    End Using
End Using


enter image description here

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 OleDBConnection and 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 DataReader.

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.

See Also: