By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,647 Members | 1,124 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,647 IT Pros & Developers. It's quick & easy.

SET NOCOUNT ON?

P: n/a
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
Jun 17 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Jun 17 '06 #2

P: n/a
Rob Meade wrote:
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.

--
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"
Jun 18 '06 #3

P: n/a
Rob Meade wrote:
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.

--
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"
Jun 18 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.