Delete and insert data in same transaction!

Hi!
I try to add records in one table from a VB6 com+ component using ADO
(provider=SQLOLEDB, SQL-server2000).
Basically what I try to do is to
1. Start a transaction
2. Delete old records in one table
3. Add new values in the same table
4. commit transaction
The problem is that I get a duplicate key when I insert my new data in
step3. If I commit the data between step 2 and 3 everything works fine. If
step 4 then fails I will end up with no data in the table which I don't want
(i mean that is what transaction is used for).
This must be a pretty common scenario so I hope there will be a solution
that will not force me to commit the transaction in the middle.
Regards
/HansHi Hans,
as you descibed the scenario should be fine, allowing the transaction
to commit without problems, perhaps there is a logical problem in your
code. Could you please post the code here. This would make error
searching much easier for us.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Hans wrote:
> Hi!
> I try to add records in one table from a VB6 com+ component using ADO
> (provider=SQLOLEDB, SQL-server2000).
> Basically what I try to do is to
> 1. Start a transaction
> 2. Delete old records in one table
> 3. Add new values in the same table
> 4. commit transaction
> The problem is that I get a duplicate key when I insert my new data in
> step3. If I commit the data between step 2 and 3 everything works fine. If
> step 4 then fails I will end up with no data in the table which I don't wa
nt
> (i mean that is what transaction is used for).
> This must be a pretty common scenario so I hope there will be a solution
> that will not force me to commit the transaction in the middle.
> Regards
> /Hans
Do you mean you want to delete and then insert new row(s) with the same
key values? That may not be an optimal solution since you could
accomplish the same thing with an UPDATE. The following works for me.
If this example doesn't help then please post some code so that we can
reproduce the problem.
CREATE TABLE tbl (x INT PRIMARY KEY);
INSERT INTO tbl(x) VALUES (1);
BEGIN TRAN;
DELETE FROM tbl WHERE x=1;
INSERT INTO tbl(x) VALUES (1);
COMMIT TRAN;
SELECT x FROM tbl;
I recommend you put the DELETE/INSERT code in a stored procedure and
execute the proc from your VB code.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> The problem is that I get a duplicate key when I insert my new data in
> step3.
This indicates that INSERTs are occurring on a different connection that the
DELETE and not within the same transaction context. You can run a SQL
Profiler trace to see the actual behavior.
Note that ADO is particularly nasty about opening additional connections
behind your back. It is important to include 'SET NOCOUNT ON' in
procs/scripts and process all results returned so that connections can be
reused.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hans" <hansb@.sorry.nospam.com> wrote in message
news:OXTNpYnSGHA.2156@.tk2msftngp13.phx.gbl...
> Hi!
> I try to add records in one table from a VB6 com+ component using ADO
> (provider=SQLOLEDB, SQL-server2000).
> Basically what I try to do is to
> 1. Start a transaction
> 2. Delete old records in one table
> 3. Add new values in the same table
> 4. commit transaction
> The problem is that I get a duplicate key when I insert my new data in
> step3. If I commit the data between step 2 and 3 everything works fine. If
> step 4 then fails I will end up with no data in the table which I don't
> want
> (i mean that is what transaction is used for).
> This must be a pretty common scenario so I hope there will be a solution
> that will not force me to commit the transaction in the middle.
> Regards
> /Hans
>
>|||Hi Jens, David and Dan!
Thanks for your replies.
Here is the code. The code is used to store default values for a user. The
table have fields for which user it is (idUser), which field (idfld) and
some other fields about the default values. The code is most likely not the
most efficient (the "IN" operator is slow but we are talking about pretty
small tables here with a couple of 1000 records) but it is only executed a
couple of times/year for a normal user (and it only takes like 100
milliseconds to excecute as it is). The key in the table is idUser (user id)
and idfld (Field id) together and I'm not sure if there is a way to update
or add in one single SQL-statement and also delete records where I earlier
had defaultvalues but where the user no longer want to have default values.
Therfor I delete all defaultvalues for the current user for the current
table (I join in another table which holds the table id). For example there
may be 5 rows before the save and maybe only 3 rows left in the table after
the update.
If OS_WIN2000 Then
Set rs = CreateObject("ADODB.Recordset")
Set con = CreateObject("ADODB.Connection")
Else
Set rs = CtxCreateObject("ADODB.Recordset")
Set con = CtxCreateObject("ADODB.Connection")
End If
con.Open GetConnectionString()
'Use transaction
con.BeginTrans
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
'idtbl and idUser is already singlequoted
For i = LBound(userList) To UBound(userList)
sSQL = "Delete from " & TSP("vmo_base_defaultvalues") & " where iduser="
& userList(i) & " and "
sSQL = sSQL & " idfld in (select vmo_base_field.idfld from " &
TSP("vmo_base_defaultvalues") & ","
sSQL = sSQL & TSP("vmo_base_field") & " where
vmo_base_field.idfld=vmo_base_defaultvalues.idfld "
sSQL = sSQL & " and vmo_base_field.idtbl = " & idTBL & ")"
rs.Open sSQL, con
'If I commit here it works OK but I want to commit after the entire
operation is finished
For l = LBound(sArg) To UBound(sArg) Step 4
If sArg(l) <> "" And sArg(l + 1) <> "" Then
sSQL = "Insert into " & TSP("vmo_base_defaultvalues") & "
(idUser,idfld,vValue,datevalue, deftype) " & _
"values (" & _
userList(i) & "," & _
sArg(l) & "," & _
sArg(l + 1) & "," & _
sArg(l + 2) & "," & _
sArg(l + 3) & ")"
rs.Open sSQL, con
End If
Next l
Next i
con.CommitTrans
Regards
/Hans|||The main problem is that you are using recordsets when no data are returned.
Additional connections are probably acquired for the subsequent INSERT
statements and outside scope of the first transaction.
The example below shows how to use Command objects for these DML statements.
I would also suggest using parameters instead of concatenating literal
values. Parameters are more secure, eliminate the need to quote values,
escape quotes, format dates, etc.
Set con = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
con.Open GetConnectionString()
cmd.ActiveConnection = con
con.BeginTrans
'idtbl and idUser is already singlequoted
For i = LBound(userList) To UBound(userList)
sSQL = "SET NOCOUNT ON Delete from " & _
TSP("vmo_base_defaultvalues") & _
" where iduser=" & _
userList(i) & " and "
sSQL = sSQL & " idfld in (select vmo_base_field.idfld from " &
TSP("vmo_base_defaultvalues") & ","
sSQL = sSQL & TSP("vmo_base_field") & _
" where vmo_base_field.idfld=vmo_base_defaultvalues.idfld "
sSQL = sSQL & " and vmo_base_field.idtbl = " & idTBL & ")"
cmd.CommandText = sSQL
cmd.Execute
For l = LBound(sArg) To UBound(sArg) Step 4
If sArg(l) <> "" And sArg(l + 1) <> "" Then
sSQL = "SET NOCOUNT ON Insert into " & _
TSP("vmo_base_defaultvalues") & _
"(idUser,idfld,vValue,datevalue, deftype) " & _
"values (" & _
userList(i) & "," & _
sArg(l) & "," & _
sArg(l + 1) & "," & _
sArg(l + 2) & "," & _
sArg(l + 3) & ")"
cmd.CommandText = sSQL
cmd.Execute
End If
Next l
Next i
con.CommitTrans
Hope this helps.
Dan Guzman
SQL Server MVP
"Hans" <hansb@.sorry.nospam.com> wrote in message
news:%23%23EoJT2SGHA.1148@.TK2MSFTNGP10.phx.gbl...
> Hi Jens, David and Dan!
> Thanks for your replies.
> Here is the code. The code is used to store default values for a user. The
> table have fields for which user it is (idUser), which field (idfld) and
> some other fields about the default values. The code is most likely not
> the
> most efficient (the "IN" operator is slow but we are talking about pretty
> small tables here with a couple of 1000 records) but it is only executed a
> couple of times/year for a normal user (and it only takes like 100
> milliseconds to excecute as it is). The key in the table is idUser (user
> id)
> and idfld (Field id) together and I'm not sure if there is a way to update
> or add in one single SQL-statement and also delete records where I earlier
> had defaultvalues but where the user no longer want to have default
> values.
> Therfor I delete all defaultvalues for the current user for the current
> table (I join in another table which holds the table id). For example
> there
> may be 5 rows before the save and maybe only 3 rows left in the table
> after
> the update.
> If OS_WIN2000 Then
> Set rs = CreateObject("ADODB.Recordset")
> Set con = CreateObject("ADODB.Connection")
> Else
> Set rs = CtxCreateObject("ADODB.Recordset")
> Set con = CtxCreateObject("ADODB.Connection")
> End If
> con.Open GetConnectionString()
> 'Use transaction
> con.BeginTrans
> rs.CursorLocation = adUseClient
> rs.CursorType = adOpenStatic
> rs.LockType = adLockOptimistic
> 'idtbl and idUser is already singlequoted
> For i = LBound(userList) To UBound(userList)
> sSQL = "Delete from " & TSP("vmo_base_defaultvalues") & " where
> iduser="
> & userList(i) & " and "
> sSQL = sSQL & " idfld in (select vmo_base_field.idfld from " &
> TSP("vmo_base_defaultvalues") & ","
> sSQL = sSQL & TSP("vmo_base_field") & " where
> vmo_base_field.idfld=vmo_base_defaultvalues.idfld "
> sSQL = sSQL & " and vmo_base_field.idtbl = " & idTBL & ")"
> rs.Open sSQL, con
> 'If I commit here it works OK but I want to commit after the entire
> operation is finished
> For l = LBound(sArg) To UBound(sArg) Step 4
> If sArg(l) <> "" And sArg(l + 1) <> "" Then
> sSQL = "Insert into " & TSP("vmo_base_defaultvalues") & "
> (idUser,idfld,vValue,datevalue, deftype) " & _
> "values (" & _
> userList(i) & "," & _
> sArg(l) & "," & _
> sArg(l + 1) & "," & _
> sArg(l + 2) & "," & _
> sArg(l + 3) & ")"
> rs.Open sSQL, con
> End If
> Next l
> Next i
> con.CommitTrans
>
> Regards
> /Hans
>|||Thanks Dan for the tip!
Yes that seems to fix the problem. The code is pretty old and written for
Oracle in the first place where I did not have any problems with the
transaction (so I guess the real problem is inside the oledb provider). Yes
you are right using parameters is much safer but at least I don't like
touching code that has been working for years, well at least for other
databases than SQL-server :-)
/Hans