searching a database

shabazzk · March 10th, 2004, 01:39 PM

I am writing some ASP, but I want to code in a search box that will accept a string from the user and retrieve the info from a SQL Server 2000 database with about 8 tables. Do I have to code it on the Sql side, ASP side or both? Wha would the code be?

planoie · March 10th, 2004, 03:07 PM

This is a pretty broad question.

You'll have to code it in both ASP and SQL. You need a form for the user to enter their search criteria, you'll need ASP to process that form and you'll have to write some SQL to look up the results from all tables you wish to search from.

Peter
------------------------------------------------------
Work smarter, not harder.

Toran · March 10th, 2004, 06:56 PM

you can create the form pretty fast in HTML you submit the form as POST or GET (your choice) In the page you submit to use Request.QueryString("fieldName") and sned the value to the database.

sample for Form:

<html>
<FORM NAME="tempQuery" ACTION="destinationPage.ASP" METHOD="Get">
    <h1>Your Headline</h1>

        I want to search for:<input type="text" NAME="searchString" VALUE="">
        <INPUT type="submit" value="Continue Next">
        </table>
</form>
</html>

sample for the destination:
<%option explicit%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<head>
<%
DIM searchString, objCommand, objRS, sqlStrDef
searchString = Request.QueryString("searchString")
%>
    <title> SearchReturn</title>

<%

%>
</head>

<body>
<%
sqlStrDef= "SELECT field1, field2, field3 FROM tableName WHERE field1 = '" & searchString & "'"
Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = strConn
objCommand.CommandText = sqlStrDef
'executing and resetting to Nothing
objCommand.CommandType = adCmdText
Set objRS = objCommand.Execute
Set objCommand = Nothing

objRS.movefirst
WHILE NOT objRS.eof

RESPONSE.WRITE "<h1>this is your return</h1>
RESPONSE.WRITE field1 & field2 & field3
%>
</body>
</html>

something like that shoudl work.

WATCHOUT for special characters especially the mean single quote!!!!

... there is always more to learn...