• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 424
  • Last Modified:

Create a last visited pages listing using Classic ASP

First off, I'm not even sure if my approach is the best method to captures user pages visited.

I'm looking to create a listing of the last 5 or so pages visited by a user. I'm trying to capture the User's ID, URL and Page Title and insert those values into a SQL 2005 DB. In the code example below I'm checking that a user cookie exist and then doing an insert. I know the cookie exists but I keep getting the follow error message regarding my INSERT statement.

"Microsoft OLE DB Provider for SQL Server error '80040e14'
Syntax error or access violation
 line 62 "

Line 62:
UpCom.Execute


<%
If Request.Cookies("CookieDemo") <> "" then
strUSR = Request.Cookies("CookieDemo")("user")
strPURL = "http://www.mysite.com"
strTTL = "Cool Website"


Set UpCom=Server.CreateObject("ADODB.Command")
UpCom.ActiveConnection=MY_STRING
UpCom.CommandText = "INSERT INTO mbrHistory (MID, PURL, PTITLE) VALUES (strUSR, strPUR, strTTL)"
UpCom.Parameters.Append UpCom.CreateParameter("@RETURN_VALUE", 3, 4)
UpCom.Parameters.Append UpCom.CreateParameter("@MID", 200, 1,255,strUSR)
UpCom.Parameters.Append UpCom.CreateParameter("@PURL", 200, 1,255,strPURL)
UpCom.Parameters.Append UpCom.CreateParameter("@PTITLE", 200, 1,255,strTTL)
UpCom.CommandType = 4
UpCom.CommandTimeout = 0
UpCom.Prepared = true
UpCom.Execute

Else
response.write("INSERT FAILED")
End if
%>

Open in new window

0
swaggerking
Asked:
swaggerking
  • 7
  • 6
2 Solutions
 
Wayne BarronCommented:
What is this
>>  ("@RETURN_VALUE", 3, 4)

And how are you getting your values?
Please provide code that shows how you are getting your values to instead into the database.

Carrzkiss
0
 
