SqlDataSource - Insert to 2 tables problem.

[ssims]

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]

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