Stored Procedure

You can call it as ...

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

Try to use sqlHelper.Dll from microsoft and use ExecuteStoredProcedure

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.

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

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.

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?)

Change to this:

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

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.

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

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

>>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 :)

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

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

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

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

Thanks for a great tutorial on SP's!!!