Cookies (create and recall)

Hi there, I need some help creating cookies for new web visitors and recalling them when they come back

Currently I have some code, however there is some issues, as only 1 computer will work, the rest have errors

RemoteIP=Request.ServerVariables("REMOTE_ADDR")
Set SessionLookup=oConn.Execute("SELECT * FROM site_sessions WHERE brand_ID='GPT' AND session_ID="&Session.SessionID)
If NOT SessionLookup.EOF Then
	Session_ID=SessionLookup("session_ID")
	Cookie_ID=SessionLookup("cookie_ID")
Else
	If Request.Cookies("cookie")=NULL Then
		Response.Cookies("cookie")=Session.SessionID
		Response.Cookies("cookie").Expires=#Dec 31,2030#
	End If
	Session_ID=Session.SessionID
	Cookie_ID=Request.Cookies("cookie")
	oConn.Execute("INSERT INTO site_sessions(session_ID,cookie_ID,brand_ID,session_IP,session_date,session_refer) VALUES("&Session_ID&","&Cookie_ID&",'GPT','"&RemoteIP&"','"&Now()&"','"&Request.ServerVariables("HTTP_REFERER")&"')")
End If

Open in new window


Microsoft JET Database Engine error '80040e14'

Syntax error in INSERT INTO statement

oConn.Execute("INSERT INTO site_sessions(session_ID,cookie_ID,brand_ID,session_IP,session_date,session_refer) VALUES("&Session_ID&","&Cookie_ID&",'GPT','"&RemoteIP&"','"&Now()&"','"&Request.ServerVariables("HTTP_REFERER")&"')")

Open in new window

Graemewebber4technologiesAsked:
Who is Participating?
 
Big MontyConnect With a Mentor Senior Web Developer / CEO of ExchangeTree.org Commented:
To expand a bit on Dave's answer, as he is absolutely correct on the points he makes. The code below should work irregardless of the computer you are on (my guess is you ran this with no problems directly on the server, and that's why it worked):

RemoteIP=Request.ServerVariables("REMOTE_ADDR")
Set SessionLookup=oConn.Execute("SELECT * FROM site_sessions WHERE brand_ID='GPT' AND session_ID="&Session.SessionID)
If NOT SessionLookup.EOF Then
	Session_ID=SessionLookup("session_ID")
	Cookie_ID=SessionLookup("cookie_ID")
Else
	If Request.Cookies("cookie")=NULL Then
		Response.Cookies("cookie")=Session.SessionID
		Response.Cookies("cookie").Expires= CDate( "12/31/2030" )        '-- you cannot use the syntax #date value#, it's syntax is incorrect
	End If
	Session_ID=Session.SessionID
	Cookie_ID=Request.Cookies("cookie")
        if Cookie_ID = "" then Cookie_ID = "0"      '-- if Cookie_ID doesn't contain a value, your sql will break, so I made it equal zero. Substitute that with whatever the default value should be (Maybe Session.SessionID?)

	oConn.Execute("INSERT INTO site_sessions(session_ID,cookie_ID,brand_ID,session_IP,session_date,session_refer) " _
          & " VALUES( Session_ID & "," _
          & Cookie_ID & ", "_                                      '-- I'm assuming this column in your table is numerical of some kind. if not, you'll need to add single quotes around it
          & "'GPT'," _
          & "'" & RemoteIP & "'," _
          & "getDate(), " _             '-- same as using Now(), it's just now using the sql server getDate function, so you don't need to be concern with the proper syntax
         & "'" & Request.ServerVariables("HTTP_REFERER") & "' ) " )
End If

Open in new window


I reformatted it for readability, as it was difficult to read without the spacing. I also left some comments so you can see what I did and why. Any questions, please ask!
0
 
Dave BaldwinConnect With a Mentor Fixer of ProblemsCommented:
I'm not sure what you're trying to do but I see several possible problems.  'Request.Cookies' only contains cookies that were sent with the current page request.  It does Not contain cookies that will be set by 'Response.Cookies'.  Those will be received on the Next page request.  In addition, 'HTTP_REFERER' will only be set if you got to this page from a link on another page, the 'referring page'.  If you type this URL directly in the address bar of the browser, there is no 'HTTP_REFERER'.
0
 
Graemewebber4technologiesAuthor Commented:
Happy New Year Big Monty, just trying out the suggested code...

The line:
if Cookie_ID = "" then Cookie_ID = "0"      '-- if Cookie_ID doesn't contain a value, your sql will break, so I made it equal zero. Substitute that with whatever the default value should be (Maybe Session.SessionID?)

Open in new window

Where might be the ESLE or END IF ?

Cheers

Happy New Year Dave

Yes, I am aware of HTTP_REFERER  - this is just to track to see where traffic is coming from

Request and Response for Cookies I am still trying to get my head around - just want it to assign a cookie to new visiting users and recall the returning visiting users - so I can see what pages they are viewing (with a sub_log INSERT QUERY - already works fine)

Cheers
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Dave BaldwinConnect With a Mentor Fixer of ProblemsCommented:
Apparently not...  If you put Request.ServerVariables("HTTP_REFERER") in your INSERT statement and it does not exist, your INSERT will fail.  Best practice is to create a default value that you can use when that happens.
1
 
Big MontyConnect With a Mentor Senior Web Developer / CEO of ExchangeTree.org Commented:
Happy New Years to yourself as well!

I think you're asking about the IF syntax being all on one line:

if Cookie_ID = "" then Cookie_ID = "0"      '-- if Cookie_ID doesn't contain a value, your sql will break, so I made it equal zero. Substitute that with whatever the default value should be (Maybe Session.SessionID?)

Open in new window


if you have one line of code to execute in an IF statement, you can put it all on one line and skip the closing END IF. It is the equivalent of doing:

if Cookie_ID = "" then 
     Cookie_ID = "0"      
end if

Open in new window


as for the WHY aspect of it, all I'm doing there is setting the variable Cookie_ID to zero if it doesn't contain any value. I do this so that the sql will not break. You should set it to whatever default value it ought to be.
1
 
Graemewebber4technologiesAuthor Commented:
Thanks Dave, will use an IF NULL statement for HTTP for future, haven't had any issue with it yet thou.

Nice BigMonty, didnt know that could happen!
0
 
Graemewebber4technologiesAuthor Commented:
Thanks Gents seems to be working a treat!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.