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

 

[jbenson001]

This is SQL related code and should be done there. You should use stored procedures for all of your data access. The datasource controls are aweful. You should have primary keys on your tables, preferably with identity columns and forign keys to relate them.
So, in your stored procedure, you should insert into your table, get the identity, then use that as your key value your your other table.