problem with query, expected end of statement error?

bikebanditcom · Oct 15, 2003

i get the following error from this page:

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/pbc/reporting/TMPavoprmttwj.asp, line 13

rs = "SELECT tblSalesmen.salesmanName, tblSales.....

heres the query

SELECT tblSalesmen.salesmanName, tblSales.saleDate, tblSales.slabDate, tblSales.estInstall, tblSales.estPaymen, tblSales.projectName, tblSales.phase, tblSales.lotID, tblSales.customer, tblSalesPackages.packageLevelId, tblSalesPackages.kbamnt, tblSalesPackages.pbcamnt, tblSalesPackages.status, tblSalesPackages.cancelDate
FROM (tblSalesmen INNER JOIN tblSales ON tblSalesmen.salesmanID = tblSales.salesman) INNER JOIN tblSalesPackages ON tblSales.saleID = tblSalesPackages.salesID
WHERE (((tblSalesPackages.status)="Cancelled&quot

);

the code:

rs = "SELECT tblSalesmen.salesmanName, tblSales.saleDate, tblSales.slabDate, tblSales.estInstall, tblSales.estPaymen, tblSales.projectName, tblSales.phase, tblSales.lotID, tblSales.customer, tblSalesPackages.packageLevelId, tblSalesPackages.kbamnt, tblSalesPackages.pbcamnt, tblSalesPackages.status, tblSalesPackages.cancelDate FROM (tblSalesmen INNER JOIN tblSales ON tblSalesmen.salesmanID = tblSales.salesman) INNER JOIN tblSalesPackages ON tblSales.saleID = tblSalesPackages.salesID WHERE (((tblSalesPackages.status)="Cancelled&quot

);"

any idea anybody?

VBslammer · Oct 15, 2003

Use single quotes around 'Cancelled'

VBSlammer

Unemployed in Houston, Texas

bikebanditcom · Oct 15, 2003

tried that?? doesnt work, what can i do?

here's my entire page:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%

