I'm struggling to figure out Dreamweaver's syntax for a Recordset I'm building. I'm using Dreamweaver 8, ColdFusion 9 to connect with my database, and Windows 7.

I have to start by admitting I copied code from an similar program, and am trying to get it to work for me.

Here's the error I get when I "test" the recordset:

Syntax error. in query expression '1=1

Here's the query:

SELECT LastName, FirstName, MiddleName, Generation, PartyType

FROM [Party Information]

WHERE 1=1

SELECT FullCaseNo, DocketSheet

FROM [Case Information]

WHERE 1=1

<cfif IsDefined("Form.LastName") and Form.LastName NEQ "">

AND PartyInformation.Last Like'#Form.LastName#%'

</cfif>

<cfif IsDefined("Form.FirstName") and Form.FirstName NEQ "">

AND PartyInformation.First Like'#Form.FirstName#%'

</cfif>

<cfif IsDefined("Form.CaseNo") and Form.CaseNo NEQ "">

AND CaseInformation.FullCaseNo ='#Form.CaseNo#'

</cfif>

ORDER BY LastName, FirstName, MiddleName, FullCaseNo

For the sake of just seeing I deleted "WHERE 1=1" just to see if it would run, and I got another syntax error in the FROM clause. In case this wasn't already obvious I'm brand new to Dreamweaver and just trying to get this thing to work.

I don't use ColdFusion, but your SQL query doesn't make sense. For example, your table names are [Party Information] and [Case Information]. Normally square brackets and spaces should not be allowed in database table names, unless that's the CF way of escaping names with spaces.

Even so, the WHERE clauses use PartyInformation and CaseInformation. Also, the column names are different. In the SELECT clause, you use FirstName and LastName, but the WHERE clauses use First and Last.

Finally, I suspect that you need to use LEFT JOIN. So, the beginning of the query would look like this: