SET NOCOUNT ON?

[Rob Meade]

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

 

[Rob Meade]

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

 

[Bob Barrows [MVP]]

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>"

This uses the native OLE DB provider for SQL Server, and thus communicates
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)

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?

Hard to say without seeing the code.

 

[Bob Barrows [MVP]]

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....

How is the procedure returning the value?

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....

Show us how to reproduce this behavior. Give us a CREATE TABLE statement and
a CREATE PROCEDURE statement, and the vbscript code you use to run the
procedure.