when I search

[adiaz]

When I do a search for Children's under category. I get the following error;


Script error: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'tc.channelID = t.channelID AND c.channelID = t.channelID AND c.userID = 'admin' AND t.starttime > #2003/08/07 8:13:00# AND t.category In ('Children's') AND t.starttime >=#2003/08/07 0:00:00# AND t.starttime <= #2003/08/08 0:00:00# ORDER BY t.starttime, t.t'. if strCurrentUser = "" OR strCurrentUser = "None" Then
strCurrentUser = "admin"
End if
Set fs = hs.CreateStringClass
fs.Add "SELECT top 100 t.starttime, t.endtime, t.channelnum, t.channelname, t.title, t.episode, t.description, t.category, t.episodenum, t.audio, t.subtitles, t.ratingsystem, t.rating, tc.channelNickName, t.channelID, t.programyear, t.actors, t.guests, t.hosts, t.producers, t.director, t.scheduledEvent, t.videoColor, t.videoAspect, t.repeatshowing FROM tvlistings t, userChannels c, tvchannels tc WHERE tc.channelID = t.channelID AND c.channelID = t.channelID AND c.userID = '"
fs.Add strCurrentUser
fs.Add "' AND t.starttime > "
fs.Add datString
fs.Add Year(Now)
fs.Add "/"
fs.Add Right("0" & Month(Now),2)
fs.Add "/"
fs.Add Right("0" & Day(Now),2)
fs.Add " "
fs.Add Hour(Now)
fs.Add ":"
fs.Add Right("0" & Minute(Now),2)
fs.Add ":00"
fs.Add datString
fs.Add " "
if Request.Form("title") <> "" Then
title = Request.Form("title")
title = Replace(title,"'","''")
Select case Request.Form("matchtitle")
case 1 fs.Add " AND t.title Like '%"
fs.Add title
fs.Add "%' "
case 2 fs.Add " AND t.title Like '"
fs.Add title
fs.Add "%' "
case 3 fs.Add " AND t.title = '"
fs.Add title
fs.Add "' "
End Select
End if
if Request.Form("nottitle") <> "" Then
nottitle = Request.Form("nottitle")
nottitle = Replace(nottitle,"'","''")
Select case Request.Form("notmatchtitle")
case 1 fs.Add " AND t.title NOT Like '%"
fs.Add nottitle
fs.Add "%' "
case 2 fs.Add " AND t.title NOT Like '"
fs.Add nottitle
fs.Add "%' "
case 3 fs.Add " AND t.title <> '"
fs.Add nottitle
fs.Add "' "
End Select
End if
if Request.Form("episode") <> "" Then
episode = Request.Form("episode")
episode = Replace(episode,"'","''")
Select case Request.Form("matchepisode")
case 1 fs.Add " AND t.episode Like '%"
fs.Add episode
fs.Add "%' "
case 2 fs.Add " AND t.episode Like '"
fs.Add episode
fs.Add "%' "
case 3 fs.Add " AND t.episode = '"
fs.Add episode
fs.Add "' "
End Select
End if
if Request.Form("notepisode") <> "" Then
notepisode = Request.Form("notepisode")
notepisode = Replace(notepisode,"'","''")
Select case Request.Form("notmatchepisode")
case 1 fs.Add " AND t.episode NOT Like '%"
fs.Add notepisode
fs.Add "%' "
case 2 fs.Add " AND t.episode NOT Like '"
fs.Add notepisode
fs.Add "%' "
case 3 fs.Add " AND t.episode <> '"
fs.Add notepisode
fs.Add "' "
End Select
End if
if Request.Form("keywords") <> "" Then
arrKeywords = Split(Request.Form("keywords")," ")
Select case Request.Form("matchkeyword")
case 1 fs.Add "AND ("
for x = 0 to UBound(arrKeywords)
fs.Add "t.description Like '%"
fs.Add arrKeywords(x)
fs.Add "%' "
if x < UBound[arrKeywords) Then
fs.Add "OR "
End if
Next
fs.Add ") "
case 2 fs.Add "AND ("
for x = 0 to UBound(arrKeywords)
fs.Add "t.description Like '%"
fs.Add arrKeywords(x)
fs.Add "%' "
if x < UBound[arrKeywords) Then
fs.Add "AND "
End if
Next
fs.Add ") "
case 3 fs.Add "AND t.description Like '%"
fs.Add Request.Form("keywords")
fs.Add "%' "
End Select
End if
if Request.Form("notkeyword") <> "" Then
arrKeywords = Split(Request.Form("notkeyword")," ")
Select case Request.Form("notmatch")
case 1 fs.Add "AND NOT ("
for x = 0 to UBound(arrKeywords)
fs.Add "t.description Like '%"
fs.Add arrKeywords(x)
fs.Add "%' "
if x < UBound[arrKeywords) Then
fs.Add "OR "
End if
Next
fs.Add ") "
case 2 fs.Add "AND NOT ("
for x = 0 to UBound(arrKeywords)
fs.Add "t.description Like '%"
fs.Add arrKeywords(x)
fs.Add "%' "
if x < UBound[arrKeywords) Then
fs.Add "AND "
End if
Next
fs.Add ") "
case 3 fs.Add "AND t.description NOT Like '%"
fs.Add Request.Form("notkeyword")
fs.Add "%' "
End Select
End if
if Request.Form("yearmade") <> "" Then
fs.Add " AND (t.programyear "
if Request.Form("yeardir") = 1 Then
fs.Add " < "
Else
fs.Add " > "
End if
fs.Add " '"
fs.Add Request.Form("yearmade")
fs.Add "' OR t.programyear Is NULL )"
End if
if Request.Form("category") <> "" Then
fs.Add " AND t.category In ('"
fs.Add Replace(Request.Form("category"),",","','")
fs.Add "') "
End if
if Request.Form("channel") <> "" Then
if Request.Form("channel") = "Favs Only" Then
fs.Add " AND tc.channelFavorite = 1 "
Else
fs.Add " AND t.channelID In ('"
fs.Add Replace(Request.Form("channel"),",","','")
fs.Add "') "
End if
End if
if Request.Form("credittypename") <> "" Then
Select case Request.Form("credittype")
case 1 strField = "actors"
case 2 strField = "guests"
case 3 strField = "hosts"
case 4 strField = "director"
case 5 strField = "producers"
End Select
fs.Add "AND ("
fs.Add strField
fs.Add " Like '%"
fs.Add Request.Form("credittypename")
fs.Add "%')"
End if
if Request.Form("ratings") <> "" Then
fs.Add " AND t.rating In ('"
fs.Add Replace(Request.Form("ratings"),",","','")
fs.Add "') "
End if
if Request.Form("fromdate") <> "" AND Request.Form("todate") <> "" Then
fs.Add " AND t.starttime >="
fs.Add datString
datDate1 = Request.Form("fromdate")
fs.Add Year(datDate1)
fs.Add "/"
fs.Add Right("0" & Month(datDate1),2)
fs.Add "/"
fs.Add Right("0" & Day(datDate1),2)
fs.Add " "
fs.Add Hour(datDate1)
fs.Add ":"
fs.Add Right("0" & Minute(datDate1),2)
fs.Add ":00"
fs.Add datString
fs.Add " AND t.starttime <= "
datDate2 = Request.Form("todate")
fs.Add datString
fs.Add Year(datDate2)
fs.Add "/"
fs.Add Right("0" & Month(datDate2),2)
fs.Add "/"
fs.Add Right("0" & Day(datDate2),2)
fs.Add " "
fs.Add Hour(datDate2)
fs.Add ":"
fs.Add Right("0" & Minute(datDate2),2)
fs.Add ":00"
fs.Add datString
fs.Add " "
End if
if Request.Form("sortby") <> "" Then
select case Request.Form("sortby")
case 1 fs.Add "ORDER BY t.starttime, t.title, tc.channelNickName "
intBold = 1
case 2 fs.Add "ORDER BY t.title, t.starttime, tc.channelNickName "
intBold = 2
case 3 fs.Add "ORDER BY tc.channelNickName, t.starttime, t.title "
intBold = 3
End select
End if
strSQL = fs.Value
arrResults = RunSelectQuery(strSQL,dbname)
fs.Reset
if arrResults(0,0) = "No_Data" Then
intRC = 0
Else
intRC = 1
End if
fs.Add "SELECT OptionValue, OptionName FROM UserOptions WHERE UserID = '"
fs.Add strCurrentUser
fs.Add "'"
strSQL = fs.Value
fs.Reset
arrOptions = RunSelectQuery(strSQL,dbname)
Set fs = Nothing
intShowRemind = 1
intShowRecord = 1
intShowWatch = 1
intShowRepeats = 1
intShowFavs = 1
if arrOptions(0,0) <> "No_Data" Then
for x = 0 to UBound(arrOptions,2)
strName = arrOptions(1,x)
strValue = arrOptions(0,x)
select case strName
case "intJumpWhen" intJumpWhen = CSng(strValue)
case "intHoursWide" intHoursWide = CSng(strValue)
case "intRepeatHours" intRepeatHours = strValue
case "intShowNameNum" intShowNameNum = strValue
case "intShowCatLegend" intShowCatLegend = strValue
case "intUsePopup" intUsePopup = strValue
case "intDispShowLength" intDispShowLength = strValue
case "strShowSubTles" strShowSubTles = strValue
case "strShowAudio" strShowAudio = strValue
case "strShowRating" strShowRating = strValue
case "strShowEPNum" strShowEPNum = strValue
case "strShowCat" strShowCat = strValue
case "intUseNickName" intUseNickName = strValue
case "intShowWeekDay" intShowWeekDay = strValue
case "intShowRemind" intShowRemind = strValue
case "intShowRecord" intShowRecord = strValue
case "intShowWatch" intShowWatch = strValue
case "intShowRepeats" intShowRepeats = strValue
case "intShowFavs" intShowFavs = strValue
end select
Next
End if

All other searches work fine.

[CFGuy]

I see the problem. It's the ' in the word children's.

I'll get that fixed.

Jeff Farmer
HomeSeer user since March, 2000

[adiaz]

Whar do I need to do to fix my search or do I need to wait for an update?
Thanks CF Guy!
Really some excellent work done here!!!!
I will use the heck out of this plugin!
Al

[CFGuy]

You will have to wait for the fix. But it will take less than 5 minutes to fix it so when I get home, I'll do that first thing and post it here in the thread so you don't have to wait for a new release in the updater.

Jeff Farmer
HomeSeer user since March, 2000

[CFGuy]

Here is a zip file with the search results page. It now works with children's category.

Jeff Farmer
HomeSeer user since March, 2000