I'm not certain but I think you have to set nocount off at the end of
your cfquery tag. The NOCOUNT part turns off messages from the
database back to the CFQUERY which would otherwise kill the query.
So if you turn it on again midway through the query that would break
it. (I think - I'm not near a SQLServer right now to test it)
Also, you might want to look into using IDENT_CURRENT instead of
@@IDENTITY. The difference is that @@IDENTITY gives you the last ID
generated anywhere in the database, and IDENT_CURRENT(tablename) gives
you the last id created on the table specified. Only a minor point,
but if someone's inserting something on another table somewhere you
could end up with their ID. (see books online for more details).
So your query would be like this:
<cfquery name="qInsertSandpit"
datasource="DB_ODBC">
SET NOCOUNT ON
INSERT INTO Sandpits
(Name)
VALUES
('My Sandpit Name')
SELECT IDENT_CURRENT('Sandpits') AS 'Sandpit_ID'
SET NOCOUNT OFF
</cfquery>
Cheers
Mike Kear
Windsor, NSW, Australia
AFP Webworks
http://afpwebworks.com
.com,.net,.org domains from AUD$20/Year
On Thu, 9 Dec 2004 12:33:19 +1100, Ryan Sabir <[EMAIL PROTECTED]> wrote:
> Heya,
>
> Is that the code as you've pasted it? It looks like there are 'smart'
> quotes around the string. Can you replace them with regular straight
> quotes?
>
> Do you need quotes around Sandpit_ID anyway? I wouldn't have thought
> so...
>
> bye!
>
> Thursday, December 9, 2004, 12:24:50 PM, you wrote:
>
> MB> Ok fellow CF developers, using CFMX 6.1 riddle me this�
>
> MB> Why does the SQLServer JDBC Driver spit the dummy with the following
> MB> query:
>
> MB> <cfquery name="qInsertSandpit"
> MB> datasource="DB_Native">
> MB> SET NOCOUNT ON
> MB> INSERT INTO Sandpits
> MB> (Name)
> MB> VALUES
> MB> ('My Sandpit Name')
> MB> SET NOCOUNT OFF
> MB> SELECT @@IDENTITY AS 'Sandpit_ID'
> MB> </cfquery>
>
> MB> Yet if you use an ODBC Socket connection, i.e:
>
> MB> <cfquery name="qInsertSandpit"
> MB> datasource="DB_ODBC">
> MB> SET NOCOUNT ON
> MB> INSERT INTO Sandpits
> MB> (Name)
> MB> VALUES
> MB> ('My Sandpit Name')
> MB> SET NOCOUNT OFF
> MB> SELECT @@IDENTITY AS 'Sandpit_ID'
> MB> </cfquery>
>
> MB> It works fine!
>
> MB> I am using SQL Server 200 and CF 6,1,0,83762. The error I get when using
> MB> the native db connection is:
>
> MB> Error Executing Database Query.
> MB> [Macromedia][SQLServer JDBC Driver][SQLServer]Line 7: Incorrect syntax
> MB> near '''.
>
> MB> Any pointers greatly appreciated,
> MB> Malcolm
>
> MB> ---
> MB> You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
> MB> To unsubscribe send a blank email to [EMAIL PROTECTED]
> MB> Aussie Macromedia Developers: http://lists.daemon.com.au/
>
> -----------------------
> Ryan Sabir
> Newgency Pty Ltd
> 2a Broughton St
> Paddington 2021
> Sydney, Australia
> Ph (02) 9331 2133
> Fax (02) 9331 5199
> Mobile: 0411 512 454
> http://www.newgency.com/index.cfm?referer=rysig
>
>
>
>
> ---
> You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
> To unsubscribe send a blank email to [EMAIL PROTECTED]
> Aussie Macromedia Developers: http://lists.daemon.com.au/
>
--
---
You are currently subscribed to cfaussie as: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]
Aussie Macromedia Developers: http://lists.daemon.com.au/
