error '80040e14'... only for some not all!??

dancrocker · June 7th, 2004, 06:32 PM

Hi,

i have a dilema....

I have a script that is for a page that displays after members of my database insert their username and password, i.e a successful password entry page from which to navigate. For the majority of the people who use the site there is no problem. However, 'Some' members are finding difficulty entering the site (seems odd?). the error reads:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression 'Ref ='.

/passsuccess.asp, line 21

Not everyone has this problem?? Everyone uses different computers to access the site. Someone recently suggested that many firewalls (especially 'zone alarm') block ASP scripts and this could be the cause of the problem. is this true??... what can i do if it is??

Alternatively, i fear it may be my script.... although it works in general it may need tweeking for universal acceptance.

PLEASE can someone of vaster intellect than myself find the time to spread the wealth? it will make me very happy and allow me to sleep at night. The script is as follows:

<%@LANGUAGE="VBSCRIPT"%>

<%
if(session("ref") <> "") then update__str = session("ref")
%>
<%
if(session("ref") <> "") then uptime__str = session("ref")
%>
<%
set update = Server.CreateObject("ADODB.Command")
update.ActiveConnection = MM_yearbook_STRING
update.CommandText = "UPDATE Students SET lastvisit = date() WHERE Ref = " + Replace(update__str, "'", "''") + " "
update.CommandType = 1
update.CommandTimeout = 0
update.Prepared = true
update.Execute() ..................NOTE: THIS IS THE INFAMOUS LINE 21!!!!
%>
<%
set uptime = Server.CreateObject("ADODB.Command")
uptime.ActiveConnection = MM_yearbook_STRING
uptime.CommandText = "UPDATE Students SET lasttime = time() WHERE Ref = " + Replace(uptime__str, "'", "''") + " "
uptime.CommandType = 1
uptime.CommandTimeout = 0
uptime.Prepared = true
uptime.Execute()
%>
<%
'Set FS=Server.CreateObject("Scripting.FileSystemObject ")
'Set RS=FS.OpenTextFile(Server.MapPath("counter.txt"), 1, False)
'fcount=RS.ReadLine
'RS.Close
'fcount=fcount+1
'This code is disabled due to the write access security on our server:
'Set RS=FS.OpenTextFile(Server.MapPath("counter.txt"), 2, False)
'RS.Write fcount
'RS.Close
'Set RS=Nothing
'Set FS=Nothing

%>
<%
Session("username") = cStr(Request("username"))
'MagicBeat Server Behavior - 1013 - by Jag S. Sidhu - www.magicbeat.com
%>
<link rel="stylesheet" href="uni.css" type="text/css">
<title>Uniyearbooks</title><body background="fullsizebktop.jpg" link="#CC0000" vlink="#CC0000" alink="#CC0000" text="#CCCCCC">
<table width="728" height="492" border="0" align="center" cellpadding="0" background="whitebk.gif">
<tr>
<td height="116" align="left" valign="top"> <div align="right">
<object classid="clsid27CDB6E-AE6D-11cf-96B8-444553540000" codebase="http://download.macromedia.com/pub/...version=5,0,0,0" width="356" height="116" align="left">
<param name="movie" value="julylogo2003.swf">
<param name="quality" value="high">
<embed src="julylogo2003.swf" width="356" height="116" align="left" quality="high" pluginspage="http://www.macromedia.com/shockwave...=ShockwaveFlash" type="application/x-shockwave-flash"></embed></object>
</div></td>
<td width="120" valign="top"> </td>
<td width="120" valign="top">  </td>
<td width="120" valign="top"> </td>
</tr>
<tr>
<td height="370" colspan="4" align="center" valign="top"> <div align="left">
<table width="90%" align="center">
<tr>
<td width="35%" height="43">
<script language="Javascript">

