Inserting a value from a joined Table

[Joe318]

Hi,

I'm joining 2 tables and using the formview. When I insert a record, I need to place the value of the max id + 1 of the second table in a field in the first table so that the tables are linked.
I'm not sure where to put the value. I try to put it in the insert command and I've tried the select command but that doesn't work.

Table 1 - Product: ID, ProductNum, Brand, Description
Table2 - Brand: ID, BrandName
(Brand from Table1 is linked to ID from Table 2)

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
DeleteCommand="DELETE FROM [Product] ..."
InsertCommand="INSERT INTO [Product] ([productNum], [Brand] VALUES (@productNum, @Brand)"
SelectCommand="SELECT Product.ID, Product.Brand, Product.Description, Brand.ID, Brand.BrandName From Product INNER JOIN Brand ON Product.Brand= Brand.ID"
UpdateCommand="UPDATE [Product] SET [productNum] = @productNum, [Brand] = @Brand ......"

I've Tried
InsertCommand="INSERT INTO [Product] ([productNum], [Brand] VALUES (@productNum, MAX(@Brand.ID) +1)"

or setting
BrandID =Max(@Brand)

Thanks