Possible Query structure problem.

[Bahatmut]

Possible Query structure problem.[RESOLVED]

I had hoped to not have to use this forum again (no offense) but I cannot figure out why (and yes, I checked all the sources I coudl find, help, MSDN, etc etc) this query doesn't seem to return the proper results.

Query Format:

[size=2]VideoQuery = [/size][size=2][color=#0000ff]String[/color][/size][size=2].Format("SELECT Ref, Title, Release_Date, Cost_Price, Category, Spare1 from Videos WHERE (Release_Date BETWEEN #{0}# AND #{1}#) {2} ORDER BY {3}", TextBox1.Text, TextBox2.Text, GenSearch, SortOrder)[/size]
[size=2]

[/size]

The TextBox 1 and 2 values are properly converted mm/dd/yyyy values, and the Sort order is determinded this way:

[/size]
[size=2][size=2][/size][size=2][color=#0000ff]If[/color][/size][size=2] RadioButton1.Checked = [/size][size=2][color=#0000ff]True[/color][/size][size=2][color=#0000ff]Then[/color][/size][size=2] SortOrder = "Release_Date ASC, Ref DESC"
 
[/size][size=2][color=#0000ff]If[/color][/size][size=2] RadioButton2.Checked = [/size][size=2][color=#0000ff]True[/color][/size][size=2][color=#0000ff]Then[/color][/size][size=2] SortOrder = "Release_Date ASC, Title ASC"[/size]
[size=2]

However, the GenSearch I think is the problem, it's possible values are determined this way:

[/size]
[size=2][color=#0000ff]If[/color][/size][size=2] RadioButton3.Checked = [/size][size=2][color=#0000ff]True[/color][/size][size=2][color=#0000ff]Then[/color][/size][size=2] GenSearch = [/size][size=2][color=#0000ff]Nothing
 
[/color][/size][size=2][color=#0000ff]If[/color][/size][size=2] RadioButton4.Checked = [/size][size=2][color=#0000ff]True[/color][/size][size=2][color=#0000ff]Then[/color][/size][size=2] GenSearch = "AND (Genre='Adult' OR 'P.S.')"
 
[/size][size=2][color=#0000ff]If[/color][/size][size=2] RadioButton5.Checked = [/size][size=2][color=#0000ff]True[/color][/size][size=2][color=#0000ff]Then[/color][/size][size=2] GenSearch = "And (Genre NOT Like 'Adult' or 'P.S.')"
 
[/size][size=2]

When I run with a forced pause in the debug, the radio buttons seem to be working fine, and the proper values are being inserted for the correct item. However, it never seems to pull right, it acts as if it ALWAYS gets the 'GenSearch = NOTHING' value on the actual data that is pulled? WHere do I have the thing messed up? ANd yes, the formatting of all textboxes and strings for the query occur BEFORE the actual String.Format[/size]

 

[TechGnome]

Just for squirts & giggles, try changing

If RadioButton3.Checked = TrueThen GenSearch = Nothing

to

If RadioButton3.Checked = True Then GenSearch = ""

I think that's what you really want to do. Setting GenSearch = Nothing destroyes your GenSearch object (everything in .NET is an object)... which surprises me that you're not getting a NullValueException error in the .Format function call.

-tg

 

[Bahatmut]

Nope

Nope, that change had no effect at all on the program, it still is misbehaving.

 

[Blokz]

Change:

If RadioButton4.Checked = TrueThen GenSearch = "AND (Genre='Adult' OR 'P.S.')"

If RadioButton5.Checked = TrueThen GenSearch = "And (Genre NOT Like 'Adult' or 'P.S.')"
To:
If RadioButton4.Checked = TrueThen GenSearch = "AND (Genre='Adult' OR Genre='P.S.')"

If RadioButton5.Checked = TrueThen GenSearch = "And (Genre NOT Like 'Adult%' or Genre NOT Like 'P.S.%')"
What does that return?

Blokz

 

[TechGnome]

"it still is misbehaving." -- define misbehaving.

-tg

 

[Bahatmut]

partially.

Ok BLokz, your input partially helped. the problem with the radio button 4 now works properly, but the radio button 5 is still not giving my proper results. And TechGnome, by misbehaving, I mean it's still giving my the same problem before, improper search results.

 

[TechGnome]

Try printing out the SQL after you have built it but before you execute it.... make sure it is what you expect it to be.

-tg

 

[Blokz]

In RadioButton5, are you wanting to return all Genre that are NOT either 'Adult' or 'P.S.'? If so, don't use LIKE, just use <>

Such as:

If RadioButton5.Checked = True Then GenSearch = "AND Genre <> 'Adult' AND Genre <> 'P.S.'"

Blokz