How to insert new record into intermediate table ?

I'm  using vb to develop my application. I have 3 tables. Table 1 is Media,
Table 2 is Software, Table 3 (intermediate table) is Software_Media.


"> 

I am wondering how I can make new record in the Software table but at the same
time insert that new SoftwareId ( which is being created) to the
Software_Media,. As you can see, the Software_Media table contains SoftwareId
and MediaId.

 Please, show me some code example or tutorial link if anyone has similar
problem.
 

 Any comment will be very appreciated.

Hi,

Just use "Tigger" when u insert a new record into the software table

thirumaran007:

Hi,

Just use "Tigger" when u insert a new record into the software table 

 

 

Thanks a lot. Since I'm very new to asp.net and db programming. I'll study about
the trigger.

Hi, yes Triggers are very practical and efficient to help you with this, but
just to give you another approach you can create a procedure to first insert a
new record on your Software table, then retrieve the value of the softwareid
column that was recently added, and then add it to the record of the Media
Software table, something like this:

Create procedure performinsertion

@name varchar(50),

@inputdate datetime,

@loandate datetime,

@duedate datetime,

@mediaid int,

@description varchar(100)

as 

'Lets suppose that the softwareid is not an identity field so you need to create
the idvalue in order to insert it but the media table has an identity column so
i dont need to specify it on the moment of the insertion of the record, also i
wont use all the fields of your tables, just some of them.

Declare @id int

set @id=0

'Set the value of the new id retrieving the amount of existing records and
adding 1 to it so we can generate the number of the new record.

Select @id=count(*) from Software

set @id=@id+1

Insert into Software(softwareid,name,inputdate,loandate,duedate)

values(@id,@name,@inputdate,@loandate,@duedate)

'add the new id created for the new record on the previous table(@id), to link
it with the type of media it belongs too, and some other information like
description for it, i am supposing that the Media_Software table has its column
id defined as an identity column so it will autoincrement and you dont need to
refer it on the insertion

Insert into Media_Software(mediaid,softwareid,description)

values(@mediaid,@id,@description)

Thats pretty much it, Triggers are more practical but i am just showing a
different way, in the case in which the id is not necessarily a consecutive
numerical value(for example a varchar type) you can also retrieve the value of
the column save it, and add it afterwards.

I hope this helped you.

you said "first insert a new record on your Software table, then retrieve
the value of the softwareid column that was recently added,  ". My
applications is for multi users. Getting the recently added softwareid can work
? what if many users add the many new softwares to Software table at the same
time. This where I cant move on. 

In the page of adding a new software, I have been creating a dropdown list to
store the Media , so users can choose it from there. When they click submit
button, I want  MediaId (from the dropdwon list) and SoftwareId (being created
at the moment) to be inserted into Software_Media.
 

Getting the recently added softwareid wont be practical (maybe not work) since
the application for multi-users. 

Sorry that I forgot to tell that this web-based application is for multi-users.
Please, any suggestion is very helpful.