B4J Question SQL Search Query [Solved]

[Peter Lewis]

Hi

I am trying to do a search query from a file of prices. The file has a few fields that must match and secondly 2 fields that are upper and lower range.

I tried this

        rb=DB.Sql1.ExecQuery2("SELECT shape, low_size, high_size, color, clarity ,caratprice, date FROM price WHERE shape = ? and color = ? and clarity = ? and low_size <= ? and high_size >= ?",Array As String((ra.GetString("shape")),(ra.GetString("colour")),(ra.GetString("clarity")),(ra.GetString("carat")),(ra.GetString("carat"))))

Somehow everything works fine until I get to the 2 ranges and then there are NO results.
I was thinking of putting all the values into a list but this is easier to fault find and check

and Example is the incoming carat field might be 0.55
and the fields in the file I am trying to search has a low_size of 0.5 and a high_size of 0.6.

While typing this out I just though of something. I am getting a string and trying to work out a range between strings, these should be doubles.

is there some sort of conversion I need to do or I remember reading somewhere that it is automatic (something was) , cannot remember.

Any direction would be appreciated. thx

 

[Peter Lewis]

I also tried getDouble now and it also did not give results.... I can feel I am getting closer

    rb=DB.Sql1.ExecQuery2("SELECT shape, low_size, high_size, color, clarity ,caratprice, date FROM price WHERE shape = ? and color = ? and clarity = ? and low_size <= ? and high_size >= ?",Array As String((ra.GetString("shape")),(ra.GetString("colour")),(ra.GetString("clarity")),(ra.GetDouble("carat")),(ra.GetDouble("carat"))))    ',"carat","colour","clarity")

 

[Peter Lewis]

Eveything works up to here with lots of results

    rb=DB.Sql1.ExecQuery2("SELECT shape, low_size, high_size, color, clarity ,caratprice, date FROM price WHERE shape LIKE ? and color LIKE ?  and clarity LIKE ?",Array As String((ra.GetString("shape")),(ra.GetString("colour")),(ra.GetString("clarity"))))   ',"carat","colour","clarity")

 

[Peter Lewis]

Found the Problem. The price file sizes were in string format.