|
it
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SET NOCOUNT ON?
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 I don't understand...
What's the difference between these two... objConnection99.Open "Provider=SQLOLEDB;Data Source=TITUS;User ID=<user.;Password=<password>;Initial Catalog=<db>" objConnection99.Open "DSN=<dsn>;uid=<user>;pwd=<password>" When I use the top one - I get the whole load of pain as described in my first post.. When I use the second one I have no problems and dont need to specify "SET NOCOUNT ON;"...downside is having to setup the DSN on the sever... What am I missing? Regards Rob Rob Meade wrote:
> I don't understand... This uses the native OLE DB provider for SQL Server, and thus communicates > > What's the difference between these two... > > objConnection99.Open "Provider=SQLOLEDB;Data Source=TITUS;User > ID=<user.;Password=<password>;Initial Catalog=<db>" > directly with the database rather than going through an extra layer of code. > objConnection99.Open "DSN=<dsn>;uid=<user>;pwd=<password>" This uses the default OLE DB provider for ODBC databases (MSDASQL), so communications with the database are using an extra layer of software (ODBC) > Hard to say without seeing the code.> When I use the top one - I get the whole load of pain as described in > my first post.. > > When I use the second one I have no problems and dont need to specify > "SET NOCOUNT ON;"...downside is having to setup the DSN on the > sever... > What am I missing? > -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" Rob Meade wrote:
Show quote > Hi all, How is the procedure returning the value?> > 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 - Show us how to reproduce this behavior. Give us a CREATE TABLE statement and > 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.... a CREATE PROCEDURE statement, and the vbscript code you use to run the procedure. -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM"  |
|||||||||||||||||||||||
Other interesting topics