I'm still getting this error:
Error Executing Database Query.You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version
for the right syntax to use near 'values ('94219_0.JPG', '94219')' at line 10
>From this code:
insert
into hmls_photos
(photo_filename, photo_mls_number)
select '#photolist.name#', '#listfirst(photolist.name, '_')#'
from dual
where
not exists (select photo_filename
from hmls_photos
where photo_filename = '#photolist.name#')
values (<cfqueryparam cfsqltype="cf_sql_varchar" value="#photolist.name#">,
<cfqueryparam cfsqltype="cf_sql_varchar"
value="#listfirst(photolist.name, '_')#">)
Am I still missing something?
> -----Original Message-----
> From: Aaron Rouse [mailto:[EMAIL PROTECTED]
> Sent: Friday, May 16, 2008 12:43 PM
> To: CF-Talk
> Subject: Re: What's wrong with this query?
>
> You want to insert values into two columns, photo_filename and
> photo_mls_number, but you are only selecting out one value and on top of
> that it is from dual and that in itself will thrown an error, just try
> running:
>
> select photo_filename from dual
>
> and you will see what I mean.
>
> You need to be selecting out from dual the two values you want to insert in,
> so:
>
> select <cfqueryparam value="#photolist.name#" cfsqltype="cf_sql_varchar" />,
> <cfqueryparam value="#ListFist(photolist.name, '_')#"
> cfsqltype="cf_sql_varchar" /> from dual
>
> Now you want to select those out when they do not exist so you need to add
> the WHERE NOT EXISTS to that, something like:
>
> select <cfqueryparam value="#photolist.name#" cfsqltype="cf_sql_varchar" />,
> <cfqueryparam value="#ListFist(photolist.name, '_')#"
> cfsqltype="cf_sql_varchar" /> from dual
> WHERE NOT EXISTS
> (SELECT photo_filename FROM HMLS_PHOTOS WHERE PHOTO_FILENAME = <cfqueryparam
> value="#photolist.name#" cfsqltype="cf_sql_varchar" />)
>
> Now you want to insert this into your table so something like:
>
> INSERT INTO HMLS_PHOTOS
> (photo_filename, photo_mls_number)
> select <cfqueryparam value="#photolist.name#" cfsqltype="cf_sql_varchar" />,
> <cfqueryparam value="#ListFist(photolist.name, '_')#"
> cfsqltype="cf_sql_varchar" /> from dual
> WHERE NOT EXISTS
> (SELECT photo_filename FROM HMLS_PHOTOS WHERE PHOTO_FILENAME = <cfqueryparam
> value="#photolist.name#" cfsqltype="cf_sql_varchar" />)
>
> That was all written without testing so might have typos but it is the exact
> idea from those links I posted, I just applied their answers to your
> fields. I'd hope that would work, assuming no typos on my part.
>
>
> On Fri, May 16, 2008 at 11:28 AM, Rick Faircloth <[EMAIL PROTECTED]>
> wrote:
>
> > This seems to be appropriate for my case, but still doesn't work.
> >
> > insert
> > into hmls_photos
> > (photo_filename, photo_mls_number)
> > select photo_filename
> > from dual
> > where
> > not exists (select photo_filename
> > from hmls_photos
> > where photo_filename = '#photolist.name#')
> > values (<cfqueryparam cfsqltype="cf_sql_varchar" value="#photolist.name#
> > ">,
> > <cfqueryparam cfsqltype="cf_sql_varchar" value="#listfirst(
> > photolist.name, '_')#">)
> >
> > However, I am getting a different error:
> >
> > Error Executing Database Query.You have an error in your SQL syntax;
> > check the manual that corresponds to your MySQL server version
> > for the right syntax to use near 'values ('94219_0.JPG', '94219')' at line
> > 10
> >
> > Does my syntax match your referenced code? And the referenced code doesn't
> > include a reference to the values to be inserted, so maybe the "values"
> > clause
> > is in the wrong position?
> >
> >
> >
> > > -----Original Message-----
> > > From: Aaron Rouse [mailto:[EMAIL PROTECTED]
> > > Sent: Friday, May 16, 2008 12:02 PM
> > > To: CF-Talk
> > > Subject: Re: What's wrong with this query?
> > >
> > > Your attempt though does not match the syntax used in the link I
> > provided.
> > > It is failing because of the SQL after your WHERE EXISTS. Also your
> > SELECT
> > > should be pulling out the two values you want to insert into the table,
> > just
> > > like how they did in the example on that link.
> > >
> >
> >
> >
> >
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305483
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
