INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Link ASP page to stored procedure

Link ASP page to stored procedure

(OP)
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>

RE: Link ASP page to stored procedure

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

RE: Link ASP page to stored procedure

(OP)
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.

RE: Link ASP page to stored procedure

Change this:


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


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

RE: Link ASP page to stored procedure

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

RE: Link ASP page to stored procedure

(OP)
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 smile

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

RE: Link ASP page to stored procedure

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

RE: Link ASP page to stored procedure

(OP)
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

RE: Link ASP page to stored procedure

For some strange reason, the line counts given by error messages start from 0. Check your cmd.Parameters line

RE: Link ASP page to stored procedure

(OP)
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.

RE: Link ASP page to stored procedure

Looks like the parameters have been switched. Have a look at http://www.w3schools.com/ado/met_comm_createparameter.asp#parameterdirenum

At a guess it is

CODE

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

RE: Link ASP page to stored procedure

(OP)
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 %>

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close