How to execute a stored procedure using ASP and passing a value to the stored proc

Hi all,

I'm really new to ASP and need to try to figure out how to do the following;

I have a stored procedure called "sp_intellisearch" located in SQL server.  its basically a stored proc that joins mutliple tables and allow you to search using wildcard characters.

I need to make an ASP page that has
- one text box (search box)
- one search button (search button)
- one listbox (listed results)
- A user can type any character or string in the search box, lets say the user types character 'a' in the text box(search box) and then clicks on the search button.  The 'a'  gets passed to the stored procedure, the stored procedure ("sp_intellisearch") does its thing and all the returned results are listed in an listbox(listed results) on the ASP page.

can anyone provide a sample on how to do this?

again i have very knowledge in asp.

thanks in advance,
lat

 

latzo2Asked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
Providing you have your ADO constants defined, the following should work for you:

Dim cn, cmd, rs

Set cn = Server.CreateObject("ADODB.Connection")
With cn
   .ConnectionString = "uor connection string goes here"
   .Open
End With

Set cmd = Server.CreateObject("ADODB.Command")
With cmd
   Set .ActiveConnection = cn
   .CommandText = "sfa_Intellisearch"
   .CommandType = adCmdStoredProc
   .Parameters.Append .CreateParameter("@UserID", adGUID, adParamInput, 0, "{6FF5F8BD-26D8-42A9-9654 3CA8021739DA}")
   .Parameters.Append .CreateParameter("@OrgName", adVarWChar, adParamInput, 100, "A")
   .Parameters.Append .CreateParameter("@RelationshipIDs", adVarWChar, adParamInput, 4000)
   .Parameters.Append .CreateParameter("@FilterStatus", adInteger, adParamInput, 0, 0)
   .Parameters.Append .CreateParameter("@RelatedOrgsOnly", adBoolean, adParamInput, 0)
   .Parameters.Append .CreateParameter("@OrgID", adGUID, adParamInput, 0)
   .Parameters.Append .CreateParameter("@TraceUp", adBoolean, adParamInput, 0)
   Set rs = .Execute
End With
Set cmd = Nothing

' Your code goes here


rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
0
 
DesertWarriorCommented:
Hi latzo2,

here are a couple examples I found on the web that you could check out :

http://www.asp101.com/samples/viewasp.asp?file=db_search.asp
http://www.learnasp.com/learn/SQLandor.asp
http://www.15seconds.com/issue/010423.htm


Cheers!
0
 
anderson22Commented:
First, never use sp_ in a stored procedure name. This is a reserved prefix for system procedures.  What happens when you use one is that the server will look through every procedure in the system before looking through your current scope (the database you are actually using).  Not a big deal but something that will cost you a few milli seconds of processing (I used this scheme myself for over a year before I ran over an article in a SQL magazine discussing the topic).

Here is a blurp of ASP code using a MSSQL stored procedure.  The bad news is that if you want to return a recordset and an output variable from ASP 3.0, you have to actually make two calls to the stored procedure.

--- START ---
Set objCommand      = Server.CreateObject("ADODB.Command")
                  
Set objCommand.ActiveConnection = objConn
objCommand.CommandText = "getAlias"
objCommand.CommandType = adCmdStoredProc

objCommand.Parameters.Append objCommand.CreateParameter("@ALI_ID",            adInteger, adParamInput,      4,      strAliasID)

objCommand.Execute


Set objCommand = nothing
--- STOP ---

Also, you will need the ADO library for some of the constants.  Just make sure your data access is up to date (microsoft.com/data) and insert the following on the second line in your global.asa:

--- START ---
<!--METADATA TYPE="TypeLib" NAME="Microsoft ActiveX Data Objects 2.6 Library" UUID="{00000206-0000-0010-8000-00AA006D2EA4}" VERSION="2.6" -->
--- STOP ---

-rca
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
SlimshaneeyCommented:
I use this in asp. It is really quick and very easy.

'Create you connection to sql server, lets call it objconn. On the DB you have  a storedprocedure         ' that accepts 2 params, called myproc

'call your stored procedure and pass each param like this, the last parameter then is a recordset to retreive the values