salesman = request.form("salesman&quot

set pbcDB = server.createObject("ADODB.Connection&quot

pbcDB.open "pbc"

theSQL = "SELECT tblSalesmen.salesmanName, tblSales.saleDate, tblSales.slabDate, tblSales.estInstall, tblSales.estPaymen, tblSales.projectName, tblSales.phase, tblSales.lotID, tblSales.customer, tblSalesPackages.packageLevelId, tblSalesPackages.kbamnt, tblSalesPackages.pbcamnt, tblSalesPackages.status, tblSalesPackages.cancelDate FROM (tblSalesmen INNER JOIN tblSales ON tblSalesmen.salesmanID = tblSales.salesman) INNER JOIN tblSalesPackages ON tblSales.saleID = tblSalesPackages.salesID WHERE (((tblSalesPackages.status)="Cancelled&quot

)'"

pbcDB.Execute(theSQL)

<% on error resume next %>

<html>
<head>
<title>Cancelled Report</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="css/style.css" rel="stylesheet" type="text/css">
<style type="text/css">

</style>
</head>

<body>
<h1>Cancelled Sales Report </h1>
Today is <%= Now %>
This is the cancelled sales report for <%= salesman %>
<form name="cancelReport" method="post" action="salesEdit.asp">
<table width="636" border="1" align="left" cellpadding="4" cellspacing="4">

<tr>
<td width="55" height="30" valign="top">Sales ID</td>
<td width="83" valign="top">Sale Date</td>
<td width="121" valign="top">Project</td>
<td width="79" valign="top">Lot </td>
<td width="88" valign="top">Phase</td>
<td width="120" valign="top">Cancel Date</td>
</tr>
<tr>
<td height="28" valign="top"></td>
<td valign="top"></td>
<td valign="top"></td>
<td valign="top"></td>
<td valign="top"></td>
<td valign="top"></td>
</tr>
</table>
 
 
 

 
<input type="submit" name="Submit2" value="Save Excel Spreadsheet">
 
</form>
 
 
<A href="default.asp">Home</A> | <A href="login.asp">Login</A>
| <A href="salesEntry.asp">Sales Entry</A> | <A href="salesEdit.asp">Sales Editing</A>
| <A href="scheduling.asp">Scheduling</A> | <A href="register.asp">Register
A New User</A> | <A href="dbControlPanel.asp">DB Control Panel</A> | Logout
Copyright 2003 PBC Technologies Designed By <a href="mailto:%20dhayden@wecantlose.com">Dan
Hayden, Jr</a> 
<a href="

http://www.wecantlose.com">We

Can't Lose, LLC</a> 
 
</body>
</html>
<%

pbcDB.close
set pbcDB = Nothing

%>

clarkin · Oct 15, 2003

watch "s and ()s. this also needs to all be on one line:
[tt] theSQL = "SELECT tblSalesmen.salesmanName, tblSales.saleDate, tblSales.slabDate, tblSales.estInstall, tblSales.estPaymen, tblSales.projectName, tblSales.phase, tblSales.lotID, tblSales.customer, tblSalesPackages.packageLevelId, tblSalesPackages.kbamnt, tblSalesPackages.pbcamnt, tblSalesPackages.status, tblSalesPackages.cancelDate FROM (tblSalesmen INNER JOIN tblSales ON tblSalesmen.salesmanID = tblSales.salesman) INNER JOIN tblSalesPackages ON tblSales.saleID = tblSalesPackages.salesID WHERE tblSalesPackages.status ='Cancelled'"[/tt]

Posting code? Wrap it with code tags: [ignore]

Code:

[/ignore][code]CodeHere

[ignore][/code][/ignore].

VBslammer · Oct 15, 2003

I pasted you sql in an editor and you still didn't have it right, there was a stray single quote, and you didn't remove the double-quotes around Cancelled. This should work:

Code:

  theSQL = &quot;SELECT tblSalesmen.salesmanName, tblSales.saleDate, tblSales.slabDate, tblSales.estInstall, tblSales.estPaymen, tblSales.projectName, tblSales.phase, tblSales.lotID, tblSales.customer, tblSalesPackages.packageLevelId, tblSalesPackages.kbamnt, tblSalesPackages.pbcamnt, tblSalesPackages.status, tblSalesPackages.cancelDate FROM (tblSalesmen INNER JOIN tblSales ON tblSalesmen.salesmanID = tblSales.salesman) INNER JOIN tblSalesPackages ON tblSales.saleID = tblSalesPackages.salesID WHERE (((tblSalesPackages.status)='Cancelled'))&quot;

Another reason it might not work is if something is misspelled - like maybe [tt]tblSales.estPaymen[/tt] instead of [tt]tblSales.estPayment[/tt]

VBSlammer

Unemployed in Houston, Texas

bikebanditcom · Oct 15, 2003

this is what i have, but i get page cannnot be displayed:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%

salesman = request.form("salesman&quot

set pbcDB = server.createObject("ADODB.Connection&quot

pbcDB.open "pbc"

theSQL = "SELECT tblSalesmen.salesmanName, tblSales.saleDate, tblSales.slabDate, tblSales.estInstall, tblSales.estPaymen, tblSales.projectName, tblSales.phase, tblSales.lotID, tblSales.customer, tblSalesPackages.packageLevelId, tblSalesPackages.kbamnt, tblSalesPackages.pbcamnt, tblSalesPackages.status, tblSalesPackages.cancelDate FROM (tblSalesmen INNER JOIN tblSales ON tblSalesmen.salesmanID = tblSales.salesman) INNER JOIN tblSalesPackages ON tblSales.saleID = tblSalesPackages.salesID WHERE (((tblSalesPackages.status)='Cancelled'))"

pbcDB.Execute(theSQL)

%>

<html>
<head>
<title>Cancelled Report</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="css/style.css" rel="stylesheet" type="text/css">
<style type="text/css">

</style>
</head>

<body>
<h1>Cancelled Sales Report </h1>
Today is <%= Now %>
This is the cancelled sales report for <%= salesman %>
<form name="cancelReport" method="post" action="salesEdit.asp">
<table width="636" border="1" align="left" cellpadding="4" cellspacing="4">

<tr>
<td width="55" height="30" valign="top">Sales ID</td>
<td width="83" valign="top">Sale Date</td>
<td width="121" valign="top">Project</td>
<td width="79" valign="top">Lot </td>
<td width="88" valign="top">Phase</td>
<td width="120" valign="top">Cancel Date</td>
</tr>
<tr>
<td height="28" valign="top"></td>
<td valign="top"></td>
<td valign="top"></td>
<td valign="top"></td>
<td valign="top"></td>
<td valign="top"></td>
</tr>
</table>
 
 
 

 
<input type="submit" name="Submit2" value="Save Excel Spreadsheet">
 
</form>
 
 
<A href="default.asp">Home</A> | <A href="login.asp">Login</A>
| <A href="salesEntry.asp">Sales Entry</A> | <A href="salesEdit.asp">Sales Editing</A>
| <A href="scheduling.asp">Scheduling</A> | <A href="register.asp">Register
A New User</A> | <A href="dbControlPanel.asp">DB Control Panel</A> | Logout
Copyright 2003 PBC Technologies Designed By <a href="mailto:%20dhayden@wecantlose.com">Dan
Hayden, Jr</a> 
<a href="

http://www.wecantlose.com">We

Can't Lose, LLC</a> 
 
</body>
</html>
<%

pbcDB.close
set pbcDB = Nothing

%>

whatta you think?
dan

VBslammer · Oct 16, 2003

Do you have 'Show friendly HTTP error messages' checked in IE Advanced options? If so, un-check it and try again to see if you get a more useful error message.

VBSlammer

Unemployed in Houston, Texas

bikebanditcom · Oct 16, 2003

thanks for the advice, i was quickly able to determine the problem, fixed my dsn and now am not having these problems, but no my new problem is this:

Microsoft VBScript compilation error '800a0409'

Unterminated string constant

/pbc/reporting/cancelledReport.asp, line 12

set cancelledSQL = pbcDB.Execute("SELECT tblSalesmen.salesmanName.....

i know the exact piece of code i've put in there that is doing this, the problem is i dont know how to call a variable properly in the sql statement. could you take a look at my statement and determine how i should write this? thanks

my page

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
salesman = request.Form("salesman&quot

Dim theSQL, pbcDB

set pbcDB = server.createObject("ADODB.Connection&quot

pbcDB.open "pbc"

set cancelledSQL = pbcDB.Execute("SELECT tblSalesmen.salesmanName, tblSales.saleDate, tblSales.slabDate, tblSales.estInstall, tblSales.estPaymen, tblSales.projectName, tblSales.phase, tblSales.lotID, tblSales.customer, tblSalesPackages.packageLevelId, tblSalesPackages.kbamnt, tblSalesPackages.pbcamnt, tblSalesPackages.status, tblSalesPackages.cancelDate FROM (tblSalesmen INNER JOIN tblSales ON tblSalesmen.salesmanID = tblSales.salesman) INNER JOIN tblSalesPackages ON tblSales.saleID = tblSalesPackages.salesID WHERE (((tblSalesmen.salesmanName)='<%= salesman %>) AND (((tblSalesPackages.status)='Cancelled'))&quot

%>

<html>
<head>
<title>Cancelled Report</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="css/style.css" rel="stylesheet" type="text/css">
<style type="text/css">

</style>
</head>

<body>
<h1>Cancelled Sales Report </h1>
Today is <%= Now %>
This is the cancelled sales report for <%= salesman %>
<form name="cancelReport" method="post" action="salesEdit.asp">
<table width="636" border="1" align="left" cellpadding="4" cellspacing="4">

<tr>
<td width="55" height="30" valign="top">Sales ID</td>
<td width="83" valign="top">Sale Date</td>
<td width="121" valign="top">Project</td>
<td width="79" valign="top">Lot </td>
<td width="88" valign="top">Phase</td>
<td width="120" valign="top">Cancel Date</td>
</tr>
<tr>
<td height="28" valign="top"></td>
<td valign="top"></td>
<td valign="top"><%= cancelledSQL("saleDate&quot

%></td>
<td valign="top"></td>
<td valign="top"></td>
<td valign="top"></td>
</tr>
</table>
 
 
 

 
<input type="submit" name="Submit2" value="Save Excel Spreadsheet">
 
</form>
 
 
<A href="default.asp">Home</A> | <A href="login.asp">Login</A>
| <A href="salesEntry.asp">Sales Entry</A> | <A href="salesEdit.asp">Sales Editing</A>
| <A href="scheduling.asp">Scheduling</A> | <A href="register.asp">Register
A New User</A> | <A href="dbControlPanel.asp">DB Control Panel</A> | Logout
Copyright 2003 PBC Technologies Designed By <a href="mailto:%20dhayden@wecantlose.com">Dan
Hayden, Jr</a> 
<a href="

http://www.wecantlose.com">We

Can't Lose, LLC</a> 
 
</body>
</html>
<%

pbcDB.close
set pbcDB = Nothing

%>

xaviero · Oct 16, 2003

you are already in an ASP code area so instead of using
<%= salesman %>

use
& salesman &

that should do it for you

-X

bikebanditcom · Oct 16, 2003

THANKS! let me go try that out. you guys are the best.

bikebanditcom · Oct 16, 2003

thanks for the advice, now i have one other problem that has surfaced since fixing that

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

[Microsoft][ODBC Microsoft Access Driver] Missing ), ], or Item in query expression '(((tblSalesmen.salesmanName)= Hiram) AND (((tblSalesPackages.status)='Cancelled'))'.

/pbc/reporting/cancelledReport.asp, line 12

its feeding in a salesman name but then craps out here? what am i missing?

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
salesman = request.Form("salesman&quot

Dim theSQL, pbcDB

set pbcDB = server.createObject("ADODB.Connection&quot

pbcDB.open "pbc"

set cancelledSQL = pbcDB.Execute("SELECT tblSalesmen.salesmanName, tblSales.saleDate, tblSales.slabDate, tblSales.estInstall, tblSales.estPaymen, tblSales.projectName, tblSales.phase, tblSales.lotID, tblSales.customer, tblSalesPackages.packageLevelId, tblSalesPackages.kbamnt, tblSalesPackages.pbcamnt, tblSalesPackages.status, tblSalesPackages.cancelDate FROM (tblSalesmen INNER JOIN tblSales ON tblSalesmen.salesmanID = tblSales.salesman) INNER JOIN tblSalesPackages ON tblSales.saleID = tblSalesPackages.salesID WHERE (((tblSalesmen.salesmanName)= '& salesman &') AND (((tblSalesPackages.status)='Cancelled'))&quot

%>

<html>
<head>
<title>Cancelled Report</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="css/style.css" rel="stylesheet" type="text/css">
<style type="text/css">

</style>
</head>

<body>
<h1>Cancelled Sales Report </h1>
Today is <%= Now %>
This is the cancelled sales report for <%= salesman %>
<form name="cancelReport" method="post" action="salesEdit.asp">
<table width="502" border="1" align="left" cellpadding="4" cellspacing="4">

<tr>
<td width="75" height="30" valign="top">Sale Date</td>
<td width="63" valign="top">Project</td>
<td width="121" valign="top">Lot</td>
<td width="79" valign="top">Phase</td>
<td width="88" valign="top">Cancel Date</td>
</tr>
<tr>
<td height="28" valign="top"><%= cancelledSQL("saleDate&quot

%></td>
<td valign="top"><%= cancelledSQL("projectName&quot

%></td>
<td valign="top"><%= cancelledSQL("lotID&quot

%></td>
<td valign="top"><%= cancelledSQL("phase&quot

%></td>
<td valign="top"><%= cancelledSQL("cancelDate&quot

%></td>
</tr>
</table>
 
 
 

 
<input type="submit" name="Submit2" value="Save Excel Spreadsheet">
 
</form>
 
 
<A href="default.asp">Home</A> | <A href="login.asp">Login</A>
| <A href="salesEntry.asp">Sales Entry</A> | <A href="salesEdit.asp">Sales Editing</A>
| <A href="scheduling.asp">Scheduling</A> | <A href="register.asp">Register
A New User</A> | <A href="dbControlPanel.asp">DB Control Panel</A> | Logout
Copyright 2003 PBC Technologies Designed By <a href="mailto:%20dhayden@wecantlose.com">Dan
Hayden, Jr</a> 
<a href="

http://www.wecantlose.com">We

Can't Lose, LLC</a> 
 
</body>
</html>
<%

pbcDB.close
set pbcDB = Nothing

%>

VBslammer · Oct 16, 2003

Try this:

Code:

Set cancelledSQL = pbcDB.Execute(&quot;SELECT tblSalesmen.salesmanName, tblSales.saleDate, tblSales.slabDate, tblSales.estInstall, tblSales.estPaymen, tblSales.projectName, tblSales.phase, tblSales.lotID, tblSales.customer, tblSalesPackages.packageLevelId, tblSalesPackages.kbamnt, tblSalesPackages.pbcamnt, tblSalesPackages.status, tblSalesPackages.cancelDate FROM (tblSalesmen INNER JOIN tblSales ON tblSalesmen.salesmanID = tblSales.salesman) INNER JOIN tblSalesPackages ON tblSales.saleID = tblSalesPackages.salesID WHERE (((tblSalesmen.salesmanName)= '&quot; & salesman & &quot;') AND (((tblSalesPackages.status)='Cancelled'))&quot;)

Your problem is that you didn't break the string to insert the variable, ie the variable shouldn't be contained inside the string:

Code:

  &quot;... )= '&quot; & salesman & &quot;') AND ...&quot;

VBSlammer

Unemployed in Houston, Texas

bikebanditcom · Oct 16, 2003

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

[Microsoft][ODBC Microsoft Access Driver] Missing ), ], or Item in query expression '(((tblSalesmen.salesmanName)= 'Hiram') AND (((tblSalesPackages.status)='Cancelled'))'.

/pbc/reporting/cancelledReport.asp, line 8

TwoOdd · Oct 16, 2003

AND (((tblSalesPackages.status)=

Delete one of the ( after the AND

AND ((tblSalesPackages.status)=

TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

bikebanditcom · Oct 16, 2003

that was it thanks so much, now im working on this statement and am getting

Microsoft VBScript compilation error '800a03ee'

Expected ')'

/pbc/reporting/xlReports/TMPz49damvdu9.asp, line 8

set compReport = pbcDB.execute("SELECT tblLots.lotNum, tblSales.saleDate, tblSales.projectName, tblSales.phase, tblSales.kbAmnt, tblSales.PBCAmnt, tblSales.VOO, tblSales.billed, tblSalesPackages.status FROM (tblSales INNER JOIN tblLots ON tblSales.lotID = tblLots.lotNum) INNER JOIN tblSalesPackages ON tblSales.saleID = tblSalesPackages.salesID WHERE (((tblSales.billed) Is Null) AND ((tblSalesPackages.status)="Complete&quot

))&quot

what is wrong here? i've counted all my parens and all are right, i think..

bikebanditcom · Oct 16, 2003

fixed it, i needed to remove the quotes around from complete, now i have this problem

same error msg

set noBillSQL = pbcDB.execute("SELECT tblLots.lotNum, tblSales.saleDate, tblSales.projectName, tblSales.phase, tblSales.kbAmnt, tblSales.PBCAmnt, tblSales.VOO, tblSales.billed, tblSalesPackages.status FROM (tblSales INNER JOIN tblLots ON tblSales.lotID = tblLots.lotNum) INNER JOIN tblSalesPackages ON tblSales.saleID = tblSalesPackages.salesID WHERE ((tblSales.saleDate) Between #"date1"# And #"date2"#) AND ((tblSales.billed) Is Null) AND ((tblSalesPackages.status)='Complete');&quot

VBslammer · Oct 17, 2003

Code:

set noBillSQL = pbcDB.execute(&quot;SELECT tblLots.lotNum, tblSales.saleDate, tblSales.projectName, tblSales.phase, tblSales.kbAmnt, tblSales.PBCAmnt, tblSales.VOO, tblSales.billed, tblSalesPackages.status FROM (tblSales INNER JOIN tblLots ON tblSales.lotID = tblLots.lotNum) INNER JOIN tblSalesPackages ON tblSales.saleID = tblSalesPackages.salesID WHERE ((tblSales.saleDate) Between #&quot; & date1 & &quot;# And #&quot; & date2 & &quot;#) AND ((tblSales.billed) Is Null) AND ((tblSalesPackages.status)='Complete');&quot;)

VBSlammer

Unemployed in Houston, Texas

bikebanditcom · Oct 17, 2003

muy bueno, gracias senor

dan

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

problem with query, expected end of statement error?

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor