|
Include VBScript variables in ASP SQL statements
Often, you'll want to include a VBScript variable in an ASP SQL statement. For
example, suppose you want to let a user search a table for names that begin
with a letter they specify. To use a variable in a SQL statement, simply concatenate the
various parts of the statement together. To see how this works, create an ASP page with the
following code:
<HTML>
<BODY>
<FORM Name="test" Action="SQLVars.asp" Method="POST" >
<INPUT Name="txtSearch" TYPE="TEXT" Size=15
VALUE="SearchForMe">
<INPUT Type="SUBMIT" VALUE="Submit">
</FORM>
<%
Dim strSQL1
Dim strSQL2
Dim strSearch
strSearch = Request.Form("txtSearch")
If Len(strSearch) Then
strSQL1 = "Select From Customer(MyField) Where " _
& "Name(Col.Name) Like 'strSearch%'"
strSQL2 = "Select From Customer(MyField) Where " _
& "Name(Col.Name) Like '" & strSearch & "%'"
End If
%>
<UL>
<LI>Before concatenation: <%=strSQL1%>
<LI>After concatenation: <%=strSQL2%>
</UL>
</BODY>
</HTML>
When you run the page, only strSQL2 contains the variable's actual value, which
can then be sent on to SQL Server, or any other database that processes SQL
statements. Also, notice the use of the single quote before the double quote and
after the percent sign. This ensures that the database interprets the variable's value
as a string.
|
