January 17, 2006 at 7:23 am
I have an ASP page that pulls data from a database using the following query:
SELECT SQ.*" &_
", ST.COcontactNameLast AS SendTo" &_
", BT.COcontactNameLast AS BillTo" &_
", R.COcontactNameLast AS Requestor" &_
" FROM workrequest SQ" &_
" LEFT JOIN clientoffice ST ON SQ.WRsendToId=ST.COid" &_
" LEFT JOIN clientoffice BT ON SQ.WRbillToId=BT.COid" &_
" LEFT JOIN clientoffice R ON SQ.WRrequestorId=R.COid" &_
" WHERE SQ.WRid= & wrid
The ASP script then populates a form with the data pulled.
My question is, once all this data is in the form, and the user can edit it, what would the update query look like?
I tried using 2 queries, one for the workrequest table, and the other for the clientoffice table, but that did not work.
Is there a way to update both tables with one query?
Thanks!
January 17, 2006 at 8:12 am
I suggest that you should add the COid of all instances of ClientOffice in the select statement and keep in the page (May be hidden). Then you can update each "table" based on the COid.
Cheers,
Prithiviraj Kulasingham
http://preethiviraj.blogspot.com/
January 17, 2006 at 8:13 am
my advice from recent experience with asp(.net) is to use Stored procedures to retreive data and update data
your stored procedure to update the tables could easily perform multiple update statments in a transaction safe way.
you code would also be a lot more readable and compact (as well as secure)
ie
"EXEC USP_GetMyData"
rather than
SELECT SQ.*" &_
", ST.COcontactNameLast AS SendTo" &_
", BT.COcontactNameLast AS BillTo" &_
", R.COcontactNameLast AS Requestor" &_
" FROM workrequest SQ" &_
" LEFT JOIN clientoffice ST ON SQ.WRsendToId=ST.COid" &_
" LEFT JOIN clientoffice BT ON SQ.WRbillToId=BT.COid" &_
" LEFT JOIN clientoffice R ON SQ.WRrequestorId=R.COid" &_
" WHERE SQ.WRid= & wrid
and
"EXEC USP_UpdateMyData @parameter1,@parameter2................"
if you have details of the attempted seperate update statements then feel free to post them and i can knock together a simple stored procedure for you to call
MVDBA
January 17, 2006 at 8:31 am
You can try the update as shown below.
You can also wrap 2 statements in one
BEGIN TRANSACTION...COMMIT TRANSACTION
if you really want the change to be atomic.
Begin Tran
UPDATE workrequest
SET Address = @address WHERE WRrequestorId = @ID
IF @@Error = 0
UPDATE clientoffice
SET product= @product WHERE billToId=(SELECT WRrequestorID FROM workrequest WHERE workrequestorID = @ID)
IF @@Error = 0
Commit Tran
Else
Rollback Tran
End
Mike suggestion of using stored proc is also good practice.
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply