Still Searching Headlines

[Nicholas]

I put together a news clips database and am trying to
search a field called Headline. I have an ASP-generated
pulldown by month that works, but if I add a straight FP
search form it makes the pulldown return all of the
records. So, I'm trying to add a non-FP search form with
the following. It doesn't generate errors but it doesn't
return records either. The FP database results region
comes back "No records returned".

<FORM METHOD="POST" NAME="Form2"
ACTION="index2.asp#News">
<p><input TYPE="TEXT" NAME="Headline" VALUE="<%
=Request("Headline")%>" size="20"><input type="submit"
value="Submit" name="B1">
</p>
<%
IF Request.Form("Headline") = "" Then
Else
Set objDC2 = Server.CreateObject("ADODB.Connection")
objDC2.Open Application
("clipsentry_connectionstring")
Set objRS2 = objDC2.Execute("SELECT * FROM
Results WHERE (Headline LIKE '%::Headline::%') ORDER BY
Date ASC")
objRS2.Close
Set objRS2 = Nothing
objDC2.Close
Set objDC2 = Nothing
End IF
%>
</form>

I've also tried this query with the same results:
Set objRS2 = objDC2.Execute("Select * FROM Results WHERE
Headline LIKE '" & Request.Form("Headline") & "'")

What am I missing??? It is returning the records but not
putting them into the FP table?
The query in the FP stuff amkes the pulldown work...adding
an FP search form changes the query:
fp_sQry="SELECT * FROM Results WHERE (Month
= '::Month::') ORDER BY Date ASC"

 

[Jim Buyens]

Instead of:

Set objRS2 = objDC2.Execute("SELECT * FROM
Results WHERE (Headline LIKE '%::Headline::%') ORDER BY
Date ASC")

you need:

Set objRS2 = objDC2.Execute("SELECT * FROM Results WHERE
(Headline LIKE '%" & Request("Headline") & "%') ORDER BY
Date ASC")

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
||---------------------------------------------------
|| Web Database Development Step by Step .NET Edition
|| Microsoft FrontPage Version 2002 Inside Out
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------

 

[Nicholas]

Thanks for the prompt reply but that didn't help.
I believe that what is happening is that server executes
the sql statement in the FP database results region and
displays that. So,

fp_sQry="SELECT * FROM Results WHERE (Month = '::Month::')
ORDER BY Date ASC"

gives me a working month pulldown and a search form that
returns no records.

If I put the Search Headline fields into the SQL the
search form works but the month pulldown returns all
records. I believe it is doing a search on the null
Headline field and ignoring any month selected. I've been
trying SQL statements like this:

SELECT * FROM Results WHERE (Month = '::Month::'
AND '%::Headline::%' <> '') OR (Headline
LIKE '%::Headline::%') ORDER BY Date DESC

So, it comes down to how would I keep it from returning
all records on a null search?

 

[Jim Buyens]

What?
(raises one eyebrow, scratches head)
(scratches head, raises one eyebrow)

In your previous post, you showed some custom ASP code. Now, you're
talking about a DRW dropdown box.

Or is it a DRW dropdown box *plus* the custom ASP code?

Or is is a DRW dropdown box plus a normal DRW query display?

What's this Month field? Is the database field text or some other
format? And if text, how do you you keep records from July 2003 from
getting mixed in with July 2004?

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
||---------------------------------------------------
|| Web Database Development Step by Step .NET Edition
|| Microsoft FrontPage Version 2002 Inside Out
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------

 

[Nicholas]

http://www.dmvnv.com/news/index2.asp

The way it sits now the pulldown works and the search
returns no records. I can switch it around to where the
search works, but then the pulldown returns all of the
records. Neither the pulldown or the search form are from
the DRW, the results table is. Using a DRW search form
makes no difference.

The problem, and I'm about ready to give up, is that
FP2000 DRW only lets you use one SQL statement to put
results into the table it generates. That statement is
contained in the DatabaseRegionStart area of the DRW-
generated code.
s-sql="statement" This is the statement I "switch around"
to get the search to work. So, I would need a statement
to the effect of If Headline is not null, search Headline,
else search Month.

I think I made a mistake by not using the standard db date
formats...it's all text. The relevant db fields to
generate the pulldown and sort are:
Date "2004-08-25" (not displayed and used for sorting)
Month "August 2004" (only shows up in the pulldown)

I do thank you, Jim, for an earlier post in which you gave
me the statement to generate the pulldown. This is in
custom ASP, not in the DRW.
Select Results.Month, First(Results.Date) AS FirstDate
FROM Results GROUP BY Results.Month ORDER BY First
(Results.Date) DESC

Anyhow, I think my options are:
1. Use a separate page to search headlines.
2. Use ASP to write the results and don't use DRW at all.
3. Give up.

I know it's hokey amateur stuff and I do genuinely thank
you for your time.

 

[Jim Buyens]

If you write custom ASP code to do the lookup, you can just code

if request("headline") = "" then
sql = "SELECT * " & _
"FROM Results " & _
"WHERE (Month = '" & request("month") & "') " & _
"ORDER BY Date DESC "
else
sql = "SELECT * " & _
"FROM Results " & _
"WHERE (Headline LIKE '%" & request("headline") & "%') " & _
"ORDER BY Date DESC "
end if

and then process whatever comand ends up in the "sql" variable.

In the DRW, you have to do something like:

WHERE IIf('::Headline::'='', [Month] = '::month::'), [Headline] Like
'%::Headline::%')

As to your months, I would really encourage you to store them in
Access "date" format, and select them using an expression like:

WHERE (DateSerial(Year([MyDate]),Month([MyDate]),1) = #%%ddlDate%%#)

where you arrange for ddlDate to always be the first of a month.

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
||---------------------------------------------------
|| Web Database Development Step by Step .NET Edition
|| Microsoft FrontPage Version 2002 Inside Out
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------

 

[Nicholas]

Thank you. I came to the same conclusion after my last
post and much hair pulling. Write the SQL with ASP and put
it in the variable that FP uses, which is fp_sQry. Then
you have to cheat and use Notepad to delete the fp_sQry
that DRW generates. If you delete it in FP HTML view, it
gives an error message and rewrites it when you save.

I will try the IIF approach first, though. I'll have to
look into changing from text to date format. I don't have
Access and don't know how to do that. Thank you again for
the help.

-----Original Message-----
If you write custom ASP code to do the lookup, you can just code

if request("headline") = "" then
sql = "SELECT * " & _
"FROM Results " & _
"WHERE (Month = '" & request("month") & "') " & _
"ORDER BY Date DESC "
else
sql = "SELECT * " & _
"FROM Results " & _
"WHERE (Headline LIKE '%" & request("headline") & "%') " & _
"ORDER BY Date DESC "
end if

and then process whatever comand ends up in the "sql" variable.

In the DRW, you have to do something like:

WHERE IIf('::Headline::'='', [Month] = '::month::'), [Headline] Like
'%::Headline::%')

As to your months, I would really encourage you to store them in
Access "date" format, and select them using an expression like:

WHERE (DateSerial(Year([MyDate]),Month([MyDate]),1) = #%% ddlDate%%#)

where you arrange for ddlDate to always be the first of a month.

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
||---------------------------------------------------
|| Web Database Development Step by Step .NET Edition
|| Microsoft FrontPage Version 2002 Inside Out
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------


"Nicholas" <[email protected]> wrote in

http://www.dmvnv.com/news/index2.asp

The way it sits now the pulldown works and the search
returns no records. I can switch it around to where the
search works, but then the pulldown returns all of the
records. Neither the pulldown or the search form are from
the DRW, the results table is. Using a DRW search form
makes no difference.

The problem, and I'm about ready to give up, is that
FP2000 DRW only lets you use one SQL statement to put
results into the table it generates. That statement is
contained in the DatabaseRegionStart area of the DRW-
generated code.
s-sql="statement" This is the statement I "switch around"
to get the search to work. So, I would need a statement
to the effect of If Headline is not null, search Headline,
else search Month.

I think I made a mistake by not using the standard db date
formats...it's all text. The relevant db fields to
generate the pulldown and sort are:
Date "2004-08-25" (not displayed and used for sorting)
Month "August 2004" (only shows up in the pulldown)

I do thank you, Jim, for an earlier post in which you gave
me the statement to generate the pulldown. This is in
custom ASP, not in the DRW.
Select Results.Month, First(Results.Date) AS FirstDate
FROM Results GROUP BY Results.Month ORDER BY First
(Results.Date) DESC

Anyhow, I think my options are:
1. Use a separate page to search headlines.
2. Use ASP to write the results and don't use DRW at all.
3. Give up.

I know it's hokey amateur stuff and I do genuinely thank
you for your time.
code.
Now, you're in
message news:<c66101c48a28$f9dd95b0 [email protected]>... ORDER
BY ORDER
BY straight
FP form
with ORDER
BY but
not

.

 

[Nicholas]

This ASP code worked like a champ. Thank you. The IIF
statement didn't...it just returned errors about blank
fields. I tried IS NULL and default values, then gave up.

Thanks again for your help.

-----Original Message-----
If you write custom ASP code to do the lookup, you can just code

if request("headline") = "" then
sql = "SELECT * " & _
"FROM Results " & _
"WHERE (Month = '" & request("month") & "') " & _
"ORDER BY Date DESC "
else
sql = "SELECT * " & _
"FROM Results " & _
"WHERE (Headline LIKE '%" & request("headline") & "%') " & _
"ORDER BY Date DESC "
end if

and then process whatever comand ends up in the "sql" variable.

In the DRW, you have to do something like:

WHERE IIf('::Headline::'='', [Month] = '::month::'), [Headline] Like
'%::Headline::%')

As to your months, I would really encourage you to store them in
Access "date" format, and select them using an expression like:

WHERE (DateSerial(Year([MyDate]),Month([MyDate]),1) = #%% ddlDate%%#)

where you arrange for ddlDate to always be the first of a month.

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
||---------------------------------------------------
|| Web Database Development Step by Step .NET Edition
|| Microsoft FrontPage Version 2002 Inside Out
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------


"Nicholas" <[email protected]> wrote in

http://www.dmvnv.com/news/index2.asp

The way it sits now the pulldown works and the search
returns no records. I can switch it around to where the
search works, but then the pulldown returns all of the
records. Neither the pulldown or the search form are from
the DRW, the results table is. Using a DRW search form
makes no difference.

The problem, and I'm about ready to give up, is that
FP2000 DRW only lets you use one SQL statement to put
results into the table it generates. That statement is
contained in the DatabaseRegionStart area of the DRW-
generated code.
s-sql="statement" This is the statement I "switch around"
to get the search to work. So, I would need a statement
to the effect of If Headline is not null, search Headline,
else search Month.

I think I made a mistake by not using the standard db date
formats...it's all text. The relevant db fields to
generate the pulldown and sort are:
Date "2004-08-25" (not displayed and used for sorting)
Month "August 2004" (only shows up in the pulldown)

I do thank you, Jim, for an earlier post in which you gave
me the statement to generate the pulldown. This is in
custom ASP, not in the DRW.
Select Results.Month, First(Results.Date) AS FirstDate
FROM Results GROUP BY Results.Month ORDER BY First
(Results.Date) DESC

Anyhow, I think my options are:
1. Use a separate page to search headlines.
2. Use ASP to write the results and don't use DRW at all.
3. Give up.

I know it's hokey amateur stuff and I do genuinely thank
you for your time.
code.
Now, you're in
message news:<c66101c48a28$f9dd95b0 [email protected]>... ORDER
BY ORDER
BY straight
FP form
with ORDER
BY but
not

.