Defining a table variable in datasource insert statement

See more:

SQL

Hi Everyone,

I have a form with defined insert, edit and update commands in a sql datasource that handles the record adds/changes. I have to modify it where I'd like to get the ID of the inserted record back into a table variable. When I add the table declaration and the Output option into the insert statement I get errors about needing to define the table variable.

My insert statement looks like this:

declare @tt1 table (id int)
insert into law (lawname, lawdescription, lawcountry,lawcode,lawstartdate, lawenddate, lawrev, lawrefurl)
output inserted.lawid into @tt1
values (@lawname, @lawdescription,@lawcountry,@lawcode,@lawstartdate,@lawenddate, @lawrev, @lawrefurl)

The entire sqldatasource definition in the aspx page look like this:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
        ConnectionString="<%$ ConnectionStrings:STRKnowledgeDBConnectionString %>"

        SelectCommand="select lawid, lawname, lawdescription, lawcountry, countryname, lawcode, lawcomment, lawstartdate, lawenddate, lawattachment, lawrev,lawrefurl from law, loc_country where  loc_country.countryid = law.lawcountry and lawid=@lawid"

        InsertCommand="insert into law (lawname, lawdescription, lawcountry,lawcode,lawstartdate, lawenddate, lawrev, lawrefurl)
output inserted.lawid into @tt1
values (@lawname, @lawdescription,@lawcountry,@lawcode,@lawstartdate,@lawenddate, @lawrev, @lawrefurl)"





        UpdateCommand="update law set lawname=@lawname, lawdescription=@lawdescription, lawcountry=@lawcountry, lawcode=@lawcode, lawcomment=@lawcomment, lawstartdate=@lawstartdate, lawenddate=@lawenddate, lawrev = @lawrev, lawrefurl=@lawrefurl where lawid=@lawid">
        <SelectParameters>
            <asp:QueryStringParameter Name="LawID" QueryStringField="LawID" />
        </SelectParameters>
        <UpdateParameters>
            <asp:Parameter Name="lawname" />
            <asp:Parameter Name="lawdescription" />
            <asp:Parameter Name="lawcountry" />
            <asp:Parameter Name="lawcode" />
            <asp:Parameter Name="lawcomment" />
            <asp:Parameter Name="lawstartdate" />
            <asp:Parameter Name="lawenddate" />
            <asp:Parameter Name="lawrev" />
            <asp:Parameter Name="lawrefurl" />
            <asp:Parameter Name="lawid" />
        </UpdateParameters>
        <InsertParameters>
            <asp:Parameter Name="tt1" />
            <asp:Parameter Name="lawname" />
            <asp:Parameter Name="lawdescription" />
            <asp:Parameter Name="lawcountry" />
            <asp:Parameter Name="lawcode" />
            <asp:Parameter Name="lawstartdate" />
            <asp:Parameter Name="lawenddate" />
            <asp:Parameter Name="lawrev" />
            <asp:Parameter Name="lawrefurl" />
        </InsertParameters>
    </asp:SqlDataSource>

Am I putting the table definition in the wrong place or is it the wrong format? I'm new to SQL so any help would be appreciated.

Thanks
John.