VB.NET Retrieving Identity form MSSQL2000 without using stored procedures

Discussion in 'ASP .Net' started by Taras, Sep 17, 2003.

  1. Taras

    Taras Guest

    Hello!
    I have a problem. I'm using a dataset in VB.NET with multiple tables with
    relations between them. I would like dataset to update to all related tables
    with right identity, when certain record is added.

    The problem is that I don't know how to do it, when I'm not using stored
    procedures. I found an article in which OnRowUpdated event of the
    dataadapter is used. On this event ExecuteScalar method of the oledbcommand
    object is called to retrieve "@@Identity"

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/htm
    l/cpconretrievingidentityorautonumbervalues.asp

    But this example was for Access 2000 database. I tried to translate it for
    MSSQL 2000 but ExecuteScalar always returns
    null.

    In the same article there's an example which calls a stored procedure in a
    insert command. In that case everything is working perfectly, all child
    tables in dataset are automatically changed.

    Does anybody have any idea how to solve this?

    Regards, Taras
    Taras, Sep 17, 2003
    #1
    1. Advertising

  2. Taras

    alien2_51 Guest

    look into 'SetParentRow'
    http://www.codeproject.com/cs/database/relationaladonet.asp


    "Taras" <> wrote in message
    news:...
    > Hello!
    > I have a problem. I'm using a dataset in VB.NET with multiple tables with
    > relations between them. I would like dataset to update to all related

    tables
    > with right identity, when certain record is added.
    >
    > The problem is that I don't know how to do it, when I'm not using stored
    > procedures. I found an article in which OnRowUpdated event of the
    > dataadapter is used. On this event ExecuteScalar method of the

    oledbcommand
    > object is called to retrieve "@@Identity"
    >
    >

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/htm
    > l/cpconretrievingidentityorautonumbervalues.asp
    >
    > But this example was for Access 2000 database. I tried to translate it for
    > MSSQL 2000 but ExecuteScalar always returns
    > null.
    >
    > In the same article there's an example which calls a stored procedure in a
    > insert command. In that case everything is working perfectly, all child
    > tables in dataset are automatically changed.
    >
    > Does anybody have any idea how to solve this?
    >
    > Regards, Taras
    >
    >
    >
    >
    >
    alien2_51, Sep 17, 2003
    #2
    1. Advertising

  3. Taras

    Rick Spiewak Guest

    Here is how I do this when adding an order to my order table:

    insert orders (order_date, first_name,last_name,title,organization,
    address1,address2,city,state,zip,country,phone,email,amount,
    bill_to_first_name,bill_to_last_name,bill_to_address1,
    bill_to_address2,bill_to_city,bill_to_state,bill_to_zip,bill_to_country)
    values ('9/13/2003 8:10:13 PM','Elmer','Fudd','Cartoon Character',
    'Looney Tunes','1 Vine St.','2nd Floor','Hollywood Hills','CA','77478',
    'USA','508-405-0727','',1.00,'Elmer','Fudd',
    '1 Vine St.','2nd Floor','Hollywood Hills','CA','77478','USA')
    select @@IDENTITY

    Note that this is all one "executescalar" query, returning an integer. In
    practice, I build it with stringbuilder. I also use the Microsoft Data
    Access Application Block, which I highly recommend, to execute it.

    "Taras" <> wrote in message
    news:...
    > Hello!
    > I have a problem. I'm using a dataset in VB.NET with multiple tables with
    > relations between them. I would like dataset to update to all related

    tables
    > with right identity, when certain record is added.
    >
    > The problem is that I don't know how to do it, when I'm not using stored
    > procedures. I found an article in which OnRowUpdated event of the
    > dataadapter is used. On this event ExecuteScalar method of the

    oledbcommand
    > object is called to retrieve "@@Identity"
    >
    >

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/htm
    > l/cpconretrievingidentityorautonumbervalues.asp
    >
    > But this example was for Access 2000 database. I tried to translate it for
    > MSSQL 2000 but ExecuteScalar always returns
    > null.
    >
    > In the same article there's an example which calls a stored procedure in a
    > insert command. In that case everything is working perfectly, all child
    > tables in dataset are automatically changed.
    >
    > Does anybody have any idea how to solve this?
    >
    > Regards, Taras
    >
    >
    >
    >
    >
    Rick Spiewak, Oct 5, 2003
    #3
    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. excelleinc.com
    Replies:
    1
    Views:
    1,401
    Hermit Dave
    Sep 10, 2004
  2. Hennie
    Replies:
    5
    Views:
    2,810
    Brock Allen
    Jun 4, 2005
  3. Randy
    Replies:
    0
    Views:
    520
    Randy
    Jun 9, 2006
  4. pln

    C# MSSQL2000 update

    pln, Jul 21, 2006, in forum: C Programming
    Replies:
    6
    Views:
    287
    J. J. Farrell
    Jul 21, 2006
  5. Paul
    Replies:
    2
    Views:
    407
Loading...

Share This Page