Search Search

#1 worldwide
FREE Coding Lessons
since 1996
   THE BEST WAY to learn ASP & Asp.net!
Advertise Here!
click for details 		Credits Host:
DiscountASP.net 		Server Admin:
The "Team" 		Contact Info.
Charles M. Carroll
my Blog
[prev. Lesson]   SQL: Where Clause Basics
     [next Lesson]  SQL: Search Forms #1

SQL Where Examples by Charles Carroll

Here are some where statements and their results.

   filename=/learn/test/SQLwhere1.asp

<Test Script Below>


<HEAD><TITLE>SQLwhere1.asp</TITLE></HEAD>
<HTML><body bgcolor="#FFFFFF">
<%
call query2table("select * from publisher where name like 'A%%'")
%>
<!--#include virtual="/learn/test/subdbtable.inc"-->
</BODY></HTML>

   filename=/learn/test/SQLwhere2.asp

<Test Script Below>


<HEAD><TITLE>SQLwhere2.asp</TITLE></HEAD>
<HTML><body bgcolor="#FFFFFF">
<%
call query2table("select * from titles where Year_Published >= 1994")
%>
<!--#include virtual="/learn/test/subdbtable.inc"-->
</BODY></HTML>

   filename=/learn/test/SQLwhere3.asp

<Test Script Below>


<HEAD><TITLE>SQLwhere3.asp</TITLE></HEAD>
<HTML><body bgcolor="#FFFFFF">
<%
call query2table("select * from publishers where fax like '212%%'")
%>
<!--#include virtual="/learn/test/subdbtable.inc"-->
</BODY></HTML>

   filename=/learn/test/SQLwhere4.asp

<Test Script Below>


<HEAD><TITLE>sqlwhere4.asp</TITLE></HEAD>
<HTML><body bgcolor="#FFFFFF">
<%
call query2table("select * from publishers where state<> 'NY'")
%>
<!--#include virtual="/learn/test/subdbtable.inc"-->
</BODY></HTML>

The Include file looks like this:

   filename=/learn/test/subdbtable.inc

<Test Script Below>


<% 
sub query2table(inputquery)
    set conntemp=server.createobject("adodb.connection")
    conntemp.open "DSN=Student;uid=student;pwd=magic"
    set rstemp=conntemp.execute(inputquery)
    howmanyfields=rstemp.fields.count -1%>
    <table border=1><tr>
    <% 'Put Headings On The Table of Field Names
    for i=0 to howmanyfields %>
            <td><b><%=rstemp(i).name%></B></TD>
    <% next %>
    </tr>
    <% ' Now lets grab all the records
    do while not rstemp.eof %>
        <tr>
        <% for i = 0 to howmanyfields
            thisvalue=rstemp(i)
            If isnull(thisvalue) then
                thisvalue="&nbsp;"
            end if%>
            <td valign=top><%=thisvalue%></td>
        <% next %>
        </tr>
        <%rstemp.movenext
    loop%>
    </table>
    <%
    rstemp.close
    set rstemp=nothing
    conntemp.close
    set conntemp=nothing
end sub%>

There are many worthy charities!!. But perhaps help starving children in Africa or South America AND help Charles too. a $5 tip buys him lunch at McDonalds, a $20 tip buys his kid Hitoshi a new computer game, a $39 tip buys his daughter Michiko a few nice outfits. See our donor list.