swaggerkingAuthor Commented:
Hey carrzkiss,
That was a typo on my end ( ("@RETURN_VALUE", 3,). I was playing with various versions trying to get this code to work and that came over when I copy/pasted my post and didn't get edited out.

Basically I'm just exploring an idea.
My cookie value ("CookieDemo") is based off your login authentication ("carrzkissCookieDemo")

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/ASP/Q_24902433.html

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/ASP/Q_24282069.html#24032121
>>
strUSR = Request.Cookies("CookieDemo")("user")
>>

Let say the last two value are Server Variables
strPURL = Request.ServerVariables("SCRIPT_NAME")
strTTL = Request.ServerVariables("URL")
>>

Unfortunately I don't believe I can grab page title using VB. I think that can only be done using js. And I'm not sure how to incorporate that into my current statement.
<script type="text/javascript">
var x = document.title;
{
document.write(" + i + ");
}
</script>
<%
If Request.Cookies("CookieDemo") <> "" then
strUSR = Request.Cookies("CookieDemo")("user")
strPURL = Request.ServerVariables("SCRIPT_NAME")
strTTL = Request.ServerVariables("URL")


Set UpCom=Server.CreateObject("ADODB.Command")
UpCom.ActiveConnection=MY_STRING
UpCom.CommandText = "INSERT INTO mbrHistory (MID, PURL, PTITLE) VALUES (strUSR, strPUR, strTTL)"
UpCom.Parameters.Append UpCom.CreateParameter("@RETURN_VALUE", 3, 4)
UpCom.Parameters.Append UpCom.CreateParameter("@MID", 200, 1,255,strUSR)
UpCom.Parameters.Append UpCom.CreateParameter("@PURL", 200, 1,255,strPURL)
UpCom.Parameters.Append UpCom.CreateParameter("@PTITLE", 200, 1,255,strTTL)
UpCom.CommandType = 4
UpCom.CommandTimeout = 0
UpCom.Prepared = true
UpCom.Execute

Else
response.write("INSERT FAILED")
End if
%>

Open in new window

0
 
swaggerkingAuthor Commented:
Dang it, even my little js script that I rambled on about is wrong.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Wayne BarronCommented:
This is your site right?
Is the entire site dynamic and read from a database?
If so, then you do not need JS or anything else to get what you want.
You can simply just write it out as it presents itself.

Example.
Lets say that you have a recordset that is: (Please view demo below)

This is the best way to accomplish what you are wanting to do.
It is less coding and all serverside scripting instead of clientside js.

Let me know if you have any questions.

Good Luck
Carrzkiss
Almost forgot... Happy New Year!!!!




<% 
If Request.Cookies("CookieDemo") <> "" then 
strUSR = Request.Cookies("CookieDemo")("user") 
'strPURL = Request.ServerVariables("SCRIPT_NAME") 
'strTTL = Request.ServerVariables("URL") 
strpageid = int(request.QueryString("ID")) ' this gets our pageid that we are on, so we can get our records to display our content.

Set mysql=Server.CreateObject("ADODB.Command") 
mysql.ActiveConnection=MY_STRING
mysql.Prepared = true
' This is a demo table, please replace with your own.
mysql.commandtext="mytitle, pageid from pages where pageid=?"
rspages.Parameters.Append rspages.CreateParameter("@pageid", 3, 1, ,strpageid) 
set rspages = mysql.execute
if not rspages.eof then
strTTL = trim(rspages("mytitle"))
strpageid = int(rspages("pageid"))
' now, what we are going to do here is create our link structure to insert into the table.
strPURL = "page.asp?id="&pageid&""
end if
 
' ok, now we are going to use our recordset to insert the records into our waiting table.
 
Set UpCom=Server.CreateObject("ADODB.Command") 
UpCom.ActiveConnection=MY_STRING 
UpCom.CommandText = "INSERT INTO mbrHistory (MID, PURL, PTITLE) VALUES (strUSR, strPUR, strTTL)" 
UpCom.Parameters.Append UpCom.CreateParameter("@RETURN_VALUE", 3, 4) 
UpCom.Parameters.Append UpCom.CreateParameter("@MID", 200, 1,255,strUSR) 
UpCom.Parameters.Append UpCom.CreateParameter("@PURL", 200, 1,255,strPURL) 
UpCom.Parameters.Append UpCom.CreateParameter("@PTITLE", 200, 1,255,strTTL) 
UpCom.CommandType = 4 
UpCom.CommandTimeout = 0 
UpCom.Prepared = true 
UpCom.Execute 
 
Else 
response.write("INSERT FAILED") 
End if 
%>

Open in new window

0
 
swaggerkingAuthor Commented:
Thanks for the code but I'm still getting the same error:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Syntax error or access violation
/test/history.asp, line 38

LINE 38: UpCom.Execute

I took out the INSERT to check that I was pulling the values from the rs and it passed the test. I'm now assuming that it's purely the Insert that's wrong.


I also thought I saw a couple minor typos and I think I corrected them.
Original:
rspages.Parameters.Append rspages.CreateParameter("@pageid", 3, 1, ,strpageid)
set rspages = mysql.execute

Revised:
mysql.Parameters.Append mysql.CreateParameter("@pageid", 3, 1, ,strpageid)
set rspages = mysql.execute


<% 
If Request.Cookies("CookieDemo") <> "" then 
strUSR = Request.Cookies("CookieDemo")("user") 
strpageid = int(request.QueryString("ID")) 

Set mysql=Server.CreateObject("ADODB.Command") 
mysql.ActiveConnection=MY_CONN_STRING
mysql.Prepared = true

' My Table Info.

mysql.commandtext="SELECT LINK_NAME, LINK_ID FROM tblProfile WHERE LINK_ID=?"
mysql.Parameters.Append mysql.CreateParameter("@LINK_ID", 3, 1,255,strpageid) 
set rspages = mysql.execute
if not rspages.eof then
strTTL = trim(rspages("LINK_NAME"))
strpageid = trim(rspages("LINK_ID"))
strPURL = "mytestpage.asp?id="&strpageid&""
end if
 
' THE INSERT
 
Set UpCom=Server.CreateObject("ADODB.Command") 
UpCom.ActiveConnection=MY_CONN_STRING
UpCom.CommandText = "INSERT INTO mbrHistory (MID, PURL, PTITLE) VALUES (strUSR, strPUR, strTTL)" 
UpCom.Parameters.Append UpCom.CreateParameter("@RETURN_VALUE", 3, 4) 
UpCom.Parameters.Append UpCom.CreateParameter("@MID", 200, 1,255,strUSR) 
UpCom.Parameters.Append UpCom.CreateParameter("@PURL", 200, 1,255,strPURL) 
UpCom.Parameters.Append UpCom.CreateParameter("@PTITLE", 200, 1,255,strTTL) 
UpCom.CommandType = 4 
UpCom.CommandTimeout = 0 
UpCom.Prepared = true 
UpCom.Execute 
 
Else 
response.write("INSERT FAILED") 
End if 
%>

Open in new window

0
 
Wayne BarronCommented:
What is this line used for:???


UpCom.Parameters.Append UpCom.CreateParameter("@RETURN_VALUE", 3, 4)

In your INSERT statement, you only have 3 Columns and 3 Fields of which to fill them with.
But this @return_value
Is causing you problems.
Remove it and you should be good to go.

Also, make sure that your Parameters are correct.
200 = varchar
So make sure that all your columns are varchar.

Carrzkiss
0
 
swaggerkingAuthor Commented:
Removed: UpCom.Parameters.Append UpCom.CreateParameter("@RETURN_VALUE", 3, 4)
Still getting the same error.

I actually have 5 columns:
UID (int) PK
MID (nvarchar(255) not null
PURL (nvarchar(255) not null
PTITLE (nvarchar(255) not null
MODIFIED (datetime, null) default value getdate()

I didn't think I needed to declare "UID (int) PK" or "MODIFIED (datetime, null)".
0
 
Wayne BarronCommented:
Try the code below.
I removed several elements.
#1: >> UpCom.Prepared = true    (This does not go in your INSERT, DELETE, UPDATE only in your SELECT statements)

The other 2 lines

UpCom.CommandType = 4  
UpCom.CommandTimeout = 0

I have never used in an Update, insert, delete, so they are removed as well.

Give it a shot and let me know what happens now?
Carrzkiss

<%  
If Request.Cookies("CookieDemo") <> "" then  
strUSR = Request.Cookies("CookieDemo")("user")  
strpageid = int(request.QueryString("ID"))  
 
Set mysql=Server.CreateObject("ADODB.Command")  
mysql.ActiveConnection=MY_CONN_STRING 
mysql.Prepared = true 
 
' My Table Info. 
 
mysql.commandtext="SELECT LINK_NAME, LINK_ID FROM tblProfile WHERE LINK_ID=?" 
mysql.Parameters.Append mysql.CreateParameter("@LINK_ID", 3, 1,255,strpageid)  
set rspages = mysql.execute 
if not rspages.eof then 
strTTL = trim(rspages("LINK_NAME")) 
strpageid = trim(rspages("LINK_ID")) 
strPURL = "mytestpage.asp?id="&strpageid&"" 
end if 
  
' THE INSERT 
  
Set UpCom=Server.CreateObject("ADODB.Command")  
UpCom.ActiveConnection=MY_CONN_STRING 
UpCom.CommandText = "INSERT INTO mbrHistory (MID, PURL, PTITLE) VALUES (strUSR, strPUR, strTTL)"  
UpCom.Parameters.Append UpCom.CreateParameter("@MID", 200, 1,255,strUSR)  
UpCom.Parameters.Append UpCom.CreateParameter("@PURL", 200, 1,255,strPURL)  
UpCom.Parameters.Append UpCom.CreateParameter("@PTITLE", 200, 1,255,strTTL)   
UpCom.Execute  
  
Else  
response.write("INSERT FAILED")  
End if  
%>

Open in new window

0
 
swaggerkingAuthor Commented:
Getting a new error:
Microsoft OLE DB Provider for SQL Server error '80040e14'

The name "strUSR" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

LINE 32: UpCom.Execute
0
 
swaggerkingAuthor Commented:
Ok, I think I got  it. I started from scratch using a Store Procedure for my INSERT. I also had to grant permissions for this specific SP  INSERT to work.

CREATE PROCEDURE sp_history
@p_MID varchar(255),
@p_PURL varchar(255),
@p_PTITLE varchar(255)

AS
BEGIN

SET NOCOUNT ON;    
INSERT INTO [mbrHistory]
(
[MID], [PURL], [PTITLE]
)
VALUES
(
 @p_MID, @p_PURL, @p_PTITLE
)
END
GO
GRANT EXECUTE ON sp_history TO Visitor
<%
If Request.Cookies("CookieDemo") <> "" then 
strUSR = Request.Cookies("CookieDemo")("user") 
'strPURL = Request.ServerVariables("SCRIPT_NAME") 
'strTTL = Request.ServerVariables("URL") 
strpageid = int(request.QueryString("ID")) ' this gets our pageid that we are on, so we can get our records to display our content.

Set mysql=Server.CreateObject("ADODB.Command") 
mysql.ActiveConnection=MY_CONN_STRING
mysql.Prepared = true
' This is a demo table, please replace with your own.

mysql.commandtext="SELECT LINK_NAME, LINK_ID FROM tblProfile WHERE LINK_ID=?"
mysql.Parameters.Append mysql.CreateParameter("@LINK_ID", 3, 1,255,strpageid) 
set rspages = mysql.execute
if not rspages.eof then
strTTL = trim(rspages("LINK_NAME"))
strpageid = trim(rspages("LINK_ID"))
' now, what we are going to do here is create our link structure to insert into the table.
strPURL = "carz_history.asp?id="&strpageid&""
end if

set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = MY_CONN_STRING
cmd.CommandText = "sp_history"
cmd.CommandType = 4
cmd.CommandTimeout = 0
cmd.Prepared = true
cmd.Parameters.Append cmd.CreateParameter("@p_MID", 200, 1,255,strUSR)
cmd.Parameters.Append cmd.CreateParameter("@p_PURL", 200, 1,255,strPURL)
cmd.Parameters.Append cmd.CreateParameter("@p_PTITLE", 200, 1,255,strTTL)
cmd.Execute()

Else 
response.write("INSERT FAILED") 
End if 
%>

Open in new window

0
 
Wayne BarronCommented:
So, it is working for you now?
I am not up on Stored Precedures. Once I get the spare time I will start working with them.
If it is working for you now, that is great.
I do not see why it would not work before, very strang.

Carrzkiss
0
 
swaggerkingAuthor Commented:
I got it working using a SP but I'm also stumped on why the INSERT statement you helped me with isn't since it's relatively similar to my SP.  I'm going to play around with it some more this afternoon and see what I can come up with. Might be a simple as a corrupt file or restarting my machine. I really want to figure this out and practice makes perfect (or almost perfect). As always, thank you for your help and patience. Oh, my bad, I forgot to say, Happy New Year!
0
 
Wayne BarronCommented:
I am just glad that you got it to work.
It is beyond me as to why it is not working like it is suppose to.

Good Luck
Carrzkiss
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now