dim objrs
set objrs = server.createobject("adodb.recordset")

'this is the execution
objconn.myproc param1value, param2value, objrs

This scales for however many parameters there are. If there are no parameters, just pass the recordset asa  paramaeter, if there are 12 parameters, pass all 12 in order and the the rs. Easy!
0
 
latzo2Author Commented:
Thanks i'll try these an let you know how it goes.
0
 
Anthony PerkinsCommented:
anderson22,

>>The bad news is that if you want to return a recordset and an output variable from ASP 3.0, you have to actually make two calls to the stored procedure.<<
This is not necessary.  Just use CursorLocation = adUseClient
0
 
latzo2Author Commented:
I'm dead in the water here, the stored procedure i'm using accepts parameters as

in query analyzer:
exec sfa_Intellisearch '{6FF5F8BD-26D8-42A9-9654 3CA8021739DA}', 'A', NULL, '0'


how do i get this to execute in the asp page and display the results? Do you want me to provide the stored proc?

thanks

0
 
anderson22Commented:
You should probably use a numeric data type for the index column instead of a GUID.  At that point, use the Paramenters.Append to send input parameters (adParamInput) and then specify the name of the parameter (@ALI_ID, etc.) the type (adInteger) the size (4) and then the actual value (strAliasID, "", etc.):

objCommand.Parameters.Append objCommand.CreateParameter("@ALI_ID",          adInteger, adParamInput,     4,     strAliasID)

-rca
0
 
Anthony PerkinsCommented:
>>Do you want me to provide the stored proc?<<
Yes.
0
 
latzo2Author Commented:
'A' is the wildcard and retuns all companies that begin with the letter 'a'
id like to put this in a textbox on an asp form

exec sfa_Intellisearch '{6FF5F8BD-26D8-42A9-9654 3CA8021739DA}', 'A', NULL, '0'
=================================================


