SET NOCOUNT ON?

Discussion in 'ASP General' started by Rob Meade, Jun 17, 2006.

  1. Rob Meade

    Rob Meade Guest

    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, Jun 17, 2006
    #1
    1. Advertising

  2. Rob Meade

    Rob Meade Guest

    Connection string issue??

    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, Jun 18, 2006
    #2
    1. Advertising

  3. Re: Connection string issue??

    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"
     
    Bob Barrows [MVP], Jun 18, 2006
    #3
  4. 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"
     
    Bob Barrows [MVP], Jun 18, 2006
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Kikoz

    Re: SET NOCOUNT ON OPTION

    Kikoz, Oct 20, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    428
  2. Cirrosi

    Re: SET NOCOUNT ON OPTION

    Cirrosi, Oct 21, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    558
    Cirrosi
    Oct 21, 2004
  3. Alan Silver
    Replies:
    0
    Views:
    527
    Alan Silver
    Dec 21, 2005
  4. Manav
    Replies:
    0
    Views:
    784
    Manav
    Oct 15, 2003
  5. Harald Kirsch
    Replies:
    4
    Views:
    2,839
    Harald Kirsch
    Aug 31, 2004
Loading...

Share This Page