</script>
</td>
<td width="65%"><%= Session("user") %>,</td>
</tr>
<tr>
<td height="168" valign="top"><img src="awelcome.gif" width="190" height="120"></td>
<td rowspan="2" valign="top"><p align="justify"><strong>Thankyou
for logging onto this year's CARDIFFYEARBOOK.
We are glad you have become part of this new and exciting service.
</strong> </p>
<p align="justify">In
order for the yearbook to prove a success we would be grateful
if you could ensure that you have entered your details into the
database. Remember, your friends will want to read what you have
written about yourself as much as you would like to read what
they have written. </p>
<p align="justify"><strong>Please
<a href="add.asp">Click-Here</a>
to view your current profile before proceeding to the home page.</strong></p>
<p align="justify">Thankyou
for your support and we hope you enjoy the site...</p>
<p align="justify"> </p>
<p align="right"><strong><strong><a href="<A href="mailto:[email protected]"><img">mailt o:[email protected]"><img src="envelope%20wiv%20writtin.jpg" width="310" height="30" border="0" align="right"></a></strong></strong></p></td>
</tr>
<tr>
<td height="122"><blockquote>
<p align="justify">  </p>
</blockquote>
<blockquote>
<p align="justify">  </p>
</blockquote></td>
</tr>
</table>

</div>
<div align="center"></div></td>
</tr>
</table>
<table width="699" border="0" align="center" background="fullsizebkbottom.gif">
<tr>
<td width="81%" height="80" rowspan="2"> </td>
<td width="19%" height="51" valign="bottom"><div align="right"><em>A
Sitebeyondsite production ©Crocker 2004</em></div></td>
</tr>
<tr>
<td height="58" valign="bottom"> </td>
</tr>
</table>

om_prakash · June 7th, 2004, 10:36 PM

I think the query below should be modified to

update.CommandText = "UPDATE Students SET lastvisit = date() WHERE Ref = '" + Replace(update__str, "'", "''") + "' "

Om Prakash

happygv · June 7th, 2004, 11:12 PM

Hi DanCrocker,

I still wonder what you are trying to do using two update statements in succession.

Also what is the DATABASE server you are using? Is that MSSQL server or access? As I don't see any of them supporting DATE() or TIME() functions

OR

DATE() and TIME() are not T-SQL functions.
Can you explain what you are trying to do in those two cases?

Though you do change your code as suggested by OM_PRAKASH, as the next level you could have problems with these two update statements, due to the DATE and TIME functions used.

Cheers!

_________________________
-Vijay G

Strive for Perfection

dancrocker · June 8th, 2004, 04:49 AM

Yea hi,

cheers for the replies...

The database is an Access database. As explained, the main problem i am having is that most people including myself have no problems with the ASP scripting of the site and it runs smoothly and the database functions of data and time work fine..... everything is fine! 'BUT', some people are having troubles and are shown the error described. A friend has indicated that it may be a small problem where Ref maybe case sensitive in some peoples system ... i.e. change all to either Ref or ref and not mix.

Does this solution hold any weight.... it seems a bit easy!!

