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]

Connection string issue??

I don't understand...

What's the difference between these two...

objConnection99 .Open "Provider=SQLOL EDB;Data Source=TITUS;Us er
ID=<user.;Passw ord=<password>; Initial Catalog=<db>"

objConnection99 .Open "DSN=<dsn>;uid= <user>;pwd=<pas sword>"

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;"...downsid e is having to setup the DSN on the sever...

What am I missing?

Regards

Rob

[Bob Barrows [MVP]]

Re: Connection string issue??

Rob Meade wrote:[color=blue]
> I don't understand...
>
> What's the difference between these two...
>
> objConnection99 .Open "Provider=SQLOL EDB;Data Source=TITUS;Us er
> ID=<user.;Passw ord=<password>; Initial Catalog=<db>"
>[/color]

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.
[color=blue]
> objConnection99 .Open "DSN=<dsn>;uid= <user>;pwd=<pas sword>"[/color]

This uses the default OLE DB provider for ODBC databases (MSDASQL), so
communications with the database are using an extra layer of software (ODBC)
[color=blue]
>
> 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;"...downsid e is having to setup the DSN on the
> sever...
> What am I missing?
>[/color]
Hard to say without seeing the code.

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

[Bob Barrows [MVP]]

Re: SET NOCOUNT ON?

Rob Meade wrote:[color=blue]
> 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....[/color]

How is the procedure returning the value?
[color=blue]
> 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....[/color]

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.

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