SqlTransaction won't commit

[jack-b]

Hi,

I'm running a process which takes records from an XML file and inserts
them one at a time into a database table. I'm using an SqlTransaction
to commit after each record is inserted but it doesn't seem to work. (I
need to commit each time because I sleep the thread after inserting 10
record for 1hour)

Code -

mySqlConnection = SqlConnection
mySqlConnection.ConnectionString = ConnectionString
mySqlConnection.Open()

mySqlTransaction = CreateJobDetails.mySqlConnection.BeginTransaction()

* retrieve record from XML file CODE ***

* push to DB ***

//assign parameter code etc etc

SqlHelper.ExecuteNonQuery( _
mySqlTransaction, _
CommandType.StoredProcedure, _
ProcName, _
InsertParameter)

If InsertParameter(INDEX_RETURN_VALUE).Value <> 0 Then
mySqlTransaction.Rollback()
Throw New
StoredProcedureException(InsertParameter(INDEX_ERROR).Value)

Else
mySqlTransaction.Commit()
mySqlTransaction.Dispose()
mySqlConnection.Close()
End If

***************************************
Do I have to state something in the stored procedure??

Any ideas??

Cheers,
Jack

 

[Jim Hughes]

Hi,

I'm running a process which takes records from an XML file and inserts
them one at a time into a database table. I'm using an SqlTransaction
to commit after each record is inserted but it doesn't seem to work. (I
need to commit each time because I sleep the thread after inserting 10
record for 1hour)

Code -

mySqlConnection = SqlConnection
mySqlConnection.ConnectionString = ConnectionString
mySqlConnection.Open()

mySqlTransaction = CreateJobDetails.mySqlConnection.BeginTransaction()

* retrieve record from XML file CODE ***

* push to DB ***

//assign parameter code etc etc

SqlHelper.ExecuteNonQuery( _
mySqlTransaction, _
CommandType.StoredProcedure, _
ProcName, _
InsertParameter)

If InsertParameter(INDEX_RETURN_VALUE).Value <> 0 Then
mySqlTransaction.Rollback()
Throw New
StoredProcedureException(InsertParameter(INDEX_ERROR).Value)

Else
mySqlTransaction.Commit()
mySqlTransaction.Dispose()
mySqlConnection.Close()
End If

***************************************
Do I have to state something in the stored procedure??

Any ideas??

Cheers,
Jack

Show the contents of the stored procedure as well as the parameters being
defined along with sample values and we can probably tell you.