I did try the alteration to the script as described by this forum and the whole script failed.... :-(

Any additional help will be greatly received.... i'm scratching my head!!

cheers

Imar · June 8th, 2004, 04:58 AM

Hi there,

The Ref part should be executed at the server, so it shouldn't be client dependent.

However, take a look at this:

if(session("ref") <> "") then update__str = session("ref")
%>
<%
if(session("ref") <> "") then uptime__str = session("ref")
%>
<%
....
update.CommandText = "UPDATE Students SET lastvisit = date() WHERE Ref = " + Replace(update__str, "'", "''") + " "

Basically, what you do here, is check if Session("ref") has a value. If it does, update__str will have a valid value.
However, if it doesn't, update__str will be empty, and you end up with a messed up WHERE clause.

Session("Ref") will be empty when the user requests the page before this value has been set, or after the session has timed out. So, if I were you, I'd try to find out if timeouts on the session are indeed causing the problem.

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

dancrocker · June 8th, 2004, 09:34 AM

hi Imar,

thanks for your help.... i think your on to something here. To test it i logged in and as always it ran smoothly for me... however if i passed the page in question, allowed my page to stand for a while, THEN pressed back the same error occured for me.

Does this suggest a time out error is generally responsible for people who get the error initially?... what is the best step to take... is there a code i can insert/modify to remedy the timeout problem?

Imar · June 8th, 2004, 09:49 AM

Yes, I would say so. Can't you ask some of your users if they get the error when they haven't used the app in a while?

You could code a generic "Session protector". When the user logs in, or when the session is started, set a session variable, like this:

Session("LoggedIn") = True

On each page (either directly, or through an include file), check for this variable and redirect accordingly:

If Session("LoggedIn") <> True Then
Response.Redirect("Login.asp?TIMEOUT=True")
End If

With the Timeout=True variable, you can customize the message on Login.asp, to explain to the users their session has times out and they need to login again.

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

dancrocker · June 8th, 2004, 11:44 AM

Imar,

you are a legend for helping!... Not sure exactly what to do though..? i'm bit of a novice and have relied on dreamweaver to do much of my code!

my members login on a page called default.asp ..... this leads to passsuccess.asp where the timeout error occurs. I definately think you are onto something when you explained that a request was given BEFORE the info was sent. To set a session do i need to add a further column to my database or do i simply paste your recommended session code into the ASP? Is there anyway i can delay the info request to ensure it has been sent?

I'm not sure how this really works. if each page has =logged in attached to it then how does the script know when someone has timedout i.e. <>logged in??

I feel we are close to a solution and i must apologise for being an ASP retard!

Cheers.

Dan

Imar · June 8th, 2004, 12:02 PM

No need to apologize. Sessions can be tricky to understand.

When you request the first page of an Web application in IIS, a new Session is created, and Session_OnStart (defined in a file called global.asa in the root of your site is fired). Since the http protocol is stateless, IIS needs a way to hook up the session on the server to the client.
To do that, it hands out cookie that is stored at the client. For each request the client makes, the cookie (called SessionID, I think) is passed back to the server.

A Session timeout defines how long the session can live on the server. If the session has time out, the current user can no longer be connected to an existing session, and is automatically assigned to a new session.

For the duration of the Session, you can access information you have stored in that session. So, if on Login.asp you set:

Session("LoggedIn") = True

this variable will return true as long as the session is alive. When the session is ended explicitly, or times out, Session("LoggedIn") will no longer return true, and the user is redirected to a page you specify.

Add this code at the top of important pages:

If Session("LoggedIn") <> True Then
Response.Redirect("Login.asp?TIMEOUT=True")
End If

Suppose you added it to passsuccess.asp. Then you log in and get redirected to that page. The session is live, Session("LoggedIn") returns true, so you're not redirected. Refresh the page a couple of time. Session("LoggedIn") remains true, because with each request, the session is extended.
Wait at least 20 minutes. At the server, the session will time out. Request the page. Now, Session("LoggedIn") will not return True and you're redirected to Login.asp?TIMEOUT=True

There is no need to store information in a database or create additional columns. The code I described will work on default Web sites, just like that.

Speaking of databases, why are you using Session in the first place? What's the point of storing WHERE clauses in session variables? Can't you just store them in page variables?

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

dancrocker · June 8th, 2004, 01:55 PM

Imar,

You are a star! i appreciate you guiding me through this tricky mess that i've got myself into!! i've inserted the script as you instructed and it works really well redirecting me when i time out.

Although it solves a variation of my problem, will it not seem strange for a member to login and then be directed on their first page to login again. This script is ideal for when people do time out for hanging around inactive, but for the members who are requesting data before it is sent (i.e. the people affected by my problem) will they not go round in circles?.. sign in, then be asked to sign in again... and again.... and never actually get to passsuccess.asp?

Once they are redirected is there anything i can sugeest to the user to ensure they gain access... i.e. is there a problem or security setting that they can reduce to ensure they get access. It seems only a few are not getting the appropriate access. Why do some send and receive info at different speeds compared to the rest of us?? is it their firewall?

Many Many thanks,

Dan

My application is a student yearbook so all data must be recorded as data in a hard database.