Ad Tracking URL Hits

Here is the ad tracking I’ve used on occasion from http://aspfree.com. This has a stored procedure, two tables (one for tracking hits by day and by vendor, second for just storing info about the vendor). I’ve also used an ASPX page to grab the request.querystring, record to the database and then redirect the user to the particular link. When you write out the hyperlink programmatically or by hand goes something like HTTP://YOURDOMAIN.COM/TA.ASPX?V=1. Downloadable code available!

Here is the ad tracking I’ve used on occasion from http://aspfree.com. This has a stored procedure,
two tables (one for tracking hits by day and by vendor, second for just storing info about the vendor).
I’ve also used an ASPX page to grab the request.querystring, record to the database and then redirect the
user to the particular link. When you write out the hyperlink programmatically or by hand goes something
like HTTP://YOURDOMAIN.COM/TA.ASPX?V=1

STORED PROCEDURE
CREATE PROCEDURE dbo.sp_AdCompanyUrl ( @cId int, @URL varchar(255) OUTPUT ) AS Declare @chkRecord int Declare @numOfHits int Declare @dateSubmitted datetime set @dateSubmitted = convert(char(10), getDate(), 101) If exists (select cId from adCompanyHits where dateSubmitted = @DateSubmitted and cId=@cId) begin set @numOfHits = (select numOfHits from adCompanyHits where cId = @cId and dateSubmitted=@dateSubmitted) + 1 UPDATE adCompanyHits SET numOfHits = @numOfHits WHERE cId = @cId and dateSubmitted=@dateSubmitted end ELSE INSERT INTO adCompanyHits(cId, DateSubmitted) VALUES (@cId, @DateSubmitted)
/* I think one could use set @numOfHits = SELECT @@SCOPEIDENTITY */
SELECT URL FROM adCompanyInfo WHERE cId = @cId RETURN GO TABLES FOR STORING INFO TABLE 1 – ACTUALLY STORES INFO ON VENDOR AND NUMBER OF HITS BY DATE CREATE TABLE [dbo].[adCompanyHits] ( [id] [int] IDENTITY (1, 1) NOT NULL , [cId] [int] NULL , [DateSubmitted] [datetime] NULL , [NumOfHits] [int] NULL ) ON [PRIMARY] GO TABLE 2 – STORES COMPANY INFO ABOUT WHO YOUR DOING ADVERTISING WITH CREATE TABLE [dbo].[adCompanyInfo] ( [cId] [int] NOT NULL , [Company] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [URL] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [startDate] [datetime] NULL , [endDate] [datetime] NULL , [adDescription] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [titleDescription] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Contact_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Contact_Email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO ‘code for TA.ASPX or whatever you want to name the page.

/*I use TA for ‘track ad’ ‘this could also be re-arranged to use an ASP pages. */
<!–asp@ Page Language=”vb” –>
<!–asp@ Import Namespace=”System.Data” –>
<!–asp@ Import Namespace=”System.Data.SQLClient” –>
<script language=”vb” runat=”server”>
Sub Page_Load(sender As System.Object, e As System.EventArgs)

‘Grab the info from querystring
dim v as integer = Trim(Left(request.querystring(“v”), 1))

If Len(v) > 0 Then
    ‘GET YOUR CONNECTION STRING FROM WEB.CONFIG
    Dim conn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings(“DSN”))
    Dim cmd As SqlCommand = New SqlCommand(“sp_adCompanyUrl”, conn)
    cmd.CommandType = CommandType.StoredProcedure

    Dim workParam As SqlParameter = Nothing

    cmd.Parameters.Add(New SqlParameter(“@cId”, SqlDbType.Int, 4))
    cmd.Parameters(“@cId”).Value = v

    workParam = cmd.Parameters.Add(New SqlParameter(“@URL”, SqlDbType.VarChar, 255))
    workParam.Direction = ParameterDirection.Output

    conn.Open()
    Dim strID As string = cmd.ExecuteScalar()
    If conn.State = ConnectionState.Open Then
        conn.Close()
    End If
    ‘Response.Write(strID)  used for testing writing out the number.
    Response.Redirect(strID) ‘Put user code to initialize the page here
End If
End Sub
</script>

[gp-comments width="770" linklove="off" ]