CREATE PROCEDURE sfa_Intellisearch
(
      @UserID             uniqueidentifier,
      @OrgName             nvarchar(100) = '',
      @RelationshipIDs       nvarchar(4000) = '',
      @FilterStatus            int = 1,
      @RelatedOrgsOnly      bit = 0, --Meaning visible orgs only.
      @OrgID                  uniqueidentifier = NULL, --Used only if @RelatedOrgsOnly = 1
      @TraceUp            bit = 1 --Used only if @RelatedOrgsOnly = 1
)
AS
      SET NOCOUNT ON

      CREATE TABLE #Rel( RelationshipID uniqueidentifier )
      CREATE TABLE #RelatedOrgs( OrgID uniqueidentifier )

      IF( @RelationshipIDs IS NULL )
            SET @RelationshipIDs = ''

      IF( @RelationshipIDs <> '' )
            EXEC( 'INSERT INTO #Rel( RelationshipID ) SELECT LookupCodeID FROM LookupCodes WHERE LookupCodeID IN( ' + @RelationshipIDs + ' ) AND CodeType = ''ORG_RELATIONSHIP'' ' )
      ELSE
            INSERT INTO #Rel( RelationshipID ) SELECT LookupCodeID FROM LookupCodes WHERE CodeType = 'ORG_RELATIONSHIP'

      IF( @RelatedOrgsOnly = 1 )
      BEGIN
            --Get companies related to the OrgID passed...
            INSERT INTO #RelatedOrgs EXEC sfa_all_ListViewableCompaniesMP @UserID, @RelationshipIDs, @OrgID, @FilterStatus, @TraceUp, 0

            DECLARE @UserOrgID uniqueidentifier
            SELECT @UserOrgID = OrgID FROM PeopleOrg WHERE PeopleID = @UserID
            IF( @UserOrgID <> @OrgID )
            BEGIN
                  CREATE TABLE #OCRelatedOrgs( OrgID uniqueidentifier )
                  INSERT INTO #OCRelatedOrgs EXEC sfa_all_ListViewableCompaniesMP @UserID, @RelationshipIDs, @UserOrgID, @FilterStatus, 1, 0
                  DELETE FROM #RelatedOrgs WHERE #RelatedOrgs.OrgID NOT IN( SELECT #OCRelatedOrgs.OrgID FROM #OCRelatedOrgs )
                  DROP TABLE #OCRelatedOrgs
            END
      END

      SELECT DISTINCT
            OrgID =       Organization.OrgID,
            OrgName =       ISNULL( Organization.OrgName, N'' ),
            Street =       ISNULL( Address.Addr1, '' ),
            City =             ISNULL( Address.City, '' ),
            StateID =       Address.State_Prov,
            State =             ISNULL( State.Abbreviation, '' ),
            Zip =             ISNULL( Address.Postal, '' ),
            CountryID =       Address.Country,
            Country =       ISNULL( Country.Abbreviation, '' ),
            RelationshipID =      Organization.Relationship,
            Relationship =       ISNULL( LURelationship.CodeID, '' ),
            StatusID =       Organization.Status,
            Status =       ISNULL( LUStatus.CodeID, '' ),
            TeamID =       Organization.TeamID,
            Visible =       CASE       WHEN ( Organization.NamedTeamID IS NULL ) THEN 1
                              WHEN ( EXISTS( SELECT * FROM PeopleTeam INNER JOIN
                                    SubordinateList ON ( PeopleTeam.TeamID IN ( Organization.NamedTeamID, Organization.TeamID ) ) AND
                                          ( SubordinateList.PeopleID = @UserID ) AND
                                          ( PeopleTeam.PeopleID = SubordinateList.SubordinateID ) ) ) THEN 1
                              ELSE 0 END,
            PhoneNumber =      ISNULL( ( SELECT TOP 1
                              Phone = ISNULL( '+' + LTRIM(RTRIM( CountryCode.CountryCode )) + ' ', '' ) + CASE WHEN Phone.AreaCode IS NOT NULL AND Phone.AreaCode <> '' THEN '(' + LTRIM(RTRIM( Phone.AreaCode )) + ') ' ELSE '' END + CASE WHEN Phone.UnparsedNumber IS NOT NULL AND Phone.UnparsedNumber <> '' THEN LTRIM(RTRIM( Phone.UnparsedNumber )) ELSE '' END + CASE WHEN PeopleOrgPhone.ExtNumber IS NOT NULL AND PeopleOrgPhone.ExtNumber <> '' THEN 'X-' + LTRIM(RTRIM( PeopleOrgPhone.ExtNumber )) ELSE '' END
                        FROM
                              PeopleOrgPhone INNER JOIN
                              Phone ON ( PeopleOrgPhone.PhoneID = Phone.PhoneID ) AND
                                    ( PeopleOrgPhone.PhoneType = CAST( '{4EAF18EC-D5AE-11D3-B7F6-0090278625F6}' AS uniqueidentifier ) ) LEFT OUTER JOIN
                              CountryCode ON ( Phone.CountryCode = CountryCode.CountryCodeID )
                        WHERE
                              ( PeopleOrgPhone.OrgID = Organization.OrgID ) AND ( PeopleOrgPhone.PeopleID IS NULL ) ), '' ),
            Email = ISNULL( ( SELECT TOP 1 EContact.eAddress FROM PeopleOrgEContact INNER JOIN EContact ON PeopleOrgEContact.EContactID = EContact.EContactID WHERE PeopleOrgEContact.PeopleID IS NULL AND PeopleOrgEContact.OrgID = Organization.OrgID AND PeopleOrgEContact.eContactType = CAST( '{4EAF18D8-D5AE-11D3-B7F6-0090278625F6}' AS uniqueidentifier ) ), '' ) --ECONTACT_TYPE E-Mail1
      FROM
            Organization INNER JOIN
            PeopleOrgAddress ON ( PeopleOrgAddress.OrgID = Organization.OrgID ) AND
                  ( Organization.Active = 1 ) AND
                  ( PeopleOrgAddress.PrimaryAdd = 1 ) INNER JOIN
            Address ON ( PeopleOrgAddress.AddressID = Address.AddressID ) LEFT OUTER JOIN
            LookupCodes AS LURelationship ON ( Organization.Relationship = LURelationship.LookupCodeID ) LEFT OUTER JOIN
            State ON ( Address.State_Prov = State.StateID ) LEFT OUTER JOIN
            Country ON ( Address.Country = Country.CountryID ) LEFT OUTER JOIN
            LookupCodes AS LUStatus ON ( Organization.Status = LUStatus.LookupCodeID )
      WHERE
            ( Organization.Relationship IN( SELECT #Rel.RelationshipID FROM #Rel ) ) AND
            (       ( ( @OrgName <> '' ) AND ( Organization.OrgName LIKE @OrgName + '%' ) ) OR
                  ( @OrgName = '' ) ) AND
            (       ( ( @FilterStatus = 1 ) AND ( Organization.Status <> '{D0B9D4B5-0267-11D3-9FDA-00A0C9AC2E56}' ) ) OR --ORGANIZATION_STATUS Inactive
                  ( @FilterStatus = 0 ) ) AND
            (       ( ( @RelatedOrgsOnly = 1 ) AND ( Organization.OrgID IN( SELECT #RelatedOrgs.OrgID FROM #RelatedOrgs ) ) ) OR
                  ( @RelatedOrgsOnly = 0 ) )
      ORDER BY
            Visible DESC, OrgName, State, City, Relationship

      DROP TABLE #Rel
      DROP TABLE #RelatedOrgs

      SET NOCOUNT OFF



GO
0
 
anderson22Commented:
use the code from my first post except update the following line:

---
objCommand.Execute
---

to:

---
Set objRs = Server.CreateObject("ADODB.RecordSet")
Set objRs = objCommand.Execute
---

then, you can loop through the recordset

---
do while not objRs.EOF
     Response.Write objRs.Fields("COLUMN_NAME").Value
     objRs.movenext
loop
---


-rca
0
 
Anthony PerkinsCommented:
anderson22,

>>Set objRs = Server.CreateObject("ADODB.RecordSet")<<
Sorry to be so critical, but this line is not necessary.  The Connection Execute method instantiates the Recordset object.
0
 
latzo2Author Commented:
hi anderson22,

How do fit this:

exec sfa_Intellisearch '{6FF5F8BD-26D8-42A9-9654 3CA8021739DA}', 'A', NULL, '0'

                                           into

objCommand.Parameters.Append objCommand.CreateParameter("@ALI_ID",          adInteger, adParamInput,     4,     strAliasID)


do i have to create the Parameter 4 times since there are four values?  I didn't write the stored proc so i don't know what is required.  I just know it runs by passing those values above.

thanks
0
 
anderson22Commented:
the code provided by acperkins looks correct.  It is important to note one thing with stored procedures and asp; the parameters must be listed in order and completely supplied or the execution will fail.  Unlike query analyzer where parameters can be labled and provided as needed.

-rca
0
 
Anthony PerkinsCommented:
>>the parameters must be listed in order and completely supplied or the execution will fail. <<
Absolutely.  The only way round this is to use the Command's Refresh method and this is only a good idea when debugging and never a good idea in production code.
0
 
latzo2Author Commented:
When it gets to:
Set rs = .Execute

It says:
Microsoft OLE DB Provider for SQL Server (0x80040E21)
Invalid character value for cast specification.
0
 
SlimshaneeyCommented:
Use this:
'where objConn is your connection, and objRs is a recordset
objConn.sfa_Intellisearch '{6FF5F8BD-26D8-42A9-9654 3CA8021739DA}', 'A', NULL, '0', objRs
0
 
Anthony PerkinsCommented:
>>Invalid character value for cast specification.<<
It sounds like there is something wrong in the Stored Procedure, though the following lines:

PeopleOrgPhone.PhoneType = CAST( '{4EAF18EC-D5AE-11D3-B7F6-0090278625F6}' AS uniqueidentifier )
...

PeopleOrgEContact.eContactType = CAST( '{4EAF18D8-D5AE-11D3-B7F6-0090278625F6}' AS uniqueidentifier )

Probably do not need to be CASTed, as in:

PeopleOrgPhone.PhoneType = '{4EAF18EC-D5AE-11D3-B7F6-0090278625F6}'

...

PeopleOrgEContact.eContactType = '{4EAF18D8-D5AE-11D3-B7F6-0090278625F6}'

Can we assume it runs correctly in Query Analyzer?
0
 
anderson22Commented:
If ADO is able to call the stored procedure it will not return an error message if the procedure bombs.  In this case, the error appears to be generated from ADO which means that something with the setup of the call is not correct.

I would be willing to bet the problem is with your data typing.  Types like nvarchar, bit, uniqueidentifier are going to give you problems coming from vbscript.

I would recommed changing these even if it causes a headache.  The first thing is the GUID as the unique identifier.  I never suggest using GUIDs because they don't accomplish anything over an INT and they take more room.
Secondly, nvarchar stored the same information as varchar except in unicode.  This recquires extra processing and therefore is less efficient.   Unless you are dealing with multiple languages don't use it
Using a bit field is great but I don't know if ASP can handle it.  You might try using a tinyint instead just to see if that works.  A tinyint takes up a little more room but you may have to sacrifice something here.

-rca
0
 
Anthony PerkinsCommented:
>>If ADO is able to call the stored procedure it will not return an error message if the procedure bombs.<<
I don't agree.  ADO does in fact return Fatal error messages generated by Stored Procedures.  The ADO Error object gives you all the SQL Server details for the error.

>>Types like nvarchar, bit, uniqueidentifier are going to give you problems coming from vbscript.<<
I don't see why.  We use them all the time.

>>Using a bit field is great but I don't know if ASP can handle it. <<
Again, I do not agree.  This has nothing to do with ASP and everything with ADO.  If the ADO interface supports it (and it does) than that is all that counts.
0
 
latzo2Author Commented:
The s tored proc is from the produciton server and is used serveral time a day.  

If i change

PeopleOrgEContact.eContactType = CAST( '{4EAF18D8-D5AE-11D3-B7F6-0090278625F6}' AS uniqueidentifier )

to

PeopleOrgEContact.eContactType =  '{4EAF18D8-D5AE-11D3-B7F6-0090278625F6}' AS uniqueidentifier

I still ge tthe same message


This Proc has been working for a couple of years. However i didn't write it.  

Why is it that the ASP code needs all of the variables to be passed when query analyzer only nees four parameters?

'{6FF5F8BD-26D8-42A9-9654-3CA8021739DA}', 'a', NULL, '0'
0
 
latzo2Author Commented:
I noticed that th GUID had a missing "-" now when i run it it says "done"
acperkins i think your code is working, How do i know if this is running properly... can the results be put into a list box on the ASP page, just the company name and its unigue identifier in the list box.

thanks
0
 
Anthony PerkinsCommented:
>>Why is it that the ASP code needs all of the variables to be passed when query analyzer only nees four parameters?<<
The question should be better phrased as:
Why is it that the ADO code needs all of the variables to be passed when query analyzer only nees four parameters?
It has nothing to do with ASP

The answer is that it does not. In fact the only obligatory parameter is the first, as all the rest have default parameters. So it would be perfectly legal (although perhaps meaningless) to execute it as follows:

With cmd
   Set .ActiveConnection = cn
   .CommandText = "sfa_Intellisearch"
   .CommandType = adCmdStoredProc
   .Parameters.Append .CreateParameter("@UserID", adGUID, adParamInput, 0, "{6FF5F8BD-26D8-42A9-9654 3CA8021739DA}")
   Set rs = .Execute
End With

>>How do i know if this is running properly<<
Try displaying the values from the recordset, as in:

With rs
   Do While Not .EOF
      .Response.Write .Fields("OrgID").Value & " " & .Fields("OrgName").Value & "<br/>"
      .MoveNext
   Loop
   .Close
End With
Set rs = Nothing
cn.Close
Set cn = Nothing

>>can the results be put into a list box on the ASP page<<
Yes.  (Start a new question)
0
 
latzo2Author Commented:
I posted another question
titled:
"ASP, Results of Stored Proc into a list box within as asp page, with selection execs another stored proc"
0
 
latzo2Author Commented:
Thanks for all your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.