Friday, February 24, 2012

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

No comments:

Post a Comment