SqlDataSource - Insert to 2 tables problem.

Discussion in 'ASP .Net' started by ssims, Dec 1, 2006.

  1. ssims

    ssims Guest

    I'm working with ASP.Net on IIS 5.1 and MSSQL 2000 and have a rather
    frustrating problem. I'm trying to insert a record to a table, get the
    identity value, and insert two additional records to a second table.
    My insert command looks like this:

    INSERT INTO TableA VALUES(PK_NameID, CQ1, CQ2, CQ3, CQ4);
    DECLARE @ID int;
    SET @ID = SCOPE_IDENTITY();
    INSERT INTO TableB VALUES(@ID, IQ1, IQ2, IQ3, IQ4);
    INSERT INTO TableB VALUES(@ID, IQ1, IQ2, IQ3, IQ4);

    The insert command is all mashed together in the InsertCommand
    parameter of a SqlDataSource. When I run the query by hand in sqlcmd,
    everything works beautifully, but when I try to run it on the page,
    only the first insert command is processed. I can't find any
    information in any of the logs or anywhere on Google to suggest what is
    going wrong. Any help is greatly appreciated.

    Thanks,

    -Sean
    ssims, Dec 1, 2006
    #1
    1. Advertising

  2. ssims

    bpd Guest

    Try putting the command in a stored procedure and use the stored
    procedure in your SQLDataSource.

    ssims wrote:
    > I'm working with ASP.Net on IIS 5.1 and MSSQL 2000 and have a rather
    > frustrating problem. I'm trying to insert a record to a table, get the
    > identity value, and insert two additional records to a second table.
    > My insert command looks like this:
    >
    > INSERT INTO TableA VALUES(PK_NameID, CQ1, CQ2, CQ3, CQ4);
    > DECLARE @ID int;
    > SET @ID = SCOPE_IDENTITY();
    > INSERT INTO TableB VALUES(@ID, IQ1, IQ2, IQ3, IQ4);
    > INSERT INTO TableB VALUES(@ID, IQ1, IQ2, IQ3, IQ4);
    >
    > The insert command is all mashed together in the InsertCommand
    > parameter of a SqlDataSource. When I run the query by hand in sqlcmd,
    > everything works beautifully, but when I try to run it on the page,
    > only the first insert command is processed. I can't find any
    > information in any of the logs or anywhere on Google to suggest what is
    > going wrong. Any help is greatly appreciated.
    >
    > Thanks,
    >
    > -Sean
    bpd, Dec 1, 2006
    #2
    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. Hrvoje Vrbanc

    SqlDataSource INSERT query and TextBox text

    Hrvoje Vrbanc, Dec 8, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    4,373
    Hrvoje Vrbanc
    Dec 8, 2005
  2. =?Utf-8?B?RGFiYmxlcg==?=

    insert row using Gridview and SqlDataSource?

    =?Utf-8?B?RGFiYmxlcg==?=, Mar 19, 2006, in forum: ASP .Net
    Replies:
    2
    Views:
    10,637
    =?Utf-8?B?RGFiYmxlcg==?=
    Mar 20, 2006
  3. Replies:
    7
    Views:
    6,457
    Vlad Iliescu
    Apr 20, 2006
  4. mosscliffe
    Replies:
    3
    Views:
    9,427
    mosscliffe
    Apr 28, 2006
  5. Joe Kovac

    Multiple Tables as SqlDataSource

    Joe Kovac, May 7, 2007, in forum: ASP .Net
    Replies:
    2
    Views:
    1,060
    Joe Kovac
    May 7, 2007
Loading...

Share This Page