How do I replace parameters in a SQL Query

[VDG]

This is probably a newbie question but when programming in C++ I have always found it easier to dynamically create the SQL Query string rather than to use parameters. Now in MSFP I'm having troubles with both.

On my page I used the MSFP Database Results Wizard and I have my parameters in the Query as ::fUName:: and ::fPWord::, but my Query returns no records. My Query is:

fp_sQry="SELECT PRIVLEDGE FROM Users WHERE User_Name='::fUName::' AND PWord='::fPWord::'"

However, when I use the Query below I return 1 Record

fp_sQry="SELECT PRIVLEDGE FROM Users WHERE User_Name='Admin' AND PWord='admin'"

Obviously I'm am missing something, my Page code is below. Any help would be greatly appreciated.

Thanks in advance

Victor

<%
Dim fPrivledge
Dim fPWord
Dim fUName

fUName = Request.Form("Username")
fPWord = Request.Form("Password")
fPrivledge = 0
%>
<!--webbot bot="DatabaseRegionStart" s-columnnames="PRIVLEDGE,PWord,User_Name" s-columntypes="3,129,129" s-dataconnection="database" b-tableformat="FALSE" b-menuformat="FALSE" s-menuchoice="PRIVLEDGE" s-menuvalue="PRIVLEDGE" b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="FALSE" s-recordsource s-displaycolumns="PRIVLEDGE" s-criteria s-order s-sql="SELECT PRIVLEDGE FROM Users WHERE User_Name='::@fUName::' AND PWord='::@fPWord::'" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields="fUName=&amp;amp;fPWord=" s-norecordsfound="No Records Found" i-maxrecords="256" i-groupsize="0" botid="0" u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="BODY" preview="&lt;table border=0 width=&quot;100%&quot;&gt;&lt;tr&gt;&lt;td bgcolor=&quot;#FFFF00&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the start of a Database Results region. The page must be fetched from a web server with a web browser to display correctly; the current web is stored on your local disk or network.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;&lt;/table&gt;" startspan b-UseDotNET="FALSE" CurrentExt sa-InputTypes b-DataGridFormat="FALSE" b-DGridAlternate="TRUE" sa-CritTypes b-WasTableFormat="FALSE" --><!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT PRIVLEDGE FROM Users WHERE User_Name='::@fUName::' AND PWord='::@fPWord::'"
fp_sDefault="fUName=&fPWord="
fp_sNoRecords="No Records Found"
fp_sDataConn="database"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice="PRIVLEDGE"
fp_sMenuValue="PRIVLEDGE"
fp_sColTypes="&PRIVLEDGE=3&PWord=129&User_Name=129&"
fp_iDisplayCols=1
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="15177" -->       
<%
fPrivledge=FP_FieldVal(fp_rs, "PRIVLEDGE")
%>
<!--webbot bot="DatabaseRegionEnd" b-tableformat="FALSE" b-menuformat="FALSE" u-dbrgn2="_fpclass/fpdbrgn2.inc" i-groupsize="0" clientside tag="BODY" preview="&lt;table border=0 width=&quot;100%&quot;&gt;&lt;tr&gt;&lt;td bgcolor=&quot;#FFFF00&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the end of a Database Results region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;&lt;/table&gt;" startspan --><!--#include file="_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" endspan i-checksum="62730" -->
<%
Response.write "<p>Username = " & fUName & "</p>"
Response.write "<p>Password = " & fPWord & "</p>"
Response.write "<p>Privledge = " & fPrivledge & "</p>"
%>

 

[xwb]

Not sure about this one - try making the code generation VBScript instead of JScript/Javascript.

At a guess it is mixing the two.

 

[VDG]

Well, I fianally figured out ho to get this to work. It is really quite simple and I guess I missed somthing in the docs.

The SQL Parameters need to match the fields in the form that call the ".asp" file.

VDG