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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

Dave BaldwinFixer 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
Big MontyWeb Ninja at largeCommented:
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

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
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
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Dave BaldwinFixer 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 MontyWeb Ninja at largeCommented:
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
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.