May 16th, 2012, 04:31 AM
|
|
Contributing User
|
|
Join Date: Nov 2007
Posts: 62
Time spent in forums: 17 h 56 m 34 sec
Reputation Power: 6
|
|
Linking ASP to stored procedure
Hi There,
I have written an ASP page that links with an SQL database. A user can enter a value in a single field and search the database, when results are found they are returned in the asp code.
The code all works fine and does what i need but i am concerned that having the SQL in the same page as the ASP might be a little un-safe. I was wondering what would be the best method to hide the SQL code from the ASP page, maybe an include file? or better still a stored procedure.
Does anyone have an idea how i could integrate a stored procedure into my code below, i've looked but never havign done one before it's not that straight forward.
Many thanks for looking.
The SQL and entire ASP code is below.
SQL CODE:
Code:
<!--#include file="include/connection.asp"-->
<%
search = ""
search = Request.QueryString("search")
SearchCriteria = Request.Form("directorysearch")
If Len(SearchCriteria) > 2 Then
qry_count = "SELECT count(*) as count FROM wce_contact where ((company like '%"&SearchCriteria&"%' OR type_of_business like '%"&SearchCriteria&"%' OR Business_Overview like '%"&SearchCriteria&"%' OR services like '%"&SearchCriteria&"%') AND (General_Public_Facing = 'y'))"
Set oRs_count = connStr.Execute(qry_count)
count = (oRs_count("count"))
qry = "SELECT * FROM wce_contact WHERE((General_Public_Facing = 'y') AND (company LIKE '%"&SearchCriteria&"%' OR type_of_business LIKE '%"&SearchCriteria&"%' OR Business_Overview LIKE '%"&SearchCriteria&"%' OR services LIKE '%"&SearchCriteria&"%'))"
Set oRs = connStr.Execute(qry)
Else
search = ""
End If
%>
Entire Code:
Code:
<!--#include file="include/connection.asp"-->
<%
search = ""
search = Request.QueryString("search")
SearchCriteria = Request.Form("directorysearch")
qry_count = "SELECT count(*) as count FROM wce_contact where ((company like '%"&SearchCriteria&"%' OR type_of_business like '%"&SearchCriteria&"%' OR Business_Overview like '%"&SearchCriteria&"%' OR services like '%"&SearchCriteria&"%') AND (General_Public_Facing = 'y'))"
Set oRs_count = connStr.Execute(qry_count)
count = (oRs_count("count"))
'response.write(count)
'response.end
qry = "SELECT * FROM wce_contact WHERE((General_Public_Facing = 'y') AND (company LIKE '%"&SearchCriteria&"%' OR type_of_business LIKE '%"&SearchCriteria&"%' OR Business_Overview LIKE '%"&SearchCriteria&"%' OR services LIKE '%"&SearchCriteria&"%'))"
'response.write(qry)
Set oRs = connStr.Execute(qry)
'business_name = (oRs("company"))
'response.write(lup)
'response.end
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title> Portal</title>
<link type="text/css" href="portal/css/stylesheet.css" rel="stylesheet">
<script language="">
function checkfield(){
if(document.forms[0].directorysearch.value == ""){
alert("Please enter a search value");
}
else {
document.forms[0].submit();
}
}
</script>
</head>
<form name="DirectorySearch" method="post" action="DirectorySearch.asp?search=yes">
<body>
<table border=0>
<tr><td colspan=2> </td></tr>
<tr><td class=lbl align=right colspan=2>Search directory <input type=text name="directorysearch"> <input type="button" onclick="checkfield()" value="Click to Search"></td></tr>
<tr><td colspan=2> </td></tr>
<% If (search <> "") Then %>
<% If (oRs_count("count")) => "1" Then %>
<% Do while not oRs.eof %>
<tr><td class=lbl>Business Name</td>
<td class=Text> <% response.write(oRs("company")) %></td></tr>
<% If (oRs("Name_Enfield_Business_Directory")) = "y" Then %> <tr><td class=lbl>Salutation</td>
<td class=Text> <% response.write(oRs("Salutation")) %></td></tr> <% Else %> <% End If %>
<% If (oRs("Name_Enfield_Business_Directory")) = "y" Then %> <tr><td class=lbl>First Name</td>
<td class=Text> <% response.write(oRs("firstname")) %></td></tr> <% Else %> <% End If %>
<% If (oRs("Name_Enfield_Business_Directory")) = "y" Then %> <tr><td class=lbl>Last Name</td>
<td class=Text> <% response.write(oRs("lastname")) %></td></tr> <% Else %> <% End If %>
<% If (oRs("address_Enfield_Business_Directory")) = "y" Then %> <tr><td class=lbl>Address</td>
<td class=Text> <% response.write(oRs("Building_Name_Flat_Number")) %>, <% response.write(oRs("unit_number")) %>, <% response.write(oRs("Estate_or_Business_Centre")) %>, <% response.write(oRs("address1")) %>, <% response.write(oRs("address2")) %>, <% response.write(oRs("city")) %>, <% response.write(oRs("county")) %>, <% response.write(oRs("postalcode")) %></td></tr> <% Else %> <% End If %>
<% If (oRs("mobile_Enfield_Business_Directory")) = "y" Then %> <tr><td class=lbl>Mobilephone</td>
<td class=Text> <% response.write(oRs("mobilephone")) %></td></tr> <% Else %> <% End If %>
<tr><td class=lbl>Telephone</td>
<td class=Text><% response.write(oRs("phone")) %></td></tr>
<tr><td class=lbl>08 numbers</td>
<td class=Text><% response.write(oRs("Zero_Eight_Numbers")) %></td></tr>
<tr><td class=lbl>Type of Business</td>
<td class=Text><% response.write(oRs("type_of_business")) %></td></tr>
<tr><td class=lbl>Generic Email</td>
<td class=Text><% response.write(oRs("Generic_Email")) %></td></tr>
<tr><td class=lbl>Website</td>
<td class=Text> <% response.write(oRs("website")) %></td></tr>
<tr><td class=lbl>Business Overview</td>
<td class=Text><% response.write(oRs("Business_Overview")) %> </td></tr>
<tr><td class=lbl>Services</td>
<td class=Text><% response.write(oRs("services")) %> </td></tr>
<tr><td colspan=2> </td></tr>
<tr><td colspan=2><hr></td></tr>
<%
oRs.movenext
Loop
oRs.Close
Else
lup = ""
SearchCriteria = ""
oRs.Close
End If
lup = ""
SearchCriteria = ""
'End If
Else
End If
%>
</table>
</form>
</body>
</html>
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
