Insert Query using where clause

incorrect syntax near the keyword 'where'

Try substituting cfoutput for the cfquery tags so you can see the actual sql that's being constructed and then post the sql here.

looks like you had an extra )

<cfquery datasource="btadb">
  INSERT INTO titles
            (Accountnbr, groupid, titleid, titledescription)
  VALUES
            (#session.accountnbr#,
         #listgetat(line, 2, ",")#,
         #listgetat(line, 3, ",")#,
         '#REReplace(listgetat(line, 4, ","), "^""(.+)""$", "\1")#'
  Where #listgetat(line, 1, ",")# = #session.accountnbr# and
        #listgetat(line, 2, ",")# = 0
</cfquery>

there are two where clauses

if any of those vars are empty than you run into a problem

What is the value of #listgetat(line, 1, ",")#  and #listgetat(line, 2, ",")#  and also session.accountnbr too

This query looks suspicious:
INSERT INTO titles
            (Accountnbr, groupid, titleid, titledescription)
  VALUES
            (#session.accountnbr#,
         #listgetat(line, 2, ",")#,
         #listgetat(line, 3, ",")#,
         '#REReplace(listgetat(line, 4, ","), "^""(.+)""$", "\1")#')
  Where #listgetat(line, 1, ",")# = #session.accountnbr# and
        #listgetat(line, 2, ",")# = 0

How are you inserting .. shouldn't this be an update?

update titles
set
col1 = val1,
...
where
....

try this isntead:

<cfif ListLen(line, ",") GT 2>
Update titles
set
    groupid = #listgetat(line, 2, ",")#,
    titleid =  #listgetat(line, 3, ",")#,
    titledescription =  '#REReplace(listgetat(line, 4, ","), "^""(.+)""$", "\1")#'
  Where #listgetat(line, 1, ",")# = #session.accountnbr# and
        #listgetat(line, 2, ",")# = 0
<cfelse>
  line did not have enough elements
</cfif>

CJ

Am I wrong or shouldn't it be an update instead of an insert.  esp with the where clause.

Unless you just remove the where clause and then just insert it.

then it would be:

<cfif ListLen(line, ",") GT 2>
INSERT INTO titles
            (Accountnbr, groupid, titleid, titledescription)
  VALUES
            (#session.accountnbr#,
         #listgetat(line, 2, ",")#,
         #listgetat(line, 3, ",")#,
         '#REReplace(listgetat(line, 4, ","), "^""(.+)""$", "\1")#')
<cfelse>
  line did not have enough elements
</cfif>

You can't INSERT...WHERE.
You can UPDATE...WHERE (as CJ says)
and SELECT...WHERE,
but INSERT...WHERE makes no sense.

Hello there !

How about doing your query this way :

<cftransaction>
<cfquery datasource="btadb">
delete from titles
where accountnbr = #session.accountnbr# and groupid = 0
</cfquery>
<cfloop index="line" list="#message#" delimiters="#chr(10)##chr(13)#">
<cfif listgetat(line, 1, ",") eq #session.accountnbr# and listgetat(line, 2, ",") eq 0>
  <cfquery datasource="btadb">
  INSERT INTO titles
            (Accountnbr, groupid, titleid, titledescription)
  VALUES
            (#session.accountnbr#,#listgetat(line, 2, ",")#,#listgetat(line, 3, ",")#,'#REReplace(listgetat(line, 4, ","), "^""(.+)""$", "\1")#')
  </cfquery>
</cfif>
</cfloop>
</cftransaction>

Goodluck!
eNTRANCE2002 :-)

Yes I agree with CJ & dc197 on that !

also i think one of ur lines may have extra single quotes ['] in it or something [i assume this, since u are reading from a file & inserting line by line]

use #PreserveSingleQuotes()# & also wrap ur values inside a CFQUERYPARAM - so it becomes much easier !

HTH

K'Rgds
Anand