- Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
- FrontPage 2003
I have two tables: tblLOCAL and tblNATIONAL. Each table has the same fields and have no relationships. Tables have to remain separate for updating purposes.
I am trying to generate a report using a form searching for ALL Titles begning with a selected letter (A-Z or 0-9). i am using the UNION ALL to join the 2 tables. I want all records from both tables even if duplicates. I am modifying code that is working for a keyword search. The keyword search is searching for a character string (WHERE Title LIKE '%" & StrKeyword & "%'").
In this case I want to select only records (from either table) where the TITLE begins with a selected letter.
Code attached is from 2 different files. First part is the code which will give the user the option to select the letter (default.asp). The 2nd part of the code (AlphaResults.asp) is the SQL statement selecting the records.
This is the last problem with this project. Thanks for your help!
<!-- Following is the drop-down ALPHABET option to search for title beginning with -->
<FORM METHOD="POST" name="AlphaList" ACTION="AlphaResults.asp">
Select <b>LETTER</b> to list titles beginning with:
<select name = "AlphaList">
<option value=''>List All</option>
<input type="submit" value="Select Letter">
<input type="reset" value="Clear">
Dim objRS, StrAlphaList, sql
StrAlphaList = request.form("AlphaList")
sql = "SELECT * FROM (SELECT * FROM tblNational " & _
"UNION ALL " & _
"SELECT * FROM tblLocal) AS T1 "
If StrAlphaList <> "" then
Sql = sql & "WHERE Title LIKE 'StrAlphaList & "%'" & _
" AND ActiveTitle = 'Yes' "
If Request.querystring("sort") = "" then
sql = sql & "ORDER BY Title"
sql = sql & "ORDER BY " & Request.querystring("sort")
'Diagnostic for sql statement errors
'response.write "SQL statement:<font color='red'>" & sql & "</font><p>"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open sql, objConn
'Determine if there are results
If objRS.EOF then
'If no records
Response.Write "There are no individuals listed for the " & _
Ucase(prog) & "AlphaList"
While Not objRS.EOF