| Groups | Blog | Home |
sql server programming : SET NOCOUNT ON?
informational messages can cause problems retrieving actual data. SET NOCOUNT ON before the statement is the fix for that. Also as newscorrespondent wrote, use SCOPE_IDENTITY() instead of @@IDENTITY. If you need more help, post your DDL, DML, yadda yadda.
To add to what Mike C# said, I'm betting you'll find your expected result if
you use NextRecordset() (? -- been a while since I've done ADO work, but it's something like that). The row count (which is turned off by SET NOCOUNT ON) comes back as a result set in ADO. Luckily, that is fixed in ADO.NET. -- Adam Machanic Pro SQL Server 2005, available now http://www.apress.com/book/bookDisplay.html?bID=457 -- [quoted text, click to view] "Rob Meade" <ten.bewdoowsgnikNO-SPAM@edaem.bbor> wrote in message news:m_%kg.87813$wl.63758@text.news.blueyonder.co.uk... > Hi all, > > I'm trying to insert a record into a database via ASP, and then extract > the > id of the current row using @@IDENTITY... > > I've read the articles on ASPAQ and somewhere else regarding the use of > the > above, but I do actually want the ID of the row inserted using this > connection, not just the most recent record if that makes any sense... > > Now - what's frustrating me the most at the moment, is that when I execute > my stored procedure via my code ( the same code I use else where with no > problems ) - I do not get a value back for the @@IDENTITY in ASP (I do in > SQL if I run the SQL statement that I generated).. > > The only way I seem to be able to get a response is to add "SET NOCOUNT > ON; > " infront of my SQL statement - doing so gets me a value back - Hooray.... > > However, that cheer is short lived because for some bizarre reason - I now > get 4 rows inserted into the database each time i run it!?! > > There is NO loop anyway near my inserting code..and if I take the SET > NOCOUNT ON; out of the statement it does infact run correctly and insert > just the one row and I'd expect but - no returned value... > > Has anyone else come across this bizarre behaviour before? > > I use this same process else where and my technique (good or bad) is the > same, I dont understand why its going wrong?! > > Any help would be appreciated.... > > Regards > > Rob >
Hi all,
I'm trying to insert a record into a database via ASP, and then extract the id of the current row using @@IDENTITY... I've read the articles on ASPAQ and somewhere else regarding the use of the above, but I do actually want the ID of the row inserted using this connection, not just the most recent record if that makes any sense... Now - what's frustrating me the most at the moment, is that when I execute my stored procedure via my code ( the same code I use else where with no problems ) - I do not get a value back for the @@IDENTITY in ASP (I do in SQL if I run the SQL statement that I generated).. The only way I seem to be able to get a response is to add "SET NOCOUNT ON; " infront of my SQL statement - doing so gets me a value back - Hooray.... However, that cheer is short lived because for some bizarre reason - I now get 4 rows inserted into the database each time i run it!?! There is NO loop anyway near my inserting code..and if I take the SET NOCOUNT ON; out of the statement it does infact run correctly and insert just the one row and I'd expect but - no returned value... Has anyone else come across this bizarre behaviour before? I use this same process else where and my technique (good or bad) is the same, I dont understand why its going wrong?! Any help would be appreciated.... Regards Rob
Hi all,
I'd already put the SET NOCOUNT ON at the top of the stored procedure, and to ensure, I had placed in front of my SQL statment, separated by a sermi-colon. I ran a trace in SQL Profiler and the statement was being executed 4 times! I found that by not using a DSN-less connection in my ASP page, and instead using a DSN the problem went away - now - to me that just seems like crazy... Any thoughts? Regards Rob
Oh, and I had tried the SCOPE_IDENTITY() as well, from reading though I'm
not sure that that was what I wanted.. I wanted to get the "just inserted" row by my code at that time, not the most recently inserted id in the table...think there was another option I could have used as well... What was most strange about this was my method is being used else where in another part of my application - the only difference was the DSN-less/DSN connection - I still dont undestand why this would execute 4 times thought? Rob
Take a look in BOL at SCOPE_IDENTITY() and use that instead of @@IDENTITY
[quoted text, click to view]
> I wanted to get the "just inserted" row by my code at that time, not the > most recently inserted id in the table...think there was another option I > could have used as well... SCOPE_IDENTITY() will do what you want; it will return the last IDENTITY value inserted on the current database connection. The difference between @@IDENTITY and SCOPE_IDENTITY() is that SCOPE_IDENTITY() will return the last value within the current SCOPE and is therefore not affected by triggers. [quoted text, click to view] > What was most strange about this was my method is being used else where in > another part of my application - the only difference was the DSN-less/DSN > connection - I still dont undestand why this would execute 4 times > thought? If the entire procedure is being executed multiple times, then it is likely that you have an issue in your application code. It's interesting that the problem doesn't surface until you add SET NOCOUNT ON. Have you tried adding SET NOCOUNT ON at the *beginning* of the proc (immediately after 'AS')? -- Hope this helps. Dan Guzman SQL Server MVP [quoted text, click to view] "Rob Meade" <ten.bewdoowsgnikNO-SPAM@edaem.bbor> wrote in message news:Eu6lg.87861$wl.28676@text.news.blueyonder.co.uk... > Oh, and I had tried the SCOPE_IDENTITY() as well, from reading though I'm > not sure that that was what I wanted.. > > I wanted to get the "just inserted" row by my code at that time, not the > most recently inserted id in the table...think there was another option I > could have used as well... > > What was most strange about this was my method is being used else where in > another part of my application - the only difference was the DSN-less/DSN > connection - I still dont undestand why this would execute 4 times > thought? > > Rob >
[quoted text, click to view]
> I wanted to get the "just inserted" row by my code at that time, not the > most recently inserted id in the table...think there was another option I > could have used as well... > > What was most strange about this was my method is being used else where in > another part of my application - the only difference was the DSN-less/DSN > connection - I still dont undestand why this would execute 4 times > thought? Could you show your code???
Is the statement being "executed" 4 times, or is it merely showing up 4
times because you've selected several events that report the statement's progress? [quoted text, click to view] "Rob Meade" <ten.bewdoowsgnikNO-SPAM@edaem.bbor> wrote in message news:To6lg.87859$wl.59164@text.news.blueyonder.co.uk... > Hi all, > > I'd already put the SET NOCOUNT ON at the top of the stored procedure, and > to ensure, I had placed in front of my SQL statment, separated by a > sermi-colon. > > I ran a trace in SQL Profiler and the statement was being executed 4 > times! > > I found that by not using a DSN-less connection in my ASP page, and > instead using a DSN the problem went away - now - to me that just seems > like crazy... > > Any thoughts? > > Regards > > Rob >
[quoted text, click to view] "Rob Meade" <ten.bewdoowsgnikNO-SPAM@edaem.bbor> wrote in message news:Eu6lg.87861$wl.28676@text.news.blueyonder.co.uk... > Oh, and I had tried the SCOPE_IDENTITY() as well, from reading though I'm > not sure that that was what I wanted.. > > I wanted to get the "just inserted" row by my code at that time, not the > most recently inserted id in the table...think there was another option I > could have used as well... > > What was most strange about this was my method is being used else where in > another part of my application - the only difference was the DSN-less/DSN > connection - I still dont undestand why this would execute 4 times > thought? > > Rob If you want the actual "just inserted row", you can use a trigger that SELECTs the row from the INSERTED virtual table, or you can do a SELECT ... FROM mytable WHERE [Id] = SCOPE_IDENTITY(). If you want just the identity number inserted, SCOPE_IDENTITY() will do the trick.
[quoted text, click to view]
"Mike C#" <xxx@yyy.com> wrote in message news:tYdlg.347$PG4.175@fe12.lga... > Is the statement being "executed" 4 times, or is it merely showing up 4 > times because you've selected several events that report the statement's > progress? I seem to remember some ADO issue where a statement would be executed twice if you had the wrong settings turned on -- once so ADO could bind to the output columns, and then again to get the actual data... Unfortunately, I can't recall what the option was (and again, this would be only twice, not four times)... Working with ADO.NET is so much nicer :-) -- Adam Machanic Pro SQL Server 2005, available now http://www.apress.com/book/bookDisplay.html?bID=457 --
[quoted text, click to view] "Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message news:ePCzxGvkGHA.4212@TK2MSFTNGP03.phx.gbl... > "Mike C#" <xxx@yyy.com> wrote in message > news:tYdlg.347$PG4.175@fe12.lga... >> Is the statement being "executed" 4 times, or is it merely showing up 4 >> times because you've selected several events that report the statement's >> progress? > > I seem to remember some ADO issue where a statement would be executed > twice if you had the wrong settings turned on -- once so ADO could bind to > the output columns, and then again to get the actual data... > Unfortunately, I can't recall what the option was (and again, this would > be only twice, not four times)... Working with ADO.NET is so much nicer > :-) With ODBC, would that be the SQLPrepare function reporting back as a second execution? Been a while since I messed with the ODBC SDK, but hey... Who knows, might be that he just needs to update to a newer version of MDAC...
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |