Query Quandries

[Bahatmut]

I would like to thank all fo you who helped me get my other problem fixed first of all. Now I am in the process of optimizing it, and looking into how to do thigns more efficently for that code, and another project. This time, I am trying to build a Query for the OleDB that will pull the records out that are between a certain range(to allow for faster searching). I found the help paged and MSDN pages for the BETWEEN and and WHILE components, but the trick is, the ranges of data I need to pull change. I was trying this Query statement, but was getting errors when the actual datareader tried to execute

"SELECT Ref FROM Videos WHERE Ref BETWEEN Cint(TextBox2.Text) AND Cint(TextBox3.Text)"

I tried putting an extra parentehesis aroudn the Cint's, but that didn't work either. Is there anyway to use user entered values in constructing the SQL query? This is also 2 fold, as I also think this approach is how I can pull out multiple records that have a common peice of data, but if I can't figure out how to pass the info to match to into the Query, it will be very very ugly and slow code indeed.

 

[Paszt]

The secret is concatenation. As is, your statement is a string containing Cint which isn't correct SQL syntax. You need to split up the parts and have .NET evaluate the values:

"SELECT Ref FROM Videos WHERE Ref BETWEEN " & _
  Cint(TextBox2.Text) & "AND " & Cint(TextBox3.Text)

Something to note: if the values were strings (they're not here, this is just extra info) then you would need to enclose the strings in single quotes.

 

[jmcilhinney]

What Paszt has said is completely correct. It is probably not a big deal in this case because there aren't many values, but as the number of fields increase this type of string concatenation can get unwieldy quite quickly. For the sake of efficiency and readability, I strongly recommend the use of the String.Format function is cases like this:

String.Format("SELECT Ref FROM Videos WHERE Ref BETWEEN {0} AND {1}", TextBox2.Text, TextBox3.Text)

Also, there is not much point converting the text from the TextBoxes to Integers, only to have them converted back to Strings again.

 

[Bahatmut]

Hmmm...Got it.

Well I tried the first method, and the code crashed, giving me a unhandeled exception when the datareader tried to execute. I went back and removed th Cint and tried again, gettign the same result. The database I am tryign to access stores all of it's refrence 'numbers' as string data, so even through through debugger.

I solved it, the first solution didn't work, but, I had to add in a bunch of ' marks with the concatenation symbols so SQL actually could see it as string information. FInal solution SQL query below.

[size=2][color=#0000ff]Dim[/color][/size][size=2] mySelectQuery [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]String[/color][/size][size=2] = "SELECT Ref FROM Videos WHERE Ref BETWEEN " & "'" & TextBox2.Text & "'" & " AND " & "'" & TextBox3.Text & "'"[/size]
[size=2][/size] 
[size=2][CODE][/size]
[size=2][/size] 
[size=2]Thanks for all the help on this. The orignal search time for 15 items withign a block took 39 seconds, now it takes only 8 secodns for that same blcok.
[/size]

 

[jmcilhinney]

This is just a small thing but, if you are going to use the concatenation operator, why have you put each single quote in its own string? Why not just

Dim mySelectQuery As String = "SELECT Ref FROM Videos WHERE Ref BETWEEN '" & TextBox2.Text & "' AND '" & TextBox3.Text & "'"

or, using my suggestion

		Dim mySelectQuery As String = String.Format("SELECT Ref FROM Videos WHERE Ref BETWEEN '{0}' AND '{1}'", _
												    TextBox2.Text, _
												    TextBox3.Text)