Hi all, I'm new to ASP.net and and using Visual Studio 2008 and VB as my source code. I would like to query a database for a value (guaranteed to return 1 row 1 column (SELECT MAX(RECIPE_ID FROM TB_RECIPE) and then insert this value as part of another insert statement (INSERT INTO [TB_INGREDIENTS] ([RECIPE_ID], [OWNER_ID], [INGREDIENT_NAME], [QUANTITY], [UNIT_OF_MEASURE]) VALUES (@RecipeID, @UserID, @INGREDIENT_NAME, @QUANTITY, @UNIT_OF_MEASURE)) where the Recipe ID obtained in the first query is the parameter sent in the insert statement as @RecipeID. I'm using SQL Server 2005 Express connecting to an attached .mdf file. I think based on my research so far on this site that I need to use a scalar variable, but I'm too new to this to know how to accomplish this. Any help would be greatly appreciated....

 1. Why do you need to use max value ? Are you trying to simulate Identity field?

2. I suggest to simply put the logic into the stored procedure to both get the maximum value and insert as one operation. You can return the max value (inserted value) as the output parameter.

Naom, Thanks for the quick reply. 1. I'm not trying to simulate the identity field from that table. The query actually returns the max id for the logged in user so it's not the literal max id from the table. 2. I've seen this as an option in other posts too, but I'm not too familiar with how to accomplish this. Could you help me with a starting point; then I can tweak it from there. Thanks again!

 create procedure usp_InsertIntoMyTable(@Va1 as varchar(100), @Val2 as int, etc., OUT @Max_Recipe int) as

BEGIN

select @Max_Recipe = max(ID) from myTable where some condition

insert into myTable (...)

END

And then use SP instead of direct command.