basic ASP, ADO question

[DMS]

I am trying to build a simple page in classic ASP code
that queries a SQL table based on user input. I have
sample code, but it isn't quite what I want...
On page init I don't want a listing of all the records for
LastName in Authors (Pubs), but it is listing them. I can
move the list to the top of the page or the bottom of the
page, but I don't know how to get rid of the list, but
still return a record when the user enters a name and
clicks submit. Would appreciate any help or samples for
this. I just took ASP.NET class, easy to do there because
of the events page_load and button_click; but I don't know
classic ASP.
Here is the VB and Form code I have:

Dim adOpenForwardOnly, adLockReadOnly, adCmdTable
adOpenForwardOnly = 0
adLockReadOnly = 1
adCmdTable = 2

Dim objConn, objRS, strName
set objConn = Server.CreateObject("ADODB.Connection")
set objRS = Server.CreateObject("ADODB.Recordset")

objConn.Open "DSN=mydatabase" 'connects to Pubs
Dim strDatabaseType
strDatabaseType = "SQL"
Dim strSQL ' SQL string
strSQL = "SELECT LastName, FirstName FROM Employees"
dim lngRecs
<h1>Find Author</h1>
<form name=NameInfo action="FindByName.asp" method="post">
Enter Last name for author:
<input type="text" name="Name"><br><br>
<br> <INPUT TYPE=SUBMIT VALUE="Find"> <input
type=reset value="Clear">
</form>
<%
If Request.Form("Name") <> "" Then
strName = Request.Form("Name")
strSQL = strSQL & " WHERE LastName LIKE '" & Request.Form
("Name") & "'"
End If

Set objRS = objConn.Execute(strSQL, lngRecs, 1) 'adCmdText
%>
'Create the ordered list & cleanup
<% Do While Not objRS.EOF %>
<LI><% = objRS.Fields("LastName") %></LI>
<% objRS.MoveNext
Loop %>
<%
set objConn = Nothing
set objRS = Nothing
%>
</body>
</html>

[Greg Griffiths]

Add a response.write strSQL to see what SQL you are actually running
before you run it on the server, to see if there is anything odd in it.

DMS wrote:

> I am trying to build a simple page in classic ASP code
> that queries a SQL table based on user input. I have
> sample code, but it isn't quite what I want...
> On page init I don't want a listing of all the records for
> LastName in Authors (Pubs), but it is listing them. I can
> move the list to the top of the page or the bottom of the
> page, but I don't know how to get rid of the list, but
> still return a record when the user enters a name and
> clicks submit. Would appreciate any help or samples for
> this. I just took ASP.NET class, easy to do there because
> of the events page_load and button_click; but I don't know
> classic ASP.
> Here is the VB and Form code I have:
>
> Dim adOpenForwardOnly, adLockReadOnly, adCmdTable
> adOpenForwardOnly = 0
> adLockReadOnly = 1
> adCmdTable = 2
>
> Dim objConn, objRS, strName
> set objConn = Server.CreateObject("ADODB.Connection")
> set objRS = Server.CreateObject("ADODB.Recordset")
>
> objConn.Open "DSN=mydatabase" 'connects to Pubs
> Dim strDatabaseType
> strDatabaseType = "SQL"
> Dim strSQL ' SQL string
> strSQL = "SELECT LastName, FirstName FROM Employees"
> dim lngRecs
> <h1>Find Author</h1>
> <form name=NameInfo action="FindByName.asp" method="post">
> Enter Last name for author:
> <input type="text" name="Name"><br><br>
> <br> <INPUT TYPE=SUBMIT VALUE="Find"> <input
> type=reset value="Clear">
> </form>
> <%
> If Request.Form("Name") <> "" Then
> strName = Request.Form("Name")
> strSQL = strSQL & " WHERE LastName LIKE '" & Request.Form
> ("Name") & "'"
> End If
>
> Set objRS = objConn.Execute(strSQL, lngRecs, 1) 'adCmdText
> %>
> 'Create the ordered list & cleanup
> <% Do While Not objRS.EOF %>
> <LI><% = objRS.Fields("LastName") %></LI>
> <% objRS.MoveNext
> Loop %>
> <%
> set objConn = Nothing
> set objRS = Nothing
> %>
> </body>
> </html>