Yes you are, you added code to what I put in my last email. Why do you have
the VALUES part on there? The SELECT from dual is handling the VALUES that
you need. What I put in my last email should be all you need with the
exception of needing the opening and closing CFQUERY tags and again I may
have a typo or two in mine but it should be really close to what you need.
On Fri, May 16, 2008 at 12:14 PM, Rick Faircloth <[EMAIL PROTECTED]>
wrote:
> 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:305485
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
