Adding file information to SQL database on file upload

Hi there :)

I'm in the final stage of my asp.net project and one of the last things I need to do is add the following file information to my SQL server 2000 database when a file is uploaded:

First of all I have a resource table to which I need to add:
- filename
- file_path
- file_size
(the resource_id has a auto increment value)

so that should hopefully be straight forward when the file is uploaded. The next step is to reference the new resource_id in my module_resource table. My module resource table consists of:
- resource_id (foreign key)
- module_id (foreign key)

So, adding the module_id is easy enough as I can just get the value using Request.QueryString["module_id"]. The bit that I am unsure about is how to insert the new resource_id from the resource table into the module_resource table on file upload. How is this done? Using one table would solve the issue but I want one resource to be available to all modules - many to many relationship.

Any ideas?

Many thanks :)Since Resource_ID is an Identity column, you should be able to perform a SCOPE_IDENTITY() after the INSERT into the Resource table to pick up the value.

For example:

DECLARE @.resourceID bigint
INSERT INTO
 Resource
(
 filename,
 file_path,
 file_size
)
VALUES
(
 @.filename,
 @.file_path,
 @.file_size
)
SELECT @.resource_ID = SCOPE_IDENTITY()
INSERT INTO
 Module_Resource
(
 module_ID,
 resource_ID
)
VALUES
(
 @.module_ID,
 @.resource_ID
)

Terri