current updated value in table

hello all..

i want to update my table information with the value which is currently inserted/updated in another table dynamically..how to get the value of a currently inserted single cell in that table..?

table1 contains Refno,....Refno is primarykey,identity

table 2 contains uploadID,...uploadID is primary key,identity

table3 contains RefNo,UploadID(both r foriegn keys corresponding to table1 & table2)

how to fill table3 with values(not manually) when i am inserting records into table1 n table2 ..( refno and uploadId are identity columns )..Any Idea..?

thanks in Advance

Anne

hello anne..,

are u use sqldatasource to insert table1 and 2? if yes, in sqldatasource ,u can add query insert like this:

insert into [table3] ([refno],[uploadid]) values (@.refno,@.uploadid)

ok.. let's try it..

:)

|||

Hello hardy,

tranks for ur responce..i think i didn`t explained my requirement well...Actually i am doing some workflow application...in that app i hav one report Incident/Problem page...users has to enter the details regarding their incidents/problems....after submit click i hav to store the form information in Table....The form contains user,date occured,Application,Module,Description and 3 file uploads.....so now to store form info in database i created two tables...Submit (Refno,date,Application,Module,Description), Upload (UploadID,MIMEType,ImageData)......

In the above two tables RefNo ,UploadID are primary keys and identity columns....so no need to insert values in thses two columns explictly as these are identity columns..I used sqldatasource to insert the remaining info into the that tables...

while retriving i need information from the two tables..so i need one more table to relate these two tables...then i created one more table UploadRelate(Refno,uploadID)...both these columns are foriegn keys corresponding to two tables simultaneously....

my plan is to insert values into third table whenever values r inserted into other two tables...so that while retriving i can get the correct uploaded files for the corresponding user.....mySubmit, ,Upload,UploadRelate tables has to look like this after inserting values........

RefNo : user : date : application : Module: Description UploadID:MIMEType:ImageData RefNo : UploadID

1 a 1/1/07 sas sdas dsdd 1 jpg Binary 1 : 1

2 b -- -- -- -- 2 bmp -- 1 : 2

3 c -- -- -- -- 3 txt -- 1 : 3

4 d -- -- -- -- 4 jpg -- 2 : 4

5 doc -- 2 : 5

......user may upload 1 file or 2 files or 3 files..So now the problem is to insert the values in to 3rd table....whenever the other two tables r inserted with values my 3rd one has to be inserted...Is it the correct way to store in 3 tables like this or can i achive my requirement in any other way..?if my oproach is ok...then how to get the value of currently inserted column value from table...?...

insert into [table3] ([refno],[uploadid]) values (@.refno,@.uploadid) can be used ..but the thing is how to get the values of @.refno,@.uploadid...from the other two tables?

Thanks in Advance

Anne

|||

hello...

i am not undestand about ur third table like:

refno : uploadid

1 : 1

1 : 2

1 : 3

2 : 4

2 : 5

why the 1 relation with 1,1 with 2, 1 with 3..

u can not do like that..

emmm... i know..

u must use normalize if u want create table... like this:

ur 3rd table can be delete, so u have 2 table,but add uploadid in table one like this:

mysubmit table:

RefNo uploadid user date application Module Description

1 1 a 1/1/07 sas sdas dsdd

1 2 a 1/1/07 sas sdas dsdd

upload table:

UploadID MIMEType imageData

1 jpg Binary

2 bmp --

so u don't need create 3rd table, but u can call that data from two table...

ok..., let's try it...

the first important thing if u want to make table, u must understand about normalize..

|||

You can. However since there is a one-to-many relationship here instead of a many-to-many relationship, you can place the RefNo directly in the Upload Table (Since each upload must have one and only one RefNo).

That should greatly simplify your application logic.

|||

Thank u for the replies Hardy and Motley...

|||

Hello..

i am getting some problem regarding the above requirement...i am able to enter the data in my first table ,but when i am trying to enter data in second (upload) table i am not able to get the currently inserted refno from first table..my storedprocedure for second table is

ALTER PROCEDUREdbo.UploadInsert

(

@.TitleVarchar(50),

@.MMIETypevarchar(50),

@.ImageDataimage

)

AS

BEGIN

set nocount on

declare@.Ref_Noint

select@.Ref_No=@.@.IDENTITYfromdbo.Submit

INSERT INTO[upload]

([Title],[MMIEType], [ImageData],Ref_No)

VALUES(@.Title,@.MMIEType, @.ImageData,@.Ref_No)

end

RETURN

--Ref_No value is becoming null while inserting into upload table..is it the correct way to get the identity value from submit table ...? in submit table records r entering corrrectly...

Pls i need help..

anne

|||

yes i got the solution...i am doing mistake in my submit storedprocedure...without returning identity value i am retuning 0..so i am not able to get the refno into second table...

AnyHow Thanks to all

anne

sql