Marc Collins
|
| Posted: 04/17/2002, 8:57 AM |
|
I have modified the YellowPages example using CC 2.0. Everything works
perfectly except the search function doesn't seem to work on any fields in
the database that are "Memo" instead of "Text." Every search request of a
Memo field returns the same thing: all records. The searches on the other
fields work perfectly, including displaying the correct corresponding memo
field data.
I have recreated all the search and entries forms that relate to the search
at the suggestion of a CC user. It had no effect.
I have changed all the field types to Memo in CC and it has no effect. I
changed them all to Text and it has no effect. I put them back to their
"proper" settings (all Text except the one Memo) and it has no effect.
I am using a Jet OLEDB connection to an Access database on my local
development machine. It will be moving to a Win2000 web server, though, in
the future.
Can anyone tell me how to fix this now very frustrating memo field
strangeness? I assume you are supposed to be able to search memo field
text!!! I am asking because I am a newbie....
Thanks,
Marc
--
****************************************************************************
Marc Collins
marc_collins@sympatico.ca
Your mouse has moved. Windows must be restarted for the change
to take effect. Reboot now?
****************************************************************************
|
|
|
 |
Marc Collins
|
| Posted: 04/17/2002, 9:15 AM |
|
OK...after converting the Memo field to Text using a test copy of the
database (truncating the longer fields), it still doesn't work. I also
tried deleting all data in that field except for one record and the search
still returns all records.
So what could cause one field in the database to malfunction in the search
and the others to work fine? Or does it still have to do with the original
Memo format somehow? I have checked every form and field property and
cannot fid any place that CC seems to be unhappy.
Thanks
Marc
"Marc Collins" <marc_collins@sympatico.ca> wrote in message
news:a9k60s$62q$1@news.codecharge.com...
> I have modified the YellowPages example using CC 2.0. Everything works
> perfectly except the search function doesn't seem to work on any fields in
> the database that are "Memo" instead of "Text." Every search request of a
> Memo field returns the same thing: all records. The searches on the other
> fields work perfectly, including displaying the correct corresponding memo
> field data.
>
> I have recreated all the search and entries forms that relate to the
search
> at the suggestion of a CC user. It had no effect.
>
> I have changed all the field types to Memo in CC and it has no effect. I
> changed them all to Text and it has no effect. I put them back to their
> "proper" settings (all Text except the one Memo) and it has no effect.
>
> I am using a Jet OLEDB connection to an Access database on my local
> development machine. It will be moving to a Win2000 web server, though,
in
> the future.
>
> Can anyone tell me how to fix this now very frustrating memo field
> strangeness? I assume you are supposed to be able to search memo field
> text!!! I am asking because I am a newbie....
>
> Thanks,
>
> Marc
>
> --
>
****************************************************************************
> Marc Collins
> marc_collins@sympatico.ca
>
> Your mouse has moved. Windows must be restarted for the change
> to take effect. Reboot now?
>
****************************************************************************
>
>
|
|
|
|
Alexey Alexapolsky
|
| Posted: 04/18/2002, 1:51 AM |
|
I suggest that you print out CC generated SQL in Open event, like
response.write "<br>SQL : " & sSQL
to know more about the problem.
--
Alex
CodeCharge Developer
"Marc Collins" <marc_collins@sympatico.ca> wrote in message
news:a9k72t$89g$1@news.codecharge.com...
> OK...after converting the Memo field to Text using a test copy of the
> database (truncating the longer fields), it still doesn't work. I also
> tried deleting all data in that field except for one record and the search
> still returns all records.
>
> So what could cause one field in the database to malfunction in the search
> and the others to work fine? Or does it still have to do with the
original
> Memo format somehow? I have checked every form and field property and
> cannot fid any place that CC seems to be unhappy.
>
> Thanks
>
> Marc
>
> "Marc Collins" <marc_collins@sympatico.ca> wrote in message
>news:a9k60s$62q$1@news.codecharge.com...
> > I have modified the YellowPages example using CC 2.0. Everything works
> > perfectly except the search function doesn't seem to work on any fields
in
> > the database that are "Memo" instead of "Text." Every search request of
a
> > Memo field returns the same thing: all records. The searches on the
other
> > fields work perfectly, including displaying the correct corresponding
memo
> > field data.
> >
> > I have recreated all the search and entries forms that relate to the
> search
> > at the suggestion of a CC user. It had no effect.
> >
> > I have changed all the field types to Memo in CC and it has no effect.
I
> > changed them all to Text and it has no effect. I put them back to their
> > "proper" settings (all Text except the one Memo) and it has no effect.
> >
> > I am using a Jet OLEDB connection to an Access database on my local
> > development machine. It will be moving to a Win2000 web server, though,
> in
> > the future.
> >
> > Can anyone tell me how to fix this now very frustrating memo field
> > strangeness? I assume you are supposed to be able to search memo field
> > text!!! I am asking because I am a newbie....
> >
> > Thanks,
> >
> > Marc
> >
> > --
> >
>
****************************************************************************
> > Marc Collins
> > marc_collins@sympatico.ca
> >
> > Your mouse has moved. Windows must be restarted for the change
> > to take effect. Reboot now?
> >
>
****************************************************************************
> >
> >
>
>
|
|
|
|
Marc Collins
|
| Posted: 04/18/2002, 6:55 AM |
|
I am sorry, you will have to be a lot more specific. Which files? I have
looked at the code in the default.asp, the entries.asp and the enryview.asp.
The field causing the problem (citations) looks to be dealt with the same as
all the other fields...?
Obviously you are looking for something relevant, but I don't know what it
is.
I am appending the code for the 3 pages below. If there is something else
needed, please reply and I will provide it.
Thank you,
Marc
****************************************************************************
*
Default.asp:
<%
'
' Filename: Default.asp
' Generated with CodeCharge 2.0.5
' ASP 2.0 & Templates.ccp build 11/30/2001
'
'-------------------------------
' Default CustomIncludes begin
%>
<!-- #INCLUDE FILE="Common.asp" -->
<!-- #INCLUDE FILE="Header.asp" -->
<%
' Default CustomIncludes end
'-------------------------------
'===============================
' Save Page and File Name available into variables
'-------------------------------
sFileName = "Default.asp"
sTemplateFileName = "Default.html"
'===============================
'===============================
' Default PageSecurity begin
' Default PageSecurity end
'===============================
'===============================
' Default Open Event begin
' Default Open Event end
'===============================
'===============================
' Default OpenAnyPage Event begin
' Default OpenAnyPage Event end
'===============================
'===============================
'Save the name of the form and type of action into the variables
'-------------------------------
sAction = GetParam("FormAction")
sForm = GetParam("FormName")
'===============================
' Default Show begin
'===============================
' Perform the form's action
'-------------------------------
' Initialize error variables
'-------------------------------
sCategoriesErr = ""
'-------------------------------
' Select the FormAction
'-------------------------------
Select Case sForm
Case "Categories"
CategoriesAction(sAction)
end select
'===============================
'===============================
' Display page
'-------------------------------
' Load HTML template for this page
'-------------------------------
LoadTemplate sAppPath & sTemplateFileName, "main"
'-------------------------------
' Load HTML template of Header and Footer
'-------------------------------
LoadTemplate sHeaderFileName, "Header"
'-------------------------------
SetVar "FileName", sFileName
'-------------------------------
' Step through each form
'-------------------------------
Header_Show
Search_Show
Categories_Show
'-------------------------------
' Process page templates
'-------------------------------
Parse "Header", False
Parse "main", False
'-------------------------------
' Output the page to the browser
'-------------------------------
Response.write PrintVar("main")
' Default Show end
'-------------------------------
' Destroy all object variables
'-------------------------------
' Default Close Event begin
' Default Close Event end
cn.Close
Set cn = Nothing
UnloadTemplate
'===============================
'===============================
' Display Search Form
'-------------------------------
Sub Search_Show()
Dim sFormTitle: sFormTitle = "Search"
Dim sActionFileName: sActionFileName = "Entries.asp"
'-------------------------------
' Search Open Event begin
' Search Open Event end
'-------------------------------
SetVar "FormTitle", sFormTitle
SetVar "ActionPage", sActionFileName
'-------------------------------
' Set variables with search parameters
'-------------------------------
fldname = GetParam("name")
fldprov = GetParam("prov")
fldcitations = GetParam("citations")
'-------------------------------
' Search Show begin
'-------------------------------
'-------------------------------
' Search Show Event begin
' Search Show Event end
'-------------------------------
SetVar "name", ToHTML(fldname)
SetVar "prov", ToHTML(fldprov)
SetVar "citations", ToHTML(fldcitations)
'-------------------------------
' Search Show end
'-------------------------------
'-------------------------------
' Search Close Event begin
' Search Close Event end
'-------------------------------
Parse "FormSearch", False
End Sub
'===============================
'===============================
' Tree Form Action
'-------------------------------
Sub CategoriesAction(sAction)
'-------------------------------
' Categories Action begin
'-------------------------------
sCatID = GetParam("category_id")
if not IsEmpty(sCatID) then
If CLng(DlookUp("categories", "count(*)", "par_category_id=" & sCatID))
= 0 then
cn.Close
Set cn = Nothing
response.redirect("Entries.asp?category_id=" & sCatID)
end if
end if
'-------------------------------
' Categories Action end
'-------------------------------
End Sub
'===============================
'===============================
' Display Tree Form
'-------------------------------
Sub Categories_Show()
Dim sFormTitle: sFormTitle = "Categories"
'-------------------------------
' Categories Open Event begin
' Categories Open Event end
'-------------------------------
'-------------------------------
' Categories Show begin
'-------------------------------
SetVar "FormTitle", sFormTitle
SetVar "category_id", ""
SetVar "name", ""
SetVar "par_category_id", ""
SetVar "ActionPage", "Default.asp"
sSQL = "select category_id, name, par_category_id from categories"
sCatID = GetParam("category_id")
'-------------------------------
' Categories BeforeShow Event begin
' Categories BeforeShow Event end
'-------------------------------
if IsEmpty(sCatID) or (not isNumeric(sCatID)) then
'-------------------------------
' Root category
'-------------------------------
sWhere = " where par_category_id is Null"
SetVar "CurrentCategory", ""
SetVar "CatPath", ""
else
'-------------------------------
' Subcategory
'-------------------------------
sWhere = " where category_id=" & sCatID
openrs rs, sSQL & sWhere
SetVar "CurrentCategory", " > " & GetValueHTML(rs, "name")
sParCatID = GetValueHTML(rs, "par_category_id")
set rs = nothing
'-------------------------------
' Build Path
'-------------------------------
if sParCatID = "" then SetVar "CatPath", ""
sPath = ""
while sParCatID <> ""
openrs rs, sSQL & " where category_id=" & sParCatID
sParCatID = GetValueHTML(rs, "par_category_id")
sPath = replace(replace(GetVar("CatPath"), "{CategoryID}",
GetValueHTML(rs, "category_id")), "{Category}", GetValueHTML(rs, "name")) &
sPath
set rs = nothing
wend
DBlocks("CatPath") = sPath
Parse "CatPath", false
sWhere = " where par_category_id=" & sCatID
'-------------------------------
end if
'-------------------------------
' Categories list
'-------------------------------
openrs rs, sSQL & sWhere
if rs.eof then
'-------------------------------
' No subcategories
'-------------------------------
SetVar "CategoryList", ""
Parse "FormCategories", False
exit sub
'-------------------------------
end if
'-------------------------------
' Print subcategories
'-------------------------------
while not rs.eof
SetVar "CategoryID", GetValueHTML(rs,"category_id")
SetVar "Category", GetValueHTML(rs,"name")
'-------------------------------
' Categories ShowCategory Event begin
' Categories ShowCategory Event end
'-------------------------------
Parse "CategoryList", True
rs.movenext
wend
'-------------------------------
Parse "FormCategories", False
'-------------------------------
' Categories Show end
'-------------------------------
'-------------------------------
' Categories Close Event begin
' Categories Close Event end
'-------------------------------
Set rs = Nothing
End Sub
'===============================
%>
************************************************************************
EntryView.asp:
<%
'
' Filename: EntryView.asp
' Generated with CodeCharge 2.0.5
' ASP 2.0 & Templates.ccp build 11/30/2001
'
'-------------------------------
' EntryView CustomIncludes begin
%>
<!-- #INCLUDE FILE="Common.asp" -->
<!-- #INCLUDE FILE="Header.asp" -->
<%
' EntryView CustomIncludes end
'-------------------------------
'===============================
' Save Page and File Name available into variables
'-------------------------------
sFileName = "EntryView.asp"
sTemplateFileName = "EntryView.html"
'===============================
'===============================
' EntryView PageSecurity begin
' EntryView PageSecurity end
'===============================
'===============================
' EntryView Open Event begin
' EntryView Open Event end
'===============================
'===============================
' EntryView OpenAnyPage Event begin
' EntryView OpenAnyPage Event end
'===============================
'===============================
'Save the name of the form and type of action into the variables
'-------------------------------
sAction = GetParam("FormAction")
sForm = GetParam("FormName")
'===============================
' EntryView Show begin
'===============================
' Perform the form's action
'-------------------------------
' Initialize error variables
'-------------------------------
sEntryViewErr = ""
'-------------------------------
' Select the FormAction
'-------------------------------
Select Case sForm
Case "EntryView"
EntryViewAction(sAction)
end select
'===============================
'===============================
' Display page
'-------------------------------
' Load HTML template for this page
'-------------------------------
LoadTemplate sAppPath & sTemplateFileName, "main"
'-------------------------------
' Load HTML template of Header and Footer
'-------------------------------
LoadTemplate sHeaderFileName, "Header"
'-------------------------------
SetVar "FileName", sFileName
'-------------------------------
' Step through each form
'-------------------------------
Header_Show
EntryView_Show
'-------------------------------
' Process page templates
'-------------------------------
Parse "Header", False
Parse "main", False
'-------------------------------
' Output the page to the browser
'-------------------------------
Response.write PrintVar("main")
' EntryView Show end
'-------------------------------
' Destroy all object variables
'-------------------------------
' EntryView Close Event begin
' EntryView Close Event end
cn.Close
Set cn = Nothing
UnloadTemplate
'===============================
'===============================
' Action of the Record Form
'-------------------------------
Sub EntryViewAction(sAction)
'-------------------------------
' Initialize variables
'-------------------------------
Dim bExecSQL: bExecSQL = true
Dim sActionFileName : sActionFileName = ""
Dim sParams : sParams = "?"
Dim sWhere : sWhere = ""
Dim bErr : bErr = False
Dim pPKitem_id : pPKitem_id = ""
'-------------------------------
'-------------------------------
' EntryView Action begin
'-------------------------------
sActionFileName = "Entries.asp"
sParams = sParams & "address=" & ToURL(GetParam("Trn_address")) & "&"
sParams = sParams & "name=" & ToURL(GetParam("Trn_name")) & "&"
sParams = sParams & "city=" & ToURL(GetParam("Trn_city")) & "&"
sParams = sParams & "postal_code=" & ToURL(GetParam("Trn_postal_code")) &
"&"
sParams = sParams & "prov=" & ToURL(GetParam("Trn_prov")) & "&"
sParams = sParams & "category_id=" & ToURL(GetParam("Trn_category_id"))
'-------------------------------
' CANCEL action
'-------------------------------
if sAction = "cancel" then
'-------------------------------
' EntryView BeforeCancel Event begin
' EntryView BeforeCancel Event end
'-------------------------------
cn.Close
Set cn = Nothing
response.redirect sActionFileName & sParams
end if
'-------------------------------
'-------------------------------
' Load all form fields into variables
'-------------------------------
'-------------------------------
' EntryView BeforeExecute Event begin
' EntryView BeforeExecute Event end
'-------------------------------
'-------------------------------
' Execute SQL statement
'-------------------------------
if len(sEntryViewErr) > 0 then Exit Sub
on error resume next
if bExecSQL then
cn.execute sSQL
end if
sEntryViewErr = ProcessError
on error goto 0
if len(sEntryViewErr) > 0 then Exit Sub
cn.Close
Set cn = Nothing
response.redirect sActionFileName & sParams
'-------------------------------
' EntryView Action end
'-------------------------------
end sub
'===============================
'===============================
' Display Record Form
'-------------------------------
Sub EntryView_Show()
'-------------------------------
' EntryView Show begin
'-------------------------------
Dim sWhere : sWhere = ""
Dim sFormTitle: sFormTitle = "Entry"
Dim bPK : bPK = True
'-------------------------------
' Load primary key and form parameters
'-------------------------------
if sEntryViewErr = "" then
fldaddress = GetParam("address")
fldname = GetParam("name")
fldcity = GetParam("city")
fldpostal_code = GetParam("postal_code")
fldprov = GetParam("prov")
fldcategory_id = GetParam("category_id")
SetVar "Trn_address", GetParam("address")
SetVar "Trn_name", GetParam("name")
SetVar "Trn_city", GetParam("city")
SetVar "Trn_postal_code", GetParam("postal_code")
SetVar "Trn_prov", GetParam("prov")
SetVar "Trn_category_id", GetParam("category_id")
pitem_id = GetParam("item_id")
SetVar "EntryViewError", ""
else
flditem_id = GetParam("item_id")
SetVar "Trn_address", GetParam("Trn_address")
fldname = GetParam("Trn_name")
SetVar "Trn_name", GetParam("Trn_name")
SetVar "Trn_city", GetParam("Trn_city")
SetVar "Trn_postal_code", GetParam("Trn_postal_code")
SetVar "Trn_prov", GetParam("Trn_prov")
SetVar "Trn_category_id", GetParam("Trn_category_id")
pitem_id = GetParam("PK_item_id")
SetVar "sEntryViewErr", sEntryViewErr
SetVar "FormTitle", sFormTitle
Parse "EntryViewError", False
end if
'-------------------------------
'-------------------------------
' Load all form fields
'-------------------------------
'-------------------------------
' Build WHERE statement
if IsEmpty(pitem_id) then bPK = False
sWhere = sWhere & "item_id=" & ToSQL(pitem_id, "Number")
SetVar "PK_item_id", pitem_id
'-------------------------------
'-------------------------------
' EntryView Open Event begin
' EntryView Open Event end
'-------------------------------
SetVar "FormTitle", sFormTitle
'-------------------------------
' Build SQL statement and open recordset
'-------------------------------
sSQL = "select * from items where " & sWhere
openStaticRS rs, sSQL
bIsUpdateMode = (bPK and not(sAction = "insert" and sForm = "EntryView")
and not rs.eof)
'-------------------------------
'-------------------------------
' Load all fields into variables from recordset or input parameters
'-------------------------------
if bIsUpdateMode then
fldaddress = GetValue(rs, "address")
fldcitations = GetValue(rs, "citations")
fldcity = GetValue(rs, "city")
flddepartment = GetValue(rs, "department")
fldemail = GetValue(rs, "email")
fldinstitution = GetValue(rs, "institution")
flditem_id = GetValue(rs, "item_id")
fldname = GetValue(rs, "name")
fldother = GetValue(rs, "other")
fldphone = GetValue(rs, "phone")
fldpostal_code = GetValue(rs, "postal_code")
fldprov = GetValue(rs, "prov")
fldurl_URLLink = GetValue(rs, "url")
fldurl = GetValue(rs, "url")
SetVar "EntryViewDelete", ""
SetVar "EntryViewUpdate", ""
SetVar "EntryViewInsert", ""
'-------------------------------
' EntryView ShowEdit Event begin
' EntryView ShowEdit Event end
'-------------------------------
else
if sEntryViewErr = "" then
fldname = ToHTML(GetParam("name"))
fldaddress = ToHTML(GetParam("address"))
fldcity = ToHTML(GetParam("city"))
fldprov = ToHTML(GetParam("prov"))
fldpostal_code = ToHTML(GetParam("postal_code"))
end if
SetVar "EntryViewEdit", ""
SetVar "EntryViewInsert", ""
'-------------------------------
' EntryView ShowInsert Event begin
' EntryView ShowInsert Event end
'-------------------------------
end if
Parse "EntryViewCancel", false
if sEntryViewErr = "" then
'-------------------------------
' EntryView Show Event begin
fldemail="<a href=""mailto:" & fldemail & """>" & fldemail & "</a>"
' EntryView Show Event end
'-------------------------------
end if
'-------------------------------
' Show form field
'-------------------------------
SetVar "item_id", ToHTML(flditem_id)
SetVar "name", ToHTML(fldname)
SetVar "address", ToHTML(fldaddress)
SetVar "city", ToHTML(fldcity)
SetVar "prov", ToHTML(fldprov)
SetVar "postal_code", ToHTML(fldpostal_code)
SetVar "phone", ToHTML(fldphone)
SetVar "email", fldemail
SetVar "url", fldurl
SetVar "url_URLLink", fldurl_URLLink
SetVar "citations", fldcitations
SetVar "department", ToHTML(flddepartment)
SetVar "institution", ToHTML(fldinstitution)
SetVar "other", ToHTML(fldother)
Parse "FormEntryView", False
'-------------------------------
' EntryView Close Event begin
' EntryView Close Event end
'-------------------------------
Set rs = Nothing
'-------------------------------
' EntryView Show end
'-------------------------------
End Sub
'===============================
%>
****************************************************************************
Entries.asp:
<%
'
' Filename: Entries.asp
' Generated with CodeCharge 2.0.5
' ASP 2.0 & Templates.ccp build 11/30/2001
'
'-------------------------------
' Entries CustomIncludes begin
%>
<!-- #INCLUDE FILE="Common.asp" -->
<!-- #INCLUDE FILE="Header.asp" -->
<%
' Entries CustomIncludes end
'-------------------------------
'===============================
' Save Page and File Name available into variables
'-------------------------------
sFileName = "Entries.asp"
sTemplateFileName = "Entries.html"
'===============================
'===============================
' Entries PageSecurity begin
' Entries PageSecurity end
'===============================
'===============================
' Entries Open Event begin
' Entries Open Event end
'===============================
'===============================
' Entries OpenAnyPage Event begin
' Entries OpenAnyPage Event end
'===============================
'===============================
'Save the name of the form and type of action into the variables
'-------------------------------
sAction = GetParam("FormAction")
sForm = GetParam("FormName")
'===============================
' Entries Show begin
'===============================
' Display page
'-------------------------------
' Load HTML template for this page
'-------------------------------
LoadTemplate sAppPath & sTemplateFileName, "main"
'-------------------------------
' Load HTML template of Header and Footer
'-------------------------------
LoadTemplate sHeaderFileName, "Header"
'-------------------------------
SetVar "FileName", sFileName
'-------------------------------
' Step through each form
'-------------------------------
Header_Show
Search_Show
Total_Show
Entries_Show
'-------------------------------
' Process page templates
'-------------------------------
Parse "Header", False
Parse "main", False
'-------------------------------
' Output the page to the browser
'-------------------------------
Response.write PrintVar("main")
' Entries Show end
'-------------------------------
' Destroy all object variables
'-------------------------------
' Entries Close Event begin
' Entries Close Event end
cn.Close
Set cn = Nothing
UnloadTemplate
'===============================
'===============================
' Display Grid Form
'-------------------------------
Sub Entries_Show()
'-------------------------------
' Initialize variables
'-------------------------------
Dim rs
Dim sWhere : sWhere = ""
Dim sOrder : sOrder = ""
Dim sSQL : sSQL = ""
Dim sFormTitle: sFormTitle = "Entries"
Dim HasParam : HasParam = false
Dim iSort : iSort = ""
Dim iSorted : iSorted = ""
Dim sDirection : sDirection = ""
Dim sSortParams : sSortParams = ""
Dim iRecordsPerPage : iRecordsPerPage = 20
Dim iCounter : iCounter = 0
Dim iPage : iPage = 0
Dim bEof : bEof = False
SetVar "TransitParams", "address=" & ToURL(GetParam("address")) &
"&category_id=" & ToURL(GetParam("category_id")) & "&city=" &
ToURL(GetParam("city")) & "&name=" & ToURL(GetParam("name")) &
"&postal_code=" & ToURL(GetParam("postal_code")) & "&prov=" &
ToURL(GetParam("prov")) & "&"
SetVar "FormParams", "address=" & ToURL(GetParam("address")) &
"&category_id=" & ToURL(GetParam("category_id")) & "&city=" &
ToURL(GetParam("city")) & "&name=" & ToURL(GetParam("name")) &
"&postal_code=" & ToURL(GetParam("postal_code")) & "&prov=" &
ToURL(GetParam("prov")) & "&"
'-------------------------------
' Build WHERE statement
'-------------------------------
paddress = GetParam("address")
if not isEmpty(paddress) then
HasParam = true
sWhere = sWhere & "i.[address] like '%" & replace(paddress, "'", "''") &
"%'"
end if
pcategory_id = GetParam("category_id")
if IsNumeric(pcategory_id) and not isEmpty(pcategory_id) then pcategory_id
= ToSQL(pcategory_id, "Number") else pcategory_id = Empty
if not isEmpty(pcategory_id) then
if not (sWhere = "") then sWhere = sWhere & " and "
HasParam = true
sWhere = sWhere & "i.[category_id]=" & pcategory_id
end if
pcity = GetParam("city")
if not isEmpty(pcity) then
if not (sWhere = "") then sWhere = sWhere & " and "
HasParam = true
sWhere = sWhere & "i.[city] like '%" & replace(pcity, "'", "''") & "%'"
end if
pname = GetParam("name")
if not isEmpty(pname) then
if not (sWhere = "") then sWhere = sWhere & " and "
HasParam = true
sWhere = sWhere & "i.[name] like '%" & replace(pname, "'", "''") & "%'"
end if
ppostal_code = GetParam("postal_code")
if not isEmpty(ppostal_code) then
if not (sWhere = "") then sWhere = sWhere & " and "
HasParam = true
sWhere = sWhere & "i.[postal_code] like '%" & replace(ppostal_code, "'",
"''") & "%'"
end if
pprov = GetParam("prov")
if not isEmpty(pprov) then
if not (sWhere = "") then sWhere = sWhere & " and "
HasParam = true
sWhere = sWhere & "i.[prov]='" & replace(pprov, "'", "''") & "'"
end if
if HasParam then
sWhere = " AND (" & sWhere & ")"
end if
'-------------------------------
' Build ORDER BY statement
'-------------------------------
sOrder = " order by i.name Asc"
iSort = GetParam("FormEntries_Sorting")
iSorted = GetParam("FormEntries_Sorted")
sDirection = ""
if IsEmpty(iSort) then
SetVar "Form_Sorting", ""
else
if iSort = iSorted then
SetVar "Form_Sorting", ""
sDirection = " DESC"
sSortParams = "FormEntries_Sorting=" & iSort & "&FormEntries_Sorted="
& iSort & "&"
else
SetVar "Form_Sorting", iSort
sDirection = " ASC"
sSortParams = "FormEntries_Sorting=" & iSort & "&FormEntries_Sorted="
& "&"
end if
if iSort = 1 then sOrder = " order by i.[name]" & sDirection
if iSort = 2 then sOrder = " order by i.[city]" & sDirection
if iSort = 3 then sOrder = " order by i.[prov]" & sDirection
if iSort = 4 then sOrder = " order by c.[name]" & sDirection
if iSort = 5 then sOrder = " order by i.[citations]" & sDirection
end if
'-------------------------------
' Build base SQL statement
'-------------------------------
sSQL = "select .[address] as i_address, " & _
".[category_id] as i_category_id, " & _
".[citations] as i_citations, " & _
".[city] as i_city, " & _
".[item_id] as i_item_id, " & _
".[name] as i_name, " & _
".[postal_code] as i_postal_code, " & _
".[prov] as i_prov, " & _
"[c].[category_id] as c_category_id, " & _
"[c].[name] as c_name " & _
" from [items] i, [categories] c" & _
" where [c].[category_id]=i.[category_id] "
'-------------------------------
'-------------------------------
' Entries Open Event begin
' Entries Open Event end
'-------------------------------
'-------------------------------
' Assemble full SQL statement
'-------------------------------
sSQL = sSQL & sWhere & sOrder
'-------------------------------
SetVar "FormTitle", sFormTitle
'-------------------------------
' Process the parameters for sorting
'-------------------------------
SetVar "SortParams", sSortParams
'-------------------------------
'-------------------------------
' Open the recordset
'-------------------------------
openrs rs, sSQL
'-------------------------------
'-------------------------------
' Process empty recordset
'-------------------------------
if rs.eof then
set rs = nothing
SetVar "DListEntries", ""
Parse "EntriesNoRecords", False
SetVar "EntriesNavigator", ""
Parse "FormEntries", False
exit sub
end if
'-------------------------------
'-------------------------------
' Initialize page counter and records per page
'-------------------------------
iRecordsPerPage = 20
iCounter = 0
'-------------------------------
'-------------------------------
' Process page scroller
'-------------------------------
iPage = GetParam("FormEntries_Page")
if IsEmpty(iPage) then iPage = 1 else iPage = CLng(iPage)
while not rs.eof and iCounter < (iPage-1)*iRecordsPerPage
rs.movenext
iCounter = iCounter + 1
wend
iCounter = 0
'-------------------------------
'-------------------------------
' Display grid based on recordset
'-------------------------------
while not rs.EOF and iCounter < iRecordsPerPage
'-------------------------------
' Create field variables based on database fields
'-------------------------------
fldcategory_id = GetValue(rs, "c_name")
fldcitations = GetValue(rs, "i_citations")
fldcity = GetValue(rs, "i_city")
fldname_URLLink = "EntryView.asp"
fldname_item_id = GetValue(rs, "i_item_id")
fldname = GetValue(rs, "i_name")
fldprov = GetValue(rs, "i_prov")
'-------------------------------
' Entries Show begin
'-------------------------------
'-------------------------------
' Entries Show Event begin
' Entries Show Event end
'-------------------------------
'-------------------------------
' Replace Template fields with database values
'-------------------------------
SetVar "name", ToHTML(fldname)
SetVar "name_URLLink", fldname_URLLink
SetVar "Prmname_item_id", ToURL(fldname_item_id)
SetVar "city", ToHTML(fldcity)
SetVar "prov", ToHTML(fldprov)
SetVar "category_id", ToHTML(fldcategory_id)
SetVar "citations", ToHTML(fldcitations)
Parse "DListEntries", True
'-------------------------------
' Entries Show end
'-------------------------------
'-------------------------------
' Move to the next record and increase record counter
'-------------------------------
rs.MoveNext
iCounter = iCounter + 1
wend
'-------------------------------
'-------------------------------
' Entries Navigation begin
'-------------------------------
bEof = rs.eof
if rs.eof and iPage = 1 then
SetVar "EntriesNavigator", ""
else
if bEof then
SetVar "EntriesNavigatorLastPage", "_"
else
SetVar "NextPage", (iPage + 1)
end if
if iPage = 1 then
SetVar "EntriesNavigatorFirstPage", "_"
else
SetVar "PrevPage", (iPage - 1)
end if
SetVar "EntriesCurrentPage", iPage
Parse "EntriesNavigator", False
end if
'-------------------------------
' Entries Navigation end
'-------------------------------
'-------------------------------
' Finish form processing
'-------------------------------
set rs = nothing
SetVar "EntriesNoRecords", ""
Parse "FormEntries", False
'-------------------------------
' Entries Close Event begin
' Entries Close Event end
'-------------------------------
End Sub
'===============================
'===============================
' Display Search Form
'-------------------------------
Sub Search_Show()
Dim sFormTitle: sFormTitle = "Search"
Dim sActionFileName: sActionFileName = "Entries.asp"
'-------------------------------
' Search Open Event begin
' Search Open Event end
'-------------------------------
SetVar "FormTitle", sFormTitle
SetVar "ActionPage", sActionFileName
'-------------------------------
' Set variables with search parameters
'-------------------------------
fldname = GetParam("name")
fldcity = GetParam("city")
fldprov = GetParam("prov")
fldcitations = GetParam("citations")
'-------------------------------
' Search Show begin
'-------------------------------
'-------------------------------
' Search Show Event begin
' Search Show Event end
'-------------------------------
SetVar "name", ToHTML(fldname)
SetVar "city", ToHTML(fldcity)
SetVar "prov", ToHTML(fldprov)
SetVar "citations", ToHTML(fldcitations)
'-------------------------------
' Search Show end
'-------------------------------
'-------------------------------
' Search Close Event begin
' Search Close Event end
'-------------------------------
Parse "FormSearch", False
End Sub
'===============================
'===============================
' Display Grid Form
'-------------------------------
Sub Total_Show()
'-------------------------------
' Initialize variables
'-------------------------------
Dim rs
Dim sWhere : sWhere = ""
Dim sOrder : sOrder = ""
Dim sSQL : sSQL = ""
Dim sFormTitle: sFormTitle = ""
Dim HasParam : HasParam = false
Dim iRecordsPerPage : iRecordsPerPage = 20
Dim iCounter : iCounter = 0
SetVar "TransitParams", ""
SetVar "FormParams", "address=" & ToURL(GetParam("address")) &
"&category_id=" & ToURL(GetParam("category_id")) & "&city=" &
ToURL(GetParam("city")) & "&name=" & ToURL(GetParam("name")) &
"&postal_code=" & ToURL(GetParam("postal_code")) & "&prov=" &
ToURL(GetParam("prov")) & "&"
'-------------------------------
' Build WHERE statement
'-------------------------------
paddress = GetParam("address")
if not isEmpty(paddress) then
HasParam = true
sWhere = sWhere & "i.[address] like '%" & replace(paddress, "'", "''") &
"%'"
end if
pcategory_id = GetParam("category_id")
if IsNumeric(pcategory_id) and not isEmpty(pcategory_id) then pcategory_id
= ToSQL(pcategory_id, "Number") else pcategory_id = Empty
if not isEmpty(pcategory_id) then
if not (sWhere = "") then sWhere = sWhere & " and "
HasParam = true
sWhere = sWhere & "i.[category_id]=" & pcategory_id
end if
pcity = GetParam("city")
if not isEmpty(pcity) then
if not (sWhere = "") then sWhere = sWhere & " and "
HasParam = true
sWhere = sWhere & "i.[city] like '%" & replace(pcity, "'", "''") & "%'"
end if
pname = GetParam("name")
if not isEmpty(pname) then
if not (sWhere = "") then sWhere = sWhere & " and "
HasParam = true
sWhere = sWhere & "i.[name] like '%" & replace(pname, "'", "''") & "%'"
end if
ppostal_code = GetParam("postal_code")
if not isEmpty(ppostal_code) then
if not (sWhere = "") then sWhere = sWhere & " and "
HasParam = true
sWhere = sWhere & "i.[postal_code] like '%" & replace(ppostal_code, "'",
"''") & "%'"
end if
pprov = GetParam("prov")
if not isEmpty(pprov) then
if not (sWhere = "") then sWhere = sWhere & " and "
HasParam = true
sWhere = sWhere & "i.[prov] like '%" & replace(pprov, "'", "''") & "%'"
end if
if HasParam then
sWhere = " WHERE (" & sWhere & ")"
end if
'-------------------------------
' Build base SQL statement
'-------------------------------
sSQL = "select .[address] as i_address, " & _
".[category_id] as i_category_id, " & _
".[city] as i_city, " & _
".[item_id] as i_item_id, " & _
".[name] as i_name, " & _
".[postal_code] as i_postal_code, " & _
".[prov] as i_prov " & _
" from [items] i "
'-------------------------------
'-------------------------------
' Total Open Event begin
sSQL="select count(item_id) as i_item_id from items as i"
' Total Open Event end
'-------------------------------
'-------------------------------
' Assemble full SQL statement
'-------------------------------
sSQL = sSQL & sWhere & sOrder
'-------------------------------
SetVar "FormTitle", sFormTitle
'-------------------------------
' Open the recordset
'-------------------------------
openrs rs, sSQL
'-------------------------------
'-------------------------------
' Process empty recordset
'-------------------------------
if rs.eof then
set rs = nothing
SetVar "DListTotal", ""
Parse "TotalNoRecords", False
Parse "FormTotal", False
exit sub
end if
'-------------------------------
'-------------------------------
' Initialize page counter and records per page
'-------------------------------
iRecordsPerPage = 20
iCounter = 0
'-------------------------------
'-------------------------------
' Display grid based on recordset
'-------------------------------
while not rs.EOF and iCounter < iRecordsPerPage
'-------------------------------
' Create field variables based on database fields
'-------------------------------
flditem_id = GetValue(rs, "i_item_id")
'-------------------------------
' Total Show begin
'-------------------------------
'-------------------------------
' Total Show Event begin
' Total Show Event end
'-------------------------------
'-------------------------------
' Replace Template fields with database values
'-------------------------------
SetVar "item_id", ToHTML(flditem_id)
Parse "DListTotal", True
'-------------------------------
' Total Show end
'-------------------------------
'-------------------------------
' Move to the next record and increase record counter
'-------------------------------
rs.MoveNext
iCounter = iCounter + 1
wend
'-------------------------------
'-------------------------------
' Finish form processing
'-------------------------------
set rs = nothing
SetVar "TotalNoRecords", ""
Parse "FormTotal", False
'-------------------------------
' Total Close Event begin
' Total Close Event end
'-------------------------------
End Sub
'===============================
%>
***********************************************************************
"Alexey Alexapolsky" <alexa@codecharge.com> wrote in message
news:a9m1dq$i3h$2@news.codecharge.com...
> I suggest that you print out CC generated SQL in Open event, like
> response.write "<br>SQL : " & sSQL
> to know more about the problem.
>
> --
> Alex
> CodeCharge Developer
>
>
> "Marc Collins" <marc_collins@sympatico.ca> wrote in message
>news:a9k72t$89g$1@news.codecharge.com...
> > OK...after converting the Memo field to Text using a test copy of the
> > database (truncating the longer fields), it still doesn't work. I also
> > tried deleting all data in that field except for one record and the
search
> > still returns all records.
> >
> > So what could cause one field in the database to malfunction in the
search
> > and the others to work fine? Or does it still have to do with the
> original
> > Memo format somehow? I have checked every form and field property and
> > cannot fid any place that CC seems to be unhappy.
> >
> > Thanks
> >
> > Marc
> >
> > "Marc Collins" <marc_collins@sympatico.ca> wrote in message
> >news:a9k60s$62q$1@news.codecharge.com...
> > > I have modified the YellowPages example using CC 2.0. Everything
works
> > > perfectly except the search function doesn't seem to work on any
fields
> in
> > > the database that are "Memo" instead of "Text." Every search request
of
> a
> > > Memo field returns the same thing: all records. The searches on the
> other
> > > fields work perfectly, including displaying the correct corresponding
> memo
> > > field data.
> > >
> > > I have recreated all the search and entries forms that relate to the
> > search
> > > at the suggestion of a CC user. It had no effect.
> > >
> > > I have changed all the field types to Memo in CC and it has no effect.
> I
> > > changed them all to Text and it has no effect. I put them back to
their
> > > "proper" settings (all Text except the one Memo) and it has no effect.
> > >
> > > I am using a Jet OLEDB connection to an Access database on my local
> > > development machine. It will be moving to a Win2000 web server,
though,
> > in
> > > the future.
> > >
> > > Can anyone tell me how to fix this now very frustrating memo field
> > > strangeness? I assume you are supposed to be able to search memo
field
> > > text!!! I am asking because I am a newbie....
> > >
> > > Thanks,
> > >
> > > Marc
> > >
> > > --
> > >
> >
>
****************************************************************************
> > > Marc Collins
> > > marc_collins@sympatico.ca
> > >
> > > Your mouse has moved. Windows must be restarted for the change
> > > to take effect. Reboot now?
> > >
> >
>
****************************************************************************
> > >
> > >
> >
> >
>
>
|
|
|
|
|
