*** TOUGH ONE *** Posting Form Results - using multiple selection boxes (ASP)

[John R.]

OK I've been slaving over this for too long... Can
someone help me figure out this problem???

I've created an ASP website using FP2003 and it interfaces
with MS Access 2002. I am trying to create a search page
using multiple selction boxes which post to a results page
with multiple values (obviously).

FrontPage doesn't like this query statement that I am
using in the Database Results Wizard:

SELECT *, FORMAT (Asking_Price, '##,##0.00') as AP FROM
Available_Spaces_Query WHERE Neighborhood_Number IN
:Neighborhood_Number: AND Asking_Price >= ::LowPrice::
AND Asking_Price <= ::HighPrice:: AND Bedroom_ID
<= ::Bedroom_Description:: AND Bathroom_ID
<= ::Bathrooms:: AND Approx_Sq_Ft <= ::SqFt:: AND
Property_Type IN (':roperty_Type::') ORDER BY
Asking_Price ASC

FP2003 didn't like the "IN" statements (or parentheses in
SQL statements for that matter, how ironic that THIS is in
parentheses) and it used to kick me out and tell me that
there was a Database Connection Error. OK. So I would
change the SQL statement in the grey text area of the code
and then it accepted it. But it didn't give me any
desired results. Instead, I would get a page that says
Database Results Wizard Error. THEN!! With the help of a
friend from Experts Exchange I was told to make some
changes to 4 .inc files in FP2003 FPCLASS folder because
there were some "known issues" with this version of
FrontPage and this fix restores FP2002 settings for form
handling.

I was told to change the sqripts in the following 4
files: fpdbform.inc, fpdbrgn1.inc, fpdbbrgn2.inc and
fpdblib.inc. Here's the kicker...I didn't even have a
fpdbform.inc file!!! So I created it and then copied the
others over as suggested. (Don't worry folks, I made
backups of all the original files.)

Well, none of this has worked and that's why I'm here.
Now after I perform a search I get "The page cannot be
displayed - There is a problem with the page you are
trying to reach and it cannot be displayed. " What is the
deal? Why does Bill Gates hate me so much? I never did
anything bad to him or his family....not that I know of
anyway

Seriously, if anyone knows how to solve this, please
respond ASAP. I would greatly appreciate any help that
you could give.

Kind regards,
John R.

 

[Jim Buyens]

I've never had trouble with FP accepting parentheses in a
properly formed SQL statement, and that includes the IN
function.

However, are Neighborhood_Number and Property_Type both
defined within the database as numeric? Because for a
numeric field
IN(2,3,4)
is valid, but for a text field, the following is required:
IN ('2','3','4')
Also, an empty argument list such as
IN()
is invalid.

Where are the form field values Neighborhood_Number and
Property_Type coming from?

o If they're from a text box, any visitor who mistypes
an entry will get an uglier message than you
probably want.

o If they're coming from a multi-select list box or
a series of check boxes that all have the same name,
the default conversion of the resulting array to a
string probably isn't coming out in the format
that SQL wants.

To solve either of these problems, you'd probably have to
code the whole page in ASP or ASP.NET.

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)
|/---------------------------------------------------
*----------------------------------------------------

 

[John R.]

PROBLEM SOLVED!!!!!

I was changing the wrong files in the wrong folder.

***WARNING***
As I found out the hard way, there is a distinct
difference between the FPCLASS folder in the OFFICE 11
directory and the "_fpclass" folder that should be visible
in the current web - folder view of FrontPage 2003. If
you are using ASP and the _fpclass folder is not visible,
please make sure that you check the permissions for that
directory as the folder may be checked as "hidden".


Here's the solution for all who are/were having difficulty
with this problem...

BTW...I sure hope Microsoft fixes this issue with the next
update or new version of FrontPage!!!!

====================================
If you are using FP2003, the work around is to change the
following files in the _fpclass directory in the Folder
View: fpdblib.inc, fpdbrgn1.inc and fpdbrgn2.inc.

