SOT: keyword 'Where' error
Is there a problem with this Insert? I get an Incorrect syntax near the keyword 'Where' error. I added single quotes around #cnumber# and still get the error. CFQUERY name=additem datasource=purchaseorders INSERT INTO itemsordered (cnumber, line, qty, item, priceeach, totalprice ) VALUES (#cnumber#, '#line#', '#qty#', cfqueryparam value=#Trim(item)# cfsqltype=CF_SQL_VARCHAR, '#trim(item)#', '#trim(totalprice)#' ) Where cnumber = #cnumber# /cfquery Robert O. HWW [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SOT: keyword 'Where' error
Yes, you cannot specify a where clause in an insert. If you are updating a row then use update not insert. Doug Robert Orlini wrote: Is there a problem with this Insert? I get an Incorrect syntax near the keyword 'Where' error. I added single quotes around #cnumber# and still get the error. CFQUERY name=additem datasource=purchaseorders INSERT INTO itemsordered (cnumber, line, qty, item, priceeach, totalprice ) VALUES (#cnumber#, '#line#', '#qty#', cfqueryparam value=#Trim(item)# cfsqltype=CF_SQL_VARCHAR, '#trim(item)#', '#trim(totalprice)#' ) Where cnumber = #cnumber# /cfquery Robert O. HWW [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: keyword 'Where' error
Is there a problem with this Insert? I get an Incorrect syntax near the keyword 'Where' error. You can't have a where clause on an insert statement. Nick [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: keyword 'Where' error
There is no WHERE clause here, you are adding a new record -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: 09 July 2004 15:18 To: CF-Talk Subject: SOT: keyword 'Where' error Is there a problem with this Insert? I get an Incorrect syntax near the keyword 'Where' error. I added single quotes around #cnumber# and still get the error. CFQUERY name=additem datasource=purchaseorders INSERT INTO itemsordered (cnumber, line, qty, item, priceeach, totalprice ) VALUES (#cnumber#, '#line#', '#qty#', cfqueryparam value=#Trim(item)# cfsqltype=CF_SQL_VARCHAR, '#trim(item)#', '#trim(totalprice)#' ) Where cnumber = #cnumber# /cfquery Robert O. HWW [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: keyword 'Where' error
Robert, Firstyl, you can't insert using a where clause are you wanting to do an UPDATE??? Secondly and, you need to use CFQUERYPARAM for all of your values not just the value for 'item'. Paul [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SOT: keyword 'Where' error
Thanks all. Too many cut-and-pastes and I didn't pay attention. Also, still am a newbie on some of this. Robert O. -Original Message- From: Doug James [mailto:[EMAIL PROTECTED] Sent: Friday, July 09, 2004 9:26 AM To: CF-Talk Subject: Re: SOT: keyword 'Where' error Yes, you cannot specify a where clause in an insert. If you are updating a row then use update not insert. Doug Robert Orlini wrote: Is there a problem with this Insert? I get an Incorrect syntax near the keyword 'Where' error. I added single quotes around #cnumber# and still get the error. CFQUERY name=additem datasource=purchaseorders INSERT INTO itemsordered (cnumber, line, qty, item, priceeach, totalprice ) VALUES (#cnumber#, '#line#', '#qty#', cfqueryparam value=#Trim(item)# cfsqltype=CF_SQL_VARCHAR, '#trim(item)#', '#trim(totalprice)#' ) Where cnumber = #cnumber# /cfquery Robert O. HWW _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: keyword 'Where' error
Yes, you cannot specify a where clause in an insert. If you are updating a row then use update not insert. You can't have a where clause on an insert statement. Firstyl, you can't insert using a where clause are you wanting to do an UPDATE??? :) It's late and I feel mischievous, so I will respond to these assertions. You can do inserts using a where if you select values from a table, as follows (MS SQL Server-specific, possibly other DBs also): INSERT INTO TABLE (FIELD LIST) SELECT FIELD LIST FOR VALUES FROM 2.TABLE WHERE WHERE CLAUSE Clearly the syntax of the original post is incorrectly using the WHERE clause, I just wanted to point out that it CAN be used in the right circumstances. In the hope it may help someone down the track! The syntax above will potentially insert multiple records based on the SELECT. HTH Aaron Christiansen and the Prisoner of Micro$oft (Can you tell I've just been to the movies?) [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: keyword 'Where' error
Aaron, INSERT INTO TABLE (FIELD LIST) SELECT FIELD LIST FOR VALUES FROM 2.TABLE WHERE WHERE CLAUSE The WHERE clause of your example is tied to the sub-query that does the SELECT. It is not part of the INSERT statement in your example. If you read the MS SQL Books online and pay particular attention to the BNF specification of the INSERT INTO query you will see that this is correct. Paul [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
