Two update statements

[Sarah Lee]

Hi Everybody

How can I use 2 update stmts in a single SP?
I want to update two tables, with different data, but with the same, where clause

Is there any way to do that?

thanks

[campkev]

give an example of what you are trying to do

[Sarah Lee]

I splitted the two updates.
I am trying to update one table with new user entered values
It is working fine

The problem now is with the second update, where I am trying to update some file names values into the table through listbox
here i have two listboxes, in listbox1, i have some filenames, which i can add or remove from listbox2
problem is that the file names are not getting updated.

Suppose the user chooses file1, file2, from listbox1, add to lisbox2 and then update, it is not getting updated.
i am inserting the listbox1.item.value along with the contactid.
Public Sub UpdateAccounts(ByVal contactid As Int32)
Dim userBll As UserCreationBLL = New UserCreationBLL()
Dim listitems As Int32 = lstAllAccounts.Items.Count
Dim lstItems As Int32 = lstAccountAccess.Items.Count
If (lstItems > 0) Then
Dim i As Int16
For i = 0 To lstItems - 1
userBll.UpdateAccounts(contactid, Convert.ToInt32(lstAccountAccess.Items(i).Value))
Next i
End If
End Sub

SP for updating is
ALTER PROCEDURE UpdateAccounts
@contactID int,
@accountID int
AS
UPDATE [Contact Account ]
SET [Account ID] = @accountID
WHERE [Contact ID] = @contactID

thanks

[pty]

ALTER PROCEDURE UpdateAccounts
@contactID int,
@accountID int
AS
begin transaction
  UPDATE [Contact Account ]
  SET [Account ID] = @accountID
  WHERE [Contact ID] = @contactID

  UPDATE [Other table]
  SET [Account ID] = @accountID
  WHERE [Contact ID] = @contactId
commit transaction

[Sarah Lee]

Thanks

But now i am not using two updates in a single one
I separated two updates in 2 diff SP
First one is working

second one for Updating accounts
If i add some files from listbox1 to listbox2, it is getting updated
if i remove some files from listbox2, and then update, it is not updating

how can i check for the removed items in listbox

SP i am using is
ALTER PROCEDURE UpdateAccounts
@contactID int,
@accountID int
AS
INSERT INTO [Contact Account ]([Contact ID],[Account ID])

sELECT @accountid, @contactid
WHERE NOT EXISTS (SELECT 'X' FROM [dbo].[Contact Account ]
WHERE [Account ID] = @accountid AND
[contact id] = @contactid)

[pty]

I don't think there is anything wrong with updating two tables using two statements.

If you wrap them in a transaction you will ensure that you don't only update one (ie if one fails neither are committed).