Open the files with Notepad, copy the respective code for
each file and do a Save As with the respective names and
the .inc extension back to your _fpclass folder. MAKE
SURE THAT YOU MAKE BACKUPS OF THE ORIGINAL FILES BEFORE
YOU ATTEMPT TO FIX THIS !!!!!

***DISCLAIMER***
YOU ARE DOING THIS AT YOUR OWN RISK AS I CAN'T AFFORD TO
BE RESPONSIBLE FOR YOUR WORK!!! I MAKE NO WARRANTY THAT
THIS IS DEFINITELY GOING TO WORK FOR YOU, BUT ALL I CAN
SAY IS THAT IT WORKED FOR ME AND MANY, MANY OTHERS.


fpdblib.inc

<%

Sub FP_SetLocaleForPage
On Error Resume Next
Session("FP_OldCodePage") = Session.CodePage
Session("FP_OldLCID") = Session.LCID
Err.Clear
if FP_CodePage <> 0 then
Session.CodePage = FP_CodePage
if Err.Number <> 0 then Session.CodePage = Session
("FP_OldCodePage")
end if
Err.Clear
if FP_LCID <> 0 then
Session.LCID = FP_LCID
if Err.Number <> 0 then Session.LCID = Session
("FP_OldLCID")
end if
End Sub

Sub FP_RestoreLocaleForPage
On Error Resume Next
if Session("FP_OldCodePage") <> 0 then
Session.CodePage = Session("FP_OldCodePage")
end if
if Session("FP_OldLCID") <> 0 then
Session.LCID = Session("FP_OldLCID")
end if
Err.Clear
End Sub

Function FP_HTMLEncode(str)

FP_HTMLEncode = str
FP_HTMLEncode = Replace(FP_HTMLEncode,"&","^^@^^")
FP_HTMLEncode = Server.HTMLEncode(FP_HTMLEncode)
FP_HTMLEncode = Replace(FP_HTMLEncode,"^^@^^","&")

End Function

Function FP_FieldVal(rs, fldname)

FP_FieldVal = FP_HTMLEncode(FP_Field(rs, fldname))
if FP_FieldVal = "" then FP_FieldVal = "&nbsp;"

End Function

Function FP_Field(rs, fldname)

If Not IsEmpty(rs) And Not (rs Is Nothing) and Not
IsNull(rs(fldname)) Then
Select Case rs(fldname).Type
Case 128, 204, 205 ' adBinary, adVarBinary,
adLongVarBinary
FP_Field = "[#BINARY#]"
Case 201, 203 ' adLongVarChar, adLongVarWChar
if rs(fldname).DefinedSize > 255 then
' check for Access hyperlink fields
(only absolute http links)
fp_strVal = rs(fldname)
fp_idxHash1 = InStr(LCase
(fp_strVal),"#http://")
if fp_idxHash1 > 0 then
fp_idxHash2 = InStr
(fp_idxHash1+1,fp_strVal,"#")
if fp_idxHash2 > 0 then
' this is an Access hyperlink;
extract the URL part
fp_strVal = Mid
(fp_strVal,fp_idxHash1+1)
if Right(fp_strVal,1) = "#"
then
fp_strVal = Left
(fp_strVal,Len(fp_strVal)-1)
end if
end if
end if
FP_Field = fp_strVal
else
FP_Field = rs(fldname)
end if
Case Else
FP_Field = rs(fldname)
End Select
Else
FP_Field = ""
End If

End Function

Function FP_FieldHTML(rs, fldname)

FP_FieldHTML = FP_HTMLEncode(FP_Field(rs, fldname))

End Function

Function FP_FieldURL(rs, fldname)

FP_FieldURL = Server.URLEncode(FP_Field(rs, fldname))

End Function

Function FP_FieldLink(rs, fldname)

