Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Link ASP page to stored procedure 1

Status
Not open for further replies.
craigward

craigward

Programmer
Nov 13, 2007
230
GB
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>

 
Sort by date Sort by votes
The first step in this process is to create your first stored procedure. I assume you have access to SQL Server Management Studio. If so, load it and connect to your database. Then, open a new query window and copy/paste this:

Code: 
Create Procedure dbo.SearchCount
  @SearchCriteria VarChar(1000)
As
SET NOCOUNT ON

SELECT count(*) as count 
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 + '%'
           )

Notice that this stored procedure is named "SearchCount" and has a single parameter named @SearchCriteria. With stored procedures, all parameters and declared variables must start with the @ symbol. Also notice that string concatenation is slightly different between ASP and TSQL.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Thank you for that, i have that in there. What is the method for passing the ASP variable values to the variables in the procedure and then calling the result set back?

Really appreciate your help here.
 
Upvote 0 Downvote
Change this:

[tt]
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'))"
[/tt]

To

Code: 
    qry_count = "dbo.SearchCount '" & SearchCriteria & "'"

Please understand that this is just the next step in the process. We're not done yet. This step is just verifying that we can call the stored procedure from the ASP page. We still need to concern ourselves with SQL Injection, which will be the next (and final) step in the process.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
The next step in the process is to use the command object with parameters. This will safeguard you from SQL Injection attacks. The following code is an example. It may not work 100% as a drop in replacement for your code, but it should be a good start, and it may even work. I did not test this.

Please note that this is obviously more code than the original. This is to be expected because this code prevents SQL injection whereas your original does not.

Also note that I only showed you how to do this for one query (the count version). You should follow these steps for all database access on all of the pages in your website. I realize this may represent a ton of work, but it really should be done in order to protect your data.

Code: 
<%
search = ""
search = Request.QueryString("search")
SearchCriteria = Request.Form("directorysearch")
If Len(SearchCriteria) > 2 Then


   Set cmd = Server.CreateObject("ADODB.Command")
   Set cmd.ActiveConnection = connStr
   cmd.CommandText = "dbo.SearchCount"
   cmd.CommandType = adCmdStoredProc
   cmd.Parameters.Append cmd.CreateParameter("SearchCriteria", adVarChar, 1000, adParamInput)
   cmd("SearchCriteria") = SearchCriteria
   Set oRs_count = cmd.Execute

   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
%>

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Wow, thanks for that, sorry for my delayed reply I have been away today. I really was not expecting that much detail.

I have tried to apply it but because this logic is not what I am use to I am probably going to come across as very stupid :)

Your code mentions replacing

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'))"

with

Code: 
qry_count = "dbo.SearchCount '" & SearchCriteria & "'"

Should qry_count go in the other code you posted below? Or outside of that? Below you code example is what I have tried and the error I have got returned.

Code: 
<%
search = ""
search = Request.QueryString("search")
SearchCriteria = Request.Form("directorysearch")
If Len(SearchCriteria) > 2 Then


   Set cmd = Server.CreateObject("ADODB.Command")
   Set cmd.ActiveConnection = connStr
   cmd.CommandText = "dbo.SearchCount"
   cmd.CommandType = adCmdStoredProc
   cmd.Parameters.Append cmd.CreateParameter("SearchCriteria", adVarChar, 1000, adParamInput)
   cmd("SearchCriteria") = SearchCriteria
   Set oRs_count = cmd.Execute

   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
%>

Here is the entire code page and the error:

Thank you very much for you help, gret to learn new stuff, when it works i'm sure i will be able to link the logic together.

ADODB.Command error '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/DirectorySearch.asp, line 16

Line 16 is --- cmd.CommandType = adCmdStoredProc

Code: 
<!--#include file="include/connection.asp"-->

<%
search = ""
search = Request.QueryString("search")
SearchCriteria = Request.Form("directorysearch")


qry_count = "dbo.SearchCount '" & SearchCriteria & "'" 
If Len(SearchCriteria) > 2 Then


   Set cmd = Server.CreateObject("ADODB.Command")
   Set cmd.ActiveConnection = connStr
   cmd.CommandText = "dbo.SearchCount"
   cmd.CommandType = adCmdStoredProc
   cmd.Parameters.Append cmd.CreateParameter("SearchCriteria", adVarChar, 1000, adParamInput)
   cmd("SearchCriteria") = SearchCriteria
   Set oRs_count = cmd.Execute

   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
%> 
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>WiredContact 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>
 
Upvote 0 Downvote
Change:

cmd.CommandType = adCmdStoredProc

To

Code: 
cmd.CommandType = 4

adCmdStoredProc is an enumeration (with a value of 4) that probably doesn't exist in asp unless you include some special file.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
I changed that and got the following error. Maybe i have applied the code wrong from your example.

ADODB.Command error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/DirectorySearch.asp, line 17
 
Upvote 0 Downvote
For some strange reason, the line counts given by error messages start from 0. Check your cmd.Parameters line
 
Upvote 0 Downvote
I'm new to calling stored procedures and this code so i'm clueless im afraid. That line you referred to looks like:

Code: 
   cmd.Parameters.Append cmd.CreateParameter("SearchCriteria", adVarChar, 1000, adParamInput)

Thanks for looking.
 
Upvote 0 Downvote
I used this in the end to call my two stored procedures. Worked a treat.

Thanks for the help.

Code: 
<!--#include file="include/connection.asp"-->
<%
userSearch = ""
userSearch = Request.QueryString("search")
SearchCriteria = Request.Form("directorysearch")
If Len(SearchCriteria) > 2 Then

	sSql_count = "SearchCount " & SearchCriteria
	Set oRs_count = connStr.Execute(sSql_count) 
    count = (oRs_count("count"))

	sSql_result = "Search " & SearchCriteria
	Set oRs = connStr.Execute(sSql_result) 

	
	'response.end
Else
    search = ""
End If
%>
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
185
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
234
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
write HTML code within ASP line
Replies
1
Views
253
xwb
xwb
how2trainyourdragon
  • Locked
  • Question
Request.Server variables blank for classic ASP
Replies
12
Views
464
GriffMG
GriffMG
bslintx2021
  • Locked
  • Question
How to configure IIS 10 server to allow Classic ASP to connect to MSSQL server database on a domain
Replies
1
Views
477
penguinspeaks
penguinspeaks

Part and Inventory Search

Sponsor

Back
Top