sqltransaction

[Guest]

im updating a header and detail table with an update constraint. sqlcmd1 is
used to update header, sqlcmd2 is for detail. both cmd runs in one
sqltransaction.
my problem is my second sqlcmd catches a constraint error when updating the
detail table. is it because my record in the header table is not yet commited
and my foreignkey in the detail table requires that the primarykey already
exists in the header table?

is there a way to implement sqltransaction in header,detail table with
update constraint? how?

 

[Chad Z. Hower aka Kudzu]

is there a way to implement sqltransaction in header,detail table with
update constraint? how?

What database are you using? Databases usually account for this fact and also look at records
from the current transaction.

 

[Guest]

i am using sql server 2000. when i tested the same task, but instead of
placing the commands(sql) in storeprocedure,. i placed them inline in the
vbnet code,..there's no error. but i need them to be in the storedprocedure.
tnx

 

[Chad Z. Hower aka Kudzu]

i am using sql server 2000. when i tested the same task, but instead
of placing the commands(sql) in storeprocedure,. i placed them inline
in the vbnet code,..there's no error. but i need them to be in the
storedprocedure. tnx

If the problem only exists in the stored procedure, you should try the SQL server groups. Im sure
you'll have a better chance of getting an answer there.

 

[Rogas69]

Can you post the code? i believe that this is something like - you add a
record to master table, get identity value and add record to details table
with this value. I am right? Sou said that if you use inline sql it works.
Are you sure that the code is equivalent? I am thinking for example about
"set implicit_transactions off" statement.

Peter

 

[Guest]

ok thank you....nways,..i got it working......

 

[Guest]

yes!!! you're right,..only that the Identity value is generated in my
dataaccess class because its a string series. my store procedures are called
by sqlcommand objects. one for the master table and the other is for the
detail table.
my code when simplified,..looks like this

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim cn As SqlConnection
Dim cmd1 As SqlCommand
Dim trn As SqlTransaction
Dim Id As String

Id = "01"

cmd1 = New SqlCommand

'dt = New DataTable
'da = New SqlDataAdapter

Try

trn = cn.BeginTransaction(IsolationLevel.ReadCommitted)

' for header
With cmd1
.CommandText = "Insertheader"
.CommandType = CommandType.StoredProcedure
.Connection = cn
.Transaction = trn
.Parameters.Add("@Id", Id)
.Parameters.Add("@FirstName", TextBox1.Text)
.Parameters.Add("@MiddleName", TextBox2.Text)
.Parameters.Add("@LastName", TextBox3.Text)
.ExecuteNonQuery()

' for details

.CommandText = "InsertDetail"

.Parameters.Clear()
.Parameters.Add("@Id", "1")
.Parameters.Add("@IdHeader", Id)
.Parameters.Add("@Address", TextBox4.Text)
.ExecuteNonQuery()

.Parameters.Clear()
.Parameters.Add("@Id", "2")
.Parameters.Add("@IdHeader", Id)
.Parameters.Add("@Address", TextBox5.Text)
.ExecuteNonQuery()

.Parameters.Clear()
.Parameters.Add("@Id", "3")
.Parameters.Add("@IdHeader", Id)
.Parameters.Add("@Address", TextBox6.Text)
.ExecuteNonQuery()

End With

trn.Commit()

Catch ex As Exception
trn.Rollback()
End Try

End Sub

 

[Rogas69]

Hmm.. Just quess - try to change isolation level of the transaction and see
what happens. Also put
SET IMPLICIT_TRANSACTIONS { ON | OFF } statements in your stored procedures.

HTH

Peter

 

[Guest]

i tried SET IMPLICIT_TRANSACTIONS {OFF } and {ON} both in the
storedprocedures. still i get the insert constraint error. in a simple stored
procedure,...the problem does not exist. the real sprocs that im using has
some other updating ang quareying in them. what should i do?

one more question....which is better in selects,..view or sp?

 

[Rogas69]

so if you use simple stored procedure, everything is ok? so the code in your
stored procedure may be doing something with the master id.. it is just a
guess.. try to catch the call to mastersp in profiler and execute it in
query analyzer. modify stored procedure to display everything that interests
you.
on the other hand sp vs views. as Chad said in other thread, there are
situations when view is more convenient to use. In my recent project i have
5 views to retrieve some data, but usually I use stored procedures.

Peter