FP_FieldLink = Replace(FP_Field(rs, fldname), " ", "%
20")

End Function

Sub FP_OpenConnection(oConn, sAttrs, sUID, sPWD,
fMSAccessReadOnly)

Dim sTmp
Dim sConnStr
Dim fIsAccessDriver

fIsAccessDriver = (InStr(LCase(sAttrs), "microsoft
access driver") > 0)
sConnStr = FP_RemoveDriverWithDSN(sAttrs)
sTmp = sConnStr

On Error Resume Next

If fMSAccessReadOnly And fIsAccessDriver Then

sTmp = sTmp & ";Exclusive=1;ReadOnly=1"

Err.Clear
oConn.Open sTmp, sUID, sPWD
If Err.Description = "" Then Exit Sub

End If

Err.Clear
oConn.Open sConnStr, sUID, sPWD

End Sub

Function FP_RemoveDriverWithDSN(sAttrs)

FP_RemoveDriverWithDSN = sAttrs

sDrv = "driver="
sDSN = "dsn="
sLC = LCase(sAttrs)
if InStr(sLC, sDSN) < 1 then exit function

idxFirst = InStr(sLC, sDrv)
if idxFirst < 1 then exit function
idxBeg = idxFirst + Len(sDrv)
if Mid(sLC,idxBeg,1) = "{" then
idxEnd = InStr(idxBeg, sLC, "}")
if idxEnd > 0 and Mid(sLC,idxEnd+1,1) = ";" then
idxEnd = idxEnd + 1
end if
else
idxEnd = InStr(idxBeg, sLC, ";")
end if
if idxEnd < 1 then idxEnd = Len(sLC)

FP_RemoveDriverWithDSN = Left(sAttrs,idxFirst-1) & Mid
(sAttrs,idxEnd+1)

End Function

Sub FP_OpenRecordset(rs)

On Error Resume Next
rs.Open

End Sub

Function FP_ReplaceQuoteChars(sQry)

Dim sIn
Dim sOut
Dim idx

sIn = sQry
sOut = ""

idx = InStr(sIn, "%%")

Do While (idx > 0)

sOut = sOut & Left(sIn, idx - 1)
sIn = Mid(sIn, idx + 2)
if (Left(sIn,1) = "%") And (Left(sIn,2) <> "%%")
then
sIn = Mid(sIn, 2)
sOut = sOut & "%"
end if
sOut = sOut & "::"

idx = InStr(sIn, "%%")
if idx > 0 then
sOut = sOut & Left(sIn, idx - 1)
sIn = Mid(sIn, idx + 2)
sOut = sOut & "::"
if (Left(sIn,1) = "%") And (Left(sIn,2)
<> "%%") then
sIn = Mid(sIn, 2)
sOut = sOut & "%"
end if
end if

idx = InStr(sIn, "%%")

Loop

sOut = sOut & sIn

FP_ReplaceQuoteChars = sOut

End Function

Sub FP_Close(obj)

On Error Resume Next

obj.Close

End Sub

Sub FP_SetCursorProperties(rs)

On Error Resume Next

rs.CursorLocation = 3 ' adUseClient
rs.CursorType = 3 ' adOpenStatic

End Sub

%>





fpdbrgn1.inc





<%

FP_SetLocaleForPage

' determine whether or not to provide navigation controls
if fp_iPageSize > 0 then
fp_fShowNavbar = True
else
fp_fShowNavbar = False
end if

fp_sPagePath = Request.ServerVariables("PATH_INFO")
fp_sEnvKey = fp_sPagePath & "#fpdbr_" & fp_iRegion
fp_sFormName = "fpdbr_" & CStr(fp_iRegion)
fp_sFormKey = fp_sFormName & "_PagingMove"

fp_sInputs = fp_sDefault

fp_sFirstLabel = " |< "
fp_sPrevLabel = " < "
fp_sNextLabel = " > "
fp_sLastLabel = " >| "
fp_sDashLabel = " -- "

if not IsEmpty(Request(fp_sFormKey)) then
fp_sMoveType = Request(fp_sFormKey)
else
fp_sMoveType = ""
end if

fp_iCurrent=1
fp_fError=False
fp_bBlankField=False
Set fp_dictInputs = Server.CreateObject
("Scripting.Dictionary")

fp_sQry = FP_ReplaceQuoteChars(fp_sQry)

' replace any input parameters in query string
Do While (Not fp_fError) And (InStr(fp_iCurrent,
fp_sQry, "::") <> 0)
' found a opening quote, find the close quote
fp_iStart = InStr(fp_iCurrent, fp_sQry, "::")
fp_iEnd = InStr(fp_iStart + 2, fp_sQry, "::")
If fp_iEnd = 0 Then
fp_fError = True
Response.Write "<B>Database Results Error:
mismatched parameter delimiters</B>"
Else
fp_sField = Mid(fp_sQry, fp_iStart + 2, fp_iEnd -
fp_iStart - 2)
fp_sValue = Request.Form(fp_sField)
if len(fp_sValue) = 0 then fp_sValue =
Request.QueryString(fp_sField)

' if the named form field doesn't exist, make a
note of it
If (len(fp_sValue) = 0) Then
fp_iStartField = InStr(fp_sDefault,
fp_sField & "=")
if fp_iStartField > 0 then
fp_iStartField = fp_iStartField + len
(fp_sField) + 1
fp_iEndField = InStr
(fp_iStartField,fp_sDefault,"&")
if fp_iEndField > 0 then
fp_sValue = Mid
(fp_sDefault,fp_iStartField,fp_iEndField - fp_iStartField)
else
fp_sValue = Mid
(fp_sDefault,fp_iStartField)
end if
end if
End If

' remember names and values used in query
if not fp_dictInputs.Exists(fp_sField) then
fp_dictInputs.Add fp_sField, fp_sValue
end if

' this next finds the named form field value,
and substitutes in
' doubled single-quotes for all single quotes in
the literal value
' so that SQL doesn't get confused by seeing
unpaired single-quotes
If (Mid(fp_sQry, fp_iStart - 1, 1) = """") Then
fp_sValue = Replace(fp_sValue, """", """""")
ElseIf (Mid(fp_sQry, fp_iStart - 1, 1) = "'")
Then
fp_sValue = Replace(fp_sValue, "'", "''")
End If

If (len(fp_sValue) = 0) Then fp_bBlankField =
True

fp_sQry = Left(fp_sQry, fp_iStart - 1) &
fp_sValue & Right(fp_sQry, Len(fp_sQry) - fp_iEnd - 1)

' Fixup the new current position to be after the
substituted value
fp_iCurrent = fp_iStart + Len(fp_sValue)
End If
Loop

' establish connection
If Not fp_fError Then
if Application(fp_sDataConn & "_ConnectionString")
= "" then
Err.Description = "The database connection
named '" & fp_sDataConn & "' is undefined.<br><br>"
Err.Description = Err.Description & "This
problem can occur if:<br>"
Err.Description = Err.Description & "* the
connection has been removed from the web<br>"
Err.Description = Err.Description & "* the
file 'global.asa' is missing or contains errors<br>"
Err.Description = Err.Description & "* the root
folder does not have Scripting permissions enabled<br>"
Err.Description = Err.Description & "* the web
is not marked as an Application Root<br>"
fp_fError = True
end if
if Not fp_fError then
set fp_conn = Server.CreateObject
("ADODB.Connection")
fp_conn.ConnectionTimeout = Application
(fp_sDataConn & "_ConnectionTimeout")
fp_conn.CommandTimeout = Application
(fp_sDataConn & "_CommandTimeout")
fp_sConn = Application(fp_sDataConn
& "_ConnectionString")
fp_sUid = Application(fp_sDataConn
& "_RuntimeUserName")
fp_sPwd = Application(fp_sDataConn
& "_RuntimePassword")
Err.Clear
FP_OpenConnection fp_conn, fp_sConn, fp_sUid,
fp_sPwd, Not(fp_fCustomQuery)
if Err.Description <> "" then fp_fError = True
end if
if Not fp_fError then
set fp_cmd = Server.CreateObject("ADODB.Command")
fp_cmd.CommandText = fp_sQry
fp_cmd.CommandType = fp_iCommandType
set fp_cmd.ActiveConnection = fp_conn
set fp_rs = Server.CreateObject
("ADODB.Recordset")
set fp_rs.Source = fp_cmd
If fp_iCommandType = 4 Then
fp_cmd.Parameters.Refresh
Do Until Len(fp_sInputs) = 0
fp_iLoc = InStr(fp_sInputs,"=")
if fp_iLoc = 0 then exit do
fp_sKey = Left(fp_sInputs,fp_iLoc - 1)
fp_sInputs = Mid(fp_sInputs,fp_iLoc +
1)
fp_iLoc = InStr(fp_sInputs,"&")
if fp_iLoc = 0 then
fp_sInpVal = fp_sInputs
fp_sInputs = ""
else
fp_sInpVal = Left
(fp_sInputs,fp_iLoc - 1)
fp_sInputs = Mid
(fp_sInputs,fp_iLoc + 1)
end if
fp_sVal = Request.Form(fp_sKey)
if len(fp_sVal) = 0 then fp_sVal =
Request.QueryString(fp_sKey)
if len(fp_sVal) = 0 then fp_sVal =
fp_sInpVal
fp_pType = fp_cmd.Parameters
(fp_sKey).Type
select case fp_pType
case 129, 200, 201, 130, 202,
203 ' adChar, adVarChar, adLongVarChar, adWChar,
adVarWChar, adLongVarWChar
fp_cmd.Parameters
(fp_sKey).Size = Len(fp_sVal) + 1
case else
' do nothing
end select

' remember names and values used in
query
if not fp_dictInputs.Exists(fp_sKey)
then
fp_dictInputs.Add fp_sKey, fp_sVal
end if

fp_cmd.Parameters(fp_sKey) = fp_sVal
Loop
End If
If fp_iMaxRecords <> 0 Then fp_rs.MaxRecords =
fp_iMaxRecords

FP_SetCursorProperties(fp_rs)

FP_OpenRecordset(fp_rs)
end if

If Err.Description <> "" Then
if fp_fTableFormat then
Response.Write "<tr><td colspan=" &
fp_iDisplayCols & " color=#000000 bgcolor=#ffff00>"
end if
Response.Write "<tt>"
Response.Write "<b>Database Results
Error</b><br>"
if Not fp_fError then
Response.Write "<i>Description:</i> " &
Err.Description & "<br>"
Response.Write "<i>Number:</i> " &
Err.Number & " (0x" & Hex(Err.Number) & ")<br>"
Response.Write "<i>Source:</i> " &
Err.Source & "<br>"
else
Response.Write Err.Description
end if
if fp_bBlankField Then
Response.Write "<br>One or more form fields
were empty."
Response.Write " You should provide default
values for all form fields that are used in the query."
End If
Response.Write "</tt>"
if fp_fTableFormat then
Response.Write "</td></tr>"
end if
fp_fError = True
Else
' Check for the no-record case
if fp_rs.State <> 1 then
fp_fError = True
Response.Write fp_sNoRecords
ElseIf fp_rs.EOF And fp_rs.BOF Then
fp_fError = True
Response.Write fp_sNoRecords
end if
end if
end if

' determine whether or not provider supports Absolute
Positioning
if not fp_fError then
if IsObject(fp_rs) and not(fp_rs.Supports
(&H00004000)) then
fp_iPageSize = 0
fp_fShowNavbar = False
end if
end if

' move to correct position in result set
if not fp_fError then

if fp_iPageSize > 0 then
fp_iAbsPage = 1
fp_sVal = Session(fp_sEnvKey)
if fp_sVal <> "" then
fp_iAbsPage = CInt(fp_sVal)
end if

fp_rs.PageSize = fp_iPageSize
if fp_iAbsPage > fp_rs.PageCount then
fp_iAbsPage = fp_rs.PageCount
fp_rs.AbsolutePage = fp_iAbsPage
if fp_rs.PageCount = 1 then fp_fShowNavbar =
False

select case fp_sMoveType
case ""
' do nothing
case fp_sFirstLabel
fp_rs.AbsolutePage = 1
case fp_sPrevLabel
if fp_rs.AbsolutePage > 1 then
fp_rs.AbsolutePage = fp_rs.AbsolutePage - 1
case fp_sNextLabel
if fp_rs.AbsolutePage <
fp_rs.PageCount then fp_rs.AbsolutePage =
fp_rs.AbsolutePage + 1
case fp_sLastLabel
fp_rs.AbsolutePage = fp_rs.PageCount
case else
' do nothing
end select

fp_iAbsPage = fp_rs.AbsolutePage
Session(fp_sEnvKey) = fp_iAbsPage
end if

end if

if fp_fError then fp_fShowNavbar = False

fp_iCount = 0
Do
if fp_fError then exit do
if fp_rs.EOF then exit do
if fp_iPageSize > 0 And fp_iCount >= fp_rs.PageSize
then exit do
if fp_iMaxRecords > 0 And fp_iCount >= fp_iMaxRecords
then
' MaxRecords didn't work; exit loop
fp_fShowNavbar = False
exit do
end if
%>




fpdbrgn2.inc





<%
' Close the loop iterating records
fp_iCount = fp_iCount + 1
fp_rs.MoveNext
Loop

if fp_fShowNavbar then
if fp_fTableFormat then
if fp_iDisplayCols < 1 then fp_iDisplayCols = 16
Response.Write "<TR><TD ALIGN=LEFT VALIGN=MIDDLE
COLSPAN=" & CStr(fp_iDisplayCols) & ">"
end if

Response.Write "<FORM NAME=""" & fp_sFormName & """
ACTION=""" & Replace(fp_sPagePath," ","%20") & """
TARGET=""_self"" METHOD=POST>"

if fp_iAbsPage > 1 then
fp_sType = "Submit"
fp_sLabel = fp_sFirstLabel
else
fp_sType = "Button"
fp_sLabel = fp_sDashLabel
end if
Response.Write "<NOBR><INPUT TYPE=" & fp_sType & "
NAME=""" & fp_sFormKey & """ VALUE=""" & fp_sLabel & """>"
if fp_iAbsPage > 1 then fp_sLabel = fp_sPrevLabel
Response.Write "<INPUT TYPE=" & fp_sType & " NAME="""
& fp_sFormKey & """ VALUE=""" & fp_sLabel & """>"
if fp_iAbsPage < fp_rs.PageCount then
fp_sType = "Submit"
fp_sLabel = fp_sNextLabel
else
fp_sType = "Button"
fp_sLabel = fp_sDashLabel
end if
Response.Write "<INPUT TYPE=" & fp_sType & " NAME="""
& fp_sFormKey & """ VALUE=""" & fp_sLabel & """>"
if fp_iAbsPage < fp_rs.PageCount then fp_sLabel =
fp_sLastLabel
Response.Write "<INPUT TYPE=" & fp_sType & " NAME="""
& fp_sFormKey & """ VALUE=""" & fp_sLabel & """>"

Response.Write " [" & fp_iAbsPage & "/" &
fp_rs.PageCount & "]</NOBR>"

' remember names and values used in query
for each fp_sKey in fp_dictInputs
fp_sVal = fp_dictInputs.Item(fp_sKey)
Response.Write "<INPUT TYPE=HIDDEN NAME=""" &
fp_sKey & """ VALUE=""" & fp_sVal & """>"
next

Response.Write "</FORM>"

if fp_fTableFormat then
Response.Write "</TD></TR>"
end if
end if

if IsObject(fp_rs) then
FP_Close(fp_rs)
FP_Close(fp_conn)
end if

set fp_dictInputs = Nothing

set fp_rs = Nothing
set fp_cmd = Nothing
set fp_conn = Nothing

FP_RestoreLocaleForPage

%>



GOOD LUCK TO ALL YOU OUT THERE!!

Kind regards,
John