Skip navigation
Currently Being Moderated

Syntax Errors in Recordset, Dreamweaver 8

Apr 24, 2012 8:08 AM

Hi All!

 

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.

 

Any help is much appreciated, thanks in advance!!

 
Replies
  • Sudarshan Thiagarajan
    4,000 posts
    Oct 15, 2010
    Currently Being Moderated
    Apr 24, 2012 8:42 AM   in reply to MTAnnie3356

    What is it you're trying to achieve with this query? Could you elaborate?

     
    |
    Mark as:
  • Sudarshan Thiagarajan
    4,000 posts
    Oct 15, 2010
    Currently Being Moderated
    Apr 24, 2012 9:11 AM   in reply to MTAnnie3356

    Do you have this uploaded on a remote server for us to view?

     

    Also, did you try implementing a cfLoop earlier?

     
    |
    Mark as:
  • Sudarshan Thiagarajan
    4,000 posts
    Oct 15, 2010
    Currently Being Moderated
    Apr 24, 2012 9:30 AM   in reply to MTAnnie3356

    Can you upload it to a remote server? And give us a link? That would help us in drilling down to diagnostics right away.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 24, 2012 10:02 AM   in reply to MTAnnie3356

    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:

     

    SELECT LastName, FirstName, MiddleName, Generation, PartyType, FullCaseNo, DocketSheet

    FROM PartyInformation LEFT JOIN CaseInformation

      WHERE 1=1

     
    |
    Mark as:
  • Sudarshan Thiagarajan
    4,000 posts
    Oct 15, 2010
    Currently Being Moderated
    Apr 24, 2012 10:47 AM   in reply to MTAnnie3356

    That's sad to hear.

     

    Anyway, if you're continuing to work on this, why dont you try replacing this:

     

    SELECT LastName, FirstName, MiddleName, Generation, PartyType

    FROM [Party Information]

    WHERE 1=1

    with this:

    SELECT LastName, FirstName, MiddleName, Generation, PartyType

    FROM PartyInformation

    WHERE 0=1

    WHERE 1=1 is usually a SQL code to prevent injections and to increase performance of the query you're running.

    WHERE 0=1 will usually look up all records in the table you're querying into - this is what you're trying to achieve here anyway.

     

    and rename the table on your DB from Party Information to PartyInformation

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points