#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2007
    Posts
    62
    Rep Power
    7

    Question 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>&nbsp;</td></tr>
    		<tr><td class=lbl align=right colspan=2>Search directory &nbsp; <input type=text name="directorysearch"> &nbsp; <input type="button" onclick="checkfield()" value="Click to Search"></td></tr>
    		<tr><td colspan=2>&nbsp;</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>&nbsp;</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>
  2. #2
  3. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,430
    Rep Power
    4539
    One way is to use the sql EXEC statement, or you can use a command object.
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester

IMN logo majestic logo threadwatch logo seochat tools logo