Hi, I have a website that was developed a few years ago for me, site is proburn.com and has been happily running for a few years, however I have had to chnage hosts and I am now gwetting all sorts of errors. latest one is where a customer is trying to create a discs, yhis is also happening all over the site but if I can fix thsi I think the other errors will be the same.

here is the code and the full error is

Microsoft JET Database Engine error '80040e14'
Syntax error (missing operator) in query expression 'Description= "ss" AND visitorid= 2007271121302 AM0 AND IsInBasket=false'.
/proburn/createdisc.asp, line 50

<%
Option Explicit
Response.Buffer=true
'redirect code goes here


%>
<!--#include virtual="/inc_returntome.asp"-->
<!-- #include file="../tracker/tracker.asp"-->
<!-- #include file="inc_connections.asp"-->
<!-- #include file="../config.asp"-->
<%
Dim RST
Dim sqlStr
Dim redirectpage
Dim theStatus
Dim DiscName
Dim DiscDescription



if request("r")<>"" Then
redirectpage = request("r")
elseif request.form("r")<>"" Then
redirectpage = request.form("r")
else
redirectpage = "search.asp"
End If


if request.form("go")<>"" Then
MakeDisc()
if theStatus="" then
Response.Redirect redirectpage
End If
End IF
'==========================================================================================
function MakeDisc()


'   Response.write sqlstr & "<br>"
If  request.form("DiscName")<>"" Then DiscName = request.form("DiscName")
If  request.form("DiscDescription")<>"" Then DiscDescription = request.form("DiscDescription")



Set RST = Server.CreateObject("ADODB.Recordset")
if session("UserID")>0 Then
sqlStr = "select * from proburndisc where Description='" & replace(DiscDescription,"'","''") & "' AND userid=" & session("userid") & " AND IsInBasket=false"
else
sqlStr = "select * from proburndisc where Description='" & replace(DiscDescription,"'","''") & "' AND visitorid=" & session("visitorid") & " AND IsInBasket=false"
End If
'   response.write sqlstr
RST.Open sqlStr, strConnectProBurn, adOpenKeySet, adLockOptimistic
If RST.EOF Then
if session("UserID")>0 Then
RST.AddNew array("UserID","DiscName","Description","NoTracks","TotalValue","TotalDuration","DateCreated","LastActivity"), array(session("userid"),DiscName,DiscDescription,0,0,0,now(),now() )
else
RST.AddNew array("VisitorID","DiscName","Description","NoTracks","TotalValue","TotalDuration","DateCreated","LastActivity"), array(session("VisitorID"),DiscName,DiscDescription,0,0,0,now(),now() )
end if
if instr(redirectpage,"?") Then
redirectpage=redirectpage & "&id=" & RST("DiscID")
else
redirectpage=redirectpage & "?id=" & RST("DiscID")
End If
Session("ActiveDisc") = RST("DiscID")
Session("DiscName") = DiscName
Session("DiscDescription") = DiscDescription
else
theStatus = "Sorry, you already have a disc with this Description"
End If
RST.close
Set RST=Nothing
end function
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title><%=cPageTitle%>Create Pro-Burn Disc</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="../style.css" rel="stylesheet" type="text/css">
<script>
function ValidateForm(thisForm)
{


var AlertString="Oops!\n\n";
var ErrorsFound = false;


if (thisForm.DiscName.value == 0){AlertString += "\n DISC NAME is required";ErrorsFound = true;}
if (thisForm.DiscDescription.value == 0){AlertString += "\n DESCRIPTION is required";ErrorsFound = true;}
if (ErrorsFound == true){alert (AlertString);return false;}else{return true;}
}
</script>
</head>


<body onload="document.createDisc.DiscName.focus()">
<!--#include file="../header.asp"-->
<table border="0" align="center" cellpadding="0" cellspacing="0" id="content">
<tr>
<td valign="top"><!--#include file="pb_header.asp" -->
<table width="550" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td><img src="../pghd/create.gif" width="149" height="27"></td>
</tr>
</table>
<br>
<table width="550" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td class="section"><span class="sectionhdr"><b><img src="../pghd/sq.gif" hspace="5" align="absmiddle"></b>Create a Pro-Burn Disc</span><br>
<br>
Please enter a title for the Pro-Burn disc that you are about
to create. This will be printed on the CD.<br>
<br>
eg. Name: <b>Fiona's Favourites</b> &nbsp;Description: <b>Vol 5</b><br>
<br>
Would be printed on to your CD as: <b>Fiona's Favourites -
Vol 5</b><br>
<br>
You will have the opportunity to change the name later if you
like.<br>
<br>
<span class="theStatus"><b><%=theStatus%></b></span>
<div align="center">
<form name="createDisc" action="createdisc.asp" method="post" onSubmit="return ValidateForm(this);">
Name
<input name="DiscName" type="text" id="DiscName" value="<%=DiscName%>" size="10" maxlength="30" class="input">
&nbsp;&nbsp; Description
<input name="DiscDescription" type="text" id="DiscDescription" value="<%=DiscDescription%>" size="10" maxlength="30" class="input">
&nbsp;&nbsp;
<input type="submit" value="create disc" class="btn">
<input name="r" type="hidden" value="<%=redirectpage%>">
<input name="go" type="hidden" value="y">
</form>
</div>
<br>
Click 'create disc' to continue.<br>
<br>
<a href="showdiscs.asp">If you have already created a disc that you would like to use please click here</a><br>
<div align="center"></div>
</td>
</tr>
</table></td>
</tr>
</table>
<!--#include file="../footer.asp"-->

</body>
</html>

Your problem appears to be this line:

' response.write sqlstr
RST.Open sqlStr, strConnectProBurn, adOpenKeySet, adLockOptimistic

What is the value of strConnectProBurn? Since you say you changed hosts, that value would more than likely also need to be changed to reflect the new location of the database.

The variable (strConnectProBurn) isn't being declared or set on this page, is it being set in one of the include files? Possibly in <!-- #include file="inc_connections.asp"-->?

Find that variable, set it to the current location of the database, and you should find that most (if not all) of your problems will disappear.

Hi thanks for that reply however the connection file looks like this checked the path and it looks correct to me, as far as I can see anyway

<!-- METADATA TYPE="typelib"
FILE="C:\Program Files\Common Files\SYSTEM\ADO\MSADO15.DLL" -->
<%
Dim strConnectOrder
strConnectOrder = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("..\databases\orders.mdb")
Dim strConnectProburn
strConnectProburn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("..\databases\proburn.mdb")
Dim strConnectMembers
strConnectMembers = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("..\databases\Members.mdb")
Dim strConnectMailing
strConnectMailing = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("..\databases\mailing.mdb")
%>

Ok, can you provide me with a couple things then?

Add this code directly above this line 'response.write sqlstr

response.write sqlStr & "<br />" & strConnectProBurn
response.end

Run the page, then give me the last two lines of text from that page.

From there I should be able to tell if there is an issue with either variable.

Sorry it has taken me so long to reply it appears that the visitorid variable has to be a number it is configured in this code

if request.ServerVariables("HTTPS")="on" then
' if request.Cookies("Session").HasKeys then
' session("UserID")=request.Cookies("Session")("UserID")
' session("username")=request.Cookies("Session")("UserName")
' session("Email")=request.Cookies("Session")("Email")
' session("AccessLevel") = request.Cookies("Session")("AccessLevel")
' session("cWebsiteReferrer") = request.Cookies("Session")("cWebsiteReferrer")
' session("cWebsiteEntryPage") = request.Cookies("Session")("cWebsiteEntryPage")
' session("VisitorID") = request.Cookies("Session")("VisitorID")
' session("t")="y"
' end if
end if

'**********************************************
'* Session Management *
'**********************************************
if session("userid")="" then 'Make sure session("userid")=0 if it is empty
session("userid")=0
end if
if request("logout")="y" Then 'Perform logout and clear current session variables.
session("username")=cEmail_Guest
session("Email")=""
session("userid")=0
session("AccessLevel")=""
session("ActiveDisc")=""
response.Cookies("Session")("UserID")=0
response.Cookies("Session")("UserName")=""
response.Cookies("Session")("Email")=""
response.Cookies("Session")("AccessLevel") = ""
response.Cookies("Session").Expires=now()-1000
end if

Dim LoginLink, LogoutLink, LoginOutText, LoginOutBtn
if request.ServerVariables("QUERY_STRING") <> "" Then
LoginLink = "/login.asp?" & request.ServerVariables("QUERY_STRING") & "&returnpath=" & server.urlencode(request.ServerVariables("URL"))
LogoutLink = request.ServerVariables("URL") & "?" & request.ServerVariables("QUERY_STRING") & "&logout=y"
else
LoginLink = "/login.asp?returnpath=" & server.urlencode(request.ServerVariables("URL"))
LogoutLink = request.ServerVariables("URL") & "?logout=y"
end if
if session("userid")=0 Then
LoginOutText = "<a href=""" & LoginLink & """>EXISTING USER? LOG IN HERE</a>"
LoginOutBtn = "<a href=""" & LoginLink & """><img src=""/btn/login.gif"" border=""0""></a>"
else
LoginOutText = "<a href=""" & logoutLink & """ title=""You are currently logged in using " & session("username") & """>LOGOUT</a>"
LoginOutBtn = "<a href=""" & logoutLink & """ title=""You are currently logged in using " & session("username") & """><img src=""/btn/logout.gif"" border=""0""></a>"
end if


Dim cPageURL:cPageURL = request.ServerVariables("URL")
Dim cPageReferrer:cPageReferrer = request.ServerVariables("HTTP_REFERER")
if instr(cPageReferrer,cDomain) = false then cPageReferrer="index.asp"
if request.ServerVariables("QUERY_STRING")<>"" Then cPageURL = cPageURL & "?" & request.ServerVariables("QUERY_STRING")
if session("cWebsiteReferrer")="" OR session("cWebsiteEntryPage")="" Then
session("cWebsiteReferrer") = request.ServerVariables("HTTP_REFERER")
session("cWebsiteEntryPage") = "http://" & request.ServerVariables("SERVER_NAME") & cPageURL
if len(session("cWebsiteReferrer"))>255 then session("cWebsiteReferrer")=left(session("cWebsiteReferrer"),252) & "..."
if len(session("cWebsiteEntryPage"))>255 then session("cWebsiteEntryPage")=left(session("cWebsiteEntryPage"),252) & "..."
response.Cookies("Session")("cWebsiteReferrer") = session("cWebsiteReferrer")
response.Cookies("Session")("cWebsiteEntryPage") = session("cWebsiteEntryPage")
end if

Sorry If I seem a bit daft here but the site was set up by someone else and what I know about asp code is negligible. I am not even sure what you mean by run the page is this on the website?

It looks like the value returned for user id is bringing in the am or pm part of the time which is changing it from a number to a string, how can I change this?

Scott

Ok, then what you're saying is that the new server has added the " AM0" to the visitorID, whereas the old server didn't.

If that is the case, you'll have to choose one of two paths.

1. Find the line of code that is setting the VisitorID on the client's computer, then strip off the unwanted part. Or,
2. Make sure that any calls retrieving the VisitorID from the client's computer strips off the unwanted part.

My suggestion is to go with #1. Doing so will prevent having to change every instance of Session("VisitorID")

I've looked through all the code you've provided up to this point and do not see where it is being set. I'm guessing it is in one of the include files.

So that you don't end up pasting your entire site here, you're welcome to PM or email me so we can work something out.

you are a star thanks very much

You're very welcome. Glad I could help!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.