Solved

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

Posted on 2004-03-30
25
1,831 Views
Last Modified: 2012-05-05
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

 

0
Comment
Question by:latzo2
  • 9
  • 8
  • 5
  • +2
25 Comments
 
LVL 5

Expert Comment

by:DesertWarrior
ID: 10719071
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
 
LVL 4

Expert Comment

by:anderson22
ID: 10719107
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
 
LVL 11

Expert Comment

by:Slimshaneey
ID: 10722738
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
 

Author Comment

by:latzo2
ID: 10725274
Thanks i'll try these an let you know how it goes.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10726201
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
 

Author Comment

by:latzo2
ID: 10726561
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
 
LVL 4

Expert Comment

by:anderson22
ID: 10726922
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10726928
>>Do you want me to provide the stored proc?<<
Yes.
0
 

Author Comment

by:latzo2
ID: 10727075
'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
 
LVL 4

Expert Comment

by:anderson22
ID: 10727852
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10727910
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
 

Author Comment

by:latzo2
ID: 10728048
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 10728077
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
 
LVL 4

Expert Comment

by:anderson22
ID: 10728110
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10728173
>>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
 

Author Comment

by:latzo2
ID: 10728315
When it gets to:
Set rs = .Execute

It says:
Microsoft OLE DB Provider for SQL Server (0x80040E21)
Invalid character value for cast specification.
0
 
LVL 11

Expert Comment

by:Slimshaneey
ID: 10730114
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10732224
>>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
 
LVL 4

Expert Comment

by:anderson22
ID: 10732848
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10733014
>>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
 

Author Comment

by:latzo2
ID: 10733776
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
 

Author Comment

by:latzo2
ID: 10734046
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10734157
>>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
 

Author Comment

by:latzo2
ID: 10734418
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
 

Author Comment

by:latzo2
ID: 10734425
Thanks for all your help!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now