SQL SERVER - easy

[uni]

Hi,
This is probably an easy question to answer.

How would I code a procedure in SQL Server to do the followibg. Accept a variable(var1) and a recordset(rst) sent from a VB6 app, and then:

1)delete * from table1 where field1 = var1
then
2)add rst to table1

The important thing is that if two people run the procedure at the same time, l have to make sure that it runs the delete and add straight after each other and not run the delete for person 1 and then the delete for person 2 and then the add for person 1 and the add for person 2 (otherwise l will get duplicates). I guess this will require record locking.

Also how would l call this procedure from vb6.

THANKS HEAPS.
UNI.

[Iouri]

1.set the connection object (cn)

sSQL = "delete from Table where Field = " & var ' if var is a number
sSQL = "delete from Table where Field = '" & var & "'" ' if var is a string

cn.Execute sSQL

2.Add recordset to table

rs - is the recordset which you want tot enter to the table
prepare recordset for the table where you will enter records (RS1)
sSQL = "select * from Table"
RS1.Open sSQL,cn,adopendynamic,adLockOptimistic
With rs
.MoveFirs
Do while not .EOF
.AddNew
RS1!Field1 = !FieldA
RS1!Field2 = !FieldB
'FIeld1,2 from RS1, FieldA,B from rs
.Update
.MoveNext
Loop
End With








Iouri Boutchkine
[email protected]

[Clearcode]

This is a job for transactions.

You need to add a "BEGIN TRANSACTION " and "COMMIT TRANSACTION " pair aorund the code that does all the deleteing and adding of records.
This done, SQL server will take care of th elocking for you.

HTH,
Duncan

-------------------------------------------------
Ex. Datis: Duncan Jones
Merrion Computing Ltd
http://www.merrioncomputing.com