«

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

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

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"