SQL query in classic asp using ma access as databse

Question

Here is the statement of select query.. I am getting error as:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.

Dim DataConn
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open "Driver={Driver do Microsoft Access (*.mdb)}; DBQ="& Server.Mappath("db\wsmt.mdb") & ";pwd=openreach;"

Dim Recordset_report
Set Recordset_report = Server.CreateObject("ADODB.Recordset")

Dim sRet

if Request.form("submit1")<>"" then

    Recordset_report.Open "Select WP.CR_No as ""Cr No"", WP.WP_ID as""WP ID"", WP_Name as ""WP Name"","&_
" iif(isnull(ow1),' ',ow1+';')+' '+' '+iif(isnull(ow2),' ',ow2+';')+' '+' '+iif(isnull(ow3),' ',ow3+';')+' '+' '+iif(isnull(ow4),' ',ow4+';')+' '+' '+iif(isnull(ow5),' ',ow5) as ""Owner(s)"","&_
"Type as ""WP Type"", Release, Area, Rec_dt as ""Date Started"", Rec_from as ""Requirement Owner"","&_
"TCD,Perc_comp as ""% Completed"", Complexity, Status,Act_close as ""Actual Closed Date"" from wp, "&_

" inner join ( select wp_id,owner1,(select fullname from userinfo where userid=a.owner1) as ow1,owner2,"&_
" (select fullname from userinfo where userid=a.owner2) as ow2,owner3,"&_
" (select fullname from userinfo where userid=a.owner3) as ow3,owner4,"&_
" (select fullname from userinfo where userid=a.owner4) as ow4,owner5,"&_
" (select fullname from userinfo where userid=a.owner5) as ow5 from allocation a) as t,"&_
" on (wp.wp_id = t.wp_id) where Rec_dt >= #"&Request.form("from")&"# and "&_
" Rec_dt <= #"&Request.form("to")&"#  and not WP.Disable order by Rec_dt, "&_ 

" inner join (select tool_est,wp_id from estimation), "&_
" on (wp.wp_id = Estimation.wp_id) where Rec_dt >= #"&Request.form("from")&"# and ,"&_
" Rec_dt <= #"&Request.form("to")&"#  and not WP.Disable order by Rec_dt ", DataConn

JoseTeixeira JoseTeixeira · Accepted Answer · 2013-06-12T12:48:51

you're doing "from wp, inner join" => must remove the ","
after better reviewing it you have more situations where you are implying "," where you shouldn't. Gonna try to point out a few, but you should review the query.

You need to remove the ",":
as t, on (wp.wp_id = t.wp_id)

Also, after your order by order by Rec_dt you have:
, inner join (select tool_est,wp_id from estimation), on
The "," before the inner join and the one before the on both need to be removed. Also, in the line before the last one you have and , this comma two should be removed.

Lastly, your last inner join shouldn't be where it is, but I still didn't got to a conclusion wether you really want a inner join in there or if it should be worked out to some kind of union.

SQL query in classic asp using ma access as databse

1 Answers