Stored Procedure

I am just learning how to use stored procedures, and I am looking for some simple guidance...


I have a procedure stored in SQL Server called (usp_HitCount) as such:
     SELECT page_name, hit_count FROM tbl_hit_count WHERE page_name= @PageName

On my ASP page I get the page name as such:
     strPageName = Request.ServerVariables("SCRIPT_NAME")

When I was using an Access DB I would run the inline SQL as such:
     strSQL = "SELECT page_name, hit_count FROM tbl_hit_count WHERE page_name='" & strPageName & "';"

so, my question is, how can I call my stored procedure, pass it strPageName as the @PageName variable, and then use the results as a normal recordset so I can increment my hit_count column as needed?

The point of all this is it is part of a function that I place on each page to increment the number of hits that page has received as such:

<%
Function RetrieveAndIncrementCount()

//PASS PAGE NAME AND GET RS HERE//

      If rsCounter.EOF Then
            rsCounter.AddNew

            iCount = 0

            rsCounter.Fields("page_name").Value = strPageName
      Else
            rsCounter.MoveFirst

            iCount = rsCounter.Fields("hit_count").Value
      End If

      ' Increment the count and update the DB
      rsCounter.Fields("hit_count").Value = iCount + 1
      rsCounter.Update

      ' Close our connection
      rsCounter.Close
      Set rsCounter = Nothing

      ' Return the count (pre-incrementation).
      RetrieveAndIncrementCount = iCount
End Function
%>
NickJPhillipsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

amit_gCommented:
You can call it as ...

strSQL = "exec usp_HitCount '" & strPageName & "';"
programmer_bmbCommented:


Try to use sqlHelper.Dll from microsoft and use ExecuteStoredProcedure

Anthony PerkinsCommented:
In nutshell: You are attempting to return a resultset from a stored procedure and then plan to update that resultset.  The problem is that by default and for performance reasons all resultsets returned by a stored procedure are the firehose (Forward-Only, Read-Only) variety.  Notice that I say "by default", yes you can use a workaround to get an updateable recordset, the problem is that you sort of defeat one of the main reasons for using stored procedures in the first place (performance and reduce network traffic).  So you have a couple of choices:
1. Change the type of cursor returned by a stored procedure so that you can update it.
2. For a far better approach, change your stored procedure to insert or update the hit_count and return just the incremented value.
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Anthony PerkinsCommented:
If you do go for the recommended solution 2, than your stored procedure would look something like this:

Create Procedure usp_GetNewHitCount
            @PageName  varchar(50),    -- Change data type appropriately
            @HitCount integer output

AS

SET NOCOUNT ON

SELECT      @HitCount = hit_count + 1
FROM      tbl_hit_count
WHERE      page_name= @PageName

IF @HitCount Is Null
   BEGIN
      Set @HitCount = 1
      INSERT tbl_hit_count (page_name, hit_count) VALUES (@PageName, @HitCount)
   END
ELSE
   BEGIN
      UPDATE      tbl_hit_count
      SET      hit_count = @HitCount
      WHERE      page_name= @PageName
   END


Anthony PerkinsCommented:
And this is what your ASP code would look like:

<%
Dim cn, cmd

Set cn = Server.CreateObject("ADODB.Connection")
With cn
      .ConnectionString = "Your Connection string goes here"
      .Open
End With
      
Set cn = Server.CreateObject("ADODB.Command")
With cmd
      Set .ActiveConnection = cn
      .CommandText = "usp_GetNewHitCount"
      .CommandType = 4                  'adCmdStoredProc
      .Append .CreateParameter("@PageName", 200, 1, 50, Request.ServerVariables("SCRIPT_NAME")) ' change 200, 50 if not varchar(50)
      .Append .CreateParameter("@HitCount", 3, 3, 0, NULL)
      .Execute , , 128                  'adExecuteNoRecords
      Response.Write .Parameters("@HitCount").Value
End With
cn.Close
Set cn = Nothing
%>

As you will no doubt appreciate, this is far more efficient code.
NickJPhillipsAuthor Commented:
Thanks for your inputs.

To acperkins...

I am trying to work through your code to learn what it does. But, I want to make sure I got it right first...because when I attempted to run it, I keep getting the following error:

Microsoft VBScript runtime error '800a01a8'

Object required: '[undefined]'

/hit_count.asp, line 15


Line 15 is "With cmd"

any ideas? Also, I am confused about the variable in the ASP code... after @VARIABLE NAME ....  200, 1, 50, (what do these numbers do?)
alorentzCommented:
Change to this:

Set cmd = Server.CreateObject("ADODB.Command")   '<-----------------SHOULD BE CMD, NOT cn
With cmd
Anthony PerkinsCommented:
Just change:
Set cn = Server.CreateObject("ADODB.Command")

To:
Set cmd = Server.CreateObject("ADODB.Command")


>>Also, I am confused about the variable in the ASP code... after @VARIABLE NAME ....  200, 1, 50, (what do these numbers do?)<<
200 = is the datatype for varchar
1 = indicates whether the parameter is input, output or input output.
50 = length of the varchar parameter.

I made the assumption that your stored procedure had a @PageName parameter of data type varchar and length 50.  So in essence I am matching the VBScript code to the corresponding Stored Procedure and its parameters.

If you are in the habit of including the ADO constants (very recommended) than this may be clearer:

<%
Dim cn, cmd

Set cn = Server.CreateObject("ADODB.Connection")
With cn
     .ConnectionString = "Your Connection string goes here"
     .Open
End With
     
Set cn = Server.CreateObject("ADODB.Command")
With cmd
     Set .ActiveConnection = cn
     .CommandText = "usp_GetNewHitCount"
     .CommandType = adCmdStoredProc
     .Append .CreateParameter("@PageName", adVarChar, adParamInput, 50, Request.ServerVariables("SCRIPT_NAME"))
     .Append .CreateParameter("@HitCount", adInteger, adParamInputOutput, 0, NULL)
     .Execute , , adExecuteNoRecords
     Response.Write .Parameters("@HitCount").Value
End With
cn.Close
Set cn = Nothing
%>

Here is the definition of the CreateParameter method:

Syntax
Set parameter = command.CreateParameter (Name, Type, Direction, Size, Value)

Return Value
Returns a Parameter object.

Parameters
Name
Optional. A String value that contains the name of the Parameter object.

Type
Optional. A DataTypeEnum value that specifies the data type of the Parameter object.

Direction
Optional. A ParameterDirectionEnum value that specifies the type of Parameter object.

Size
Optional. A Long value that specifies the maximum length for the parameter value in characters or bytes.

Value
Optional. A Variant that specifies the value for the Parameter object.

NickJPhillipsAuthor Commented:
Hmm..now I am getting:


ADODB.Command error '800a0bb9'

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

/hit_count.asp, line 17



Line 17 = .CommandType = adCmdStoredProc


I feel like a bother here, but still trying to learn! Thanks for your help!!
alorentzCommented:
anthony - just a note...you posting the same error code:

Set cn = Server.CreateObject("ADODB.Connection")
With cn
     .ConnectionString = "Your Connection string goes here"
     .Open
End With
     
Set cn = Server.CreateObject("ADODB.Command") '<==== cmd
Anthony PerkinsCommented:
>>ADODB.Command error '800a0bb9' <<
This may have to do with the fact that you are not including the ADO constants definitions. So you need to just use the literal values as in my original code with the correction.  Here it is again (hopefully I will get it right this time):

<%
Dim cn, cmd

Set cn = Server.CreateObject("ADODB.Connection")
With cn
     .ConnectionString = "Your Connection string goes here"
     .Open
End With
     
Set cmd = Server.CreateObject("ADODB.Command")
With cmd
     Set .ActiveConnection = cn
     .CommandText = "usp_GetNewHitCount"
     .CommandType = 4               'adCmdStoredProc
     .Append .CreateParameter("@PageName", 200, 1, 50, Request.ServerVariables("SCRIPT_NAME"))
     .Append .CreateParameter("@HitCount", 3, 3, 0, NULL)
     .Execute , , 128               'adExecuteNoRecords
     Response.Write .Parameters("@HitCount").Value
End With
cn.Close
Set cn = Nothing
%>

If that still produces an error, I will need you to post the Stored Procedure as you have created it and I will test the VBScript code this time :)
NickJPhillipsAuthor Commented:
New Error:

Microsoft VBScript runtime error '800a01b6'

Object doesn't support this property or method: 'Append'

/hit_count.asp, line 18

Line 18 = .Append .CreateParameter("@PageName", 200, 1, 50, Request.ServerVariables("SCRIPT_NAME"))


Stored Procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER Procedure [dbo].[usp_GetNewHitCount]
          @PageName  varchar(50),    -- Change data type appropriately
          @HitCount integer output

AS

SET NOCOUNT ON

SELECT     @HitCount = hit_count + 1
FROM     tbl_hit_count
WHERE     page_name= @PageName

IF @HitCount Is Null
   BEGIN
     Set @HitCount = 1
     INSERT tbl_hit_count (page_name, hit_count) VALUES (@PageName, @HitCount)
   END
ELSE
   BEGIN
     UPDATE     tbl_hit_count
     SET     hit_count = @HitCount
     WHERE     page_name= @PageName
   END


TABLE:
tbl_hit_count
page_name (PK)
hit_count

Anthony PerkinsCommented:
It should be:
.Parameters.Append

Please use the following and sorry for all the mistakes:
<%
Dim cn, cmd

Set cn = Server.CreateObject("ADODB.Connection")
With cn
     .ConnectionString = "Your Connection string goes here"
     .Open
End With
     
Set cmd = Server.CreateObject("ADODB.Command")
With cmd
     Set .ActiveConnection = cn
     .CommandText = "usp_GetNewHitCount"
     .CommandType = 4               'adCmdStoredProc
     .Parameters.Append .CreateParameter("@PageName", 200, 1, 50, Request.ServerVariables("SCRIPT_NAME"))
     .Parameters.Append .CreateParameter("@HitCount", 3, 3, 0, NULL)
     .Execute , , 128               'adExecuteNoRecords
     Response.Write .Parameters("@HitCount").Value
End With
cn.Close
Set cn = Nothing
%>
Anthony PerkinsCommented:
I finally got round to testing the ASP code and providing that the Stored Procedure logic is correct you should get a successful response.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NickJPhillipsAuthor Commented:
Please, dont be sorry... you basicall wrote the new function for me. I know thats not the point of EE...so you definately went above and beyond. Now (I have already awarded you the points...if I could give you more I would) at the risk of sounding demanding ;-), would you mind helping me understand how this works (now that it is working). Like I said in my oringal post, I have just started using SQL Server...actually I installed SQL Server Express 2005 the same day as my original post. Up till a few days ago, all I had ever used was MS Access as a web BE. T-SQL, stored procedures, and the darn analyzer are all VERY new to me.

Once again thank you for your help!

Nick
Anthony PerkinsCommented:
>>would you mind helping me understand how this works <<
Not a problem.  Not sure which part specifically you are haing trouble with so I will address both:

1. Stored Procedure
Create Procedure usp_GetNewHitCount
          @PageName  varchar(50),    -- IMPORTANT: Change data type appropriately, it should match the data type and length for the column "page_name"
          @HitCount integer output       -- Output parameter

AS

SET NOCOUNT ON

SELECT     @HitCount = hit_count + 1       -- If found, assign the next hit_count + 1 to the output parameter
FROM     tbl_hit_count
WHERE     page_name= @PageName

IF @HitCount Is Null              -- Did not find the page_name, therefore must add a new entry
   BEGIN
     Set @HitCount = 1           -- Initialize the value
     INSERT tbl_hit_count (page_name, hit_count) VALUES (@PageName, @HitCount)    -- Add it to table
   END
ELSE                                    -- Found the page_name and @HitCount is equal to hit_count + 1
   BEGIN
     UPDATE     tbl_hit_count             -- Here we update the record with the new value
     SET     hit_count = @HitCount
     WHERE     page_name= @PageName
   END

-- Here there is an implicit return of the  the output parameter @HitCount

2. ASP
<%
Dim cn, cmd

Set cn = Server.CreateObject("ADODB.Connection")             ' Initialize the Connection object
With cn
     .ConnectionString = "Your Connection string goes here"
     .Open
End With
     
Set cmd = Server.CreateObject("ADODB.Command")              ' Initialize the Command object
With cmd
     Set .ActiveConnection = cn                                              ' Tell it what Connection object to use
     .CommandText = "usp_GetNewHitCount"                          ' What are we doing
     .CommandType = 4               'adCmdStoredProc              ' What type of operation
     .Parameters.Append .CreateParameter("@PageName", 200, 1, 50, Request.ServerVariables("SCRIPT_NAME"))          ' Define the input parameter @PageName. It should match the first parameter
     .Parameters.Append .CreateParameter("@HitCount", 3, 3, 0, NULL) ' Define the output parameter @PageName. It should match the second parameter
     .Execute , , 128               'adExecuteNoRecords      'Here we execute the stored procedure and since we are not returning a resultset, we (optionally) tell it (adExecuteNoRecords)
     Response.Write .Parameters("@HitCount").Value    ' Here we get the value of the output parameter back from the stored procedure
End With
cn.Close                              ' If we do not need the Connection object any longer we close it.
Set cn = Nothing                  ' Here we release the object
%>

I trust that is clear, let me know otherwise.
NickJPhillipsAuthor Commented:
Thanks for a great tutorial on SP's!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.