help with creating a sql transaction

[Jake Smythe]

Hello,

I have a dataset that is being passed to a webservice. It has about 5
records. I call a stored proc that inserts for the first record but errors
out for the others as the connection is already open. I am wanting to have a
sql trans open for all so if one of the records fails all are rolled back.
Could someone provide me with a simple way to do this? Below is my code. I
think I need a main transaction with little ones inside but I am not sure if
thats correct or how to code that. The error is "There is already an open
DataReader associated with this connection which must be closed first".
Thanks in advance.

Dim blnInsertValid As Boolean, i As Integer

Dim strDBName As String = "iv"

Dim sqlTransaction As SqlTransaction

Dim paramIntPeopleID As SqlParameter, paramDtMDate As SqlParameter,
paramIntProjectID As SqlParameter

Dim paramDecStartMile As SqlParameter, paramDecEndMile As SqlParameter,
paramDecMiles As SqlParameter

Dim paramDecRate As SqlParameter, paramStrMiscInfo As SqlParameter,
paramStrDBName As SqlParameter

Dim bolPassed As SqlParameter

Dim SqlConn As New SqlConnection(strConn)

Try

SqlConn.Open()

Dim sqlCmd As SqlCommand = SqlConn.CreateCommand

sqlCmd.CommandText = "usp_InsertMileageData"

sqlCmd.CommandType = CommandType.StoredProcedure

sqlTransaction = SqlConn.BeginTransaction()

sqlCmd.Connection = SqlConn

sqlCmd.Transaction = sqlTransaction

paramIntPeopleID = sqlCmd.Parameters.Add("@peopleid", SqlDbType.Int)

paramIntPeopleID.Direction = ParameterDirection.Input

paramDtMDate = sqlCmd.Parameters.Add("@mdate", SqlDbType.DateTime)

paramDtMDate.Direction = ParameterDirection.Input

paramIntProjectID = sqlCmd.Parameters.Add("@projid", SqlDbType.Int)

paramIntProjectID.Direction = ParameterDirection.Input

paramDecStartMile = sqlCmd.Parameters.Add("@startmile", SqlDbType.Decimal)

paramDecStartMile.Direction = ParameterDirection.Input

paramDecEndMile = sqlCmd.Parameters.Add("@endmile", SqlDbType.Decimal)

paramDecEndMile.Direction = ParameterDirection.Input

paramDecMiles = sqlCmd.Parameters.Add("@miles", SqlDbType.Decimal)

paramDecMiles.Direction = ParameterDirection.Input

paramDecRate = sqlCmd.Parameters.Add("@rate", SqlDbType.Decimal)

paramDecRate.Direction = ParameterDirection.Input

paramStrMiscInfo = sqlCmd.Parameters.Add("@miscinfo", SqlDbType.NVarChar,
500)

paramStrMiscInfo.Direction = ParameterDirection.Input



bolPassed = sqlCmd.Parameters.Add("@bolPassed", SqlDbType.Bit)

bolPassed.Direction = ParameterDirection.Output

Dim x As Integer



With dsMileage.Tables(0)

For x = 0 To .Rows.Count - 1

paramIntPeopleID.Value = CInt(.Rows(x).Item(0))

paramDtMDate.Value = CDate(.Rows(x).Item(1)) 'dtMDate

paramIntProjectID.Value = CInt(.Rows(x).Item(2)) 'intProjectID

paramDecStartMile.Value = CDec(.Rows(x).Item(3)) 'decStartMile

paramDecEndMile.Value = CDec(.Rows(x).Item(4)) 'decEndMile

paramDecMiles.Value = CDec(.Rows(x).Item(5)) 'decMiles

paramDecRate.Value = CDec(.Rows(x).Item(6)) 'decRate

paramStrMiscInfo.Value = CStr(.Rows(x).Item(7)) 'strMiscInfo

sqlCmd.ExecuteReader()

If Not CBool(bolPassed.Value) Then

blnInsertValid = False

Exit For

Else

blnInsertValid = True

End If

Next

End With

If blnInsertValid Then

sqlTransaction.Commit()

blnInsertValid = True

Else

sqlTransaction.Rollback()

blnInsertValid = False

End If

Catch ex As SqlException

sqlTransaction.Rollback()

blnInsertValid = False

End Try

SqlConn.Close()

Return blnInsertValid

 

[Marina]

You are calling 'sqlCmd.ExecuteReader'

This returns a data reader, which you don't even save in a variable -
meaning you are not using it, so why bother doing it at all?

If you open data readers, you need to close them, since you cannot do
anything else with the connection until the datareader is closed. Why call
ExecuteReader, if you don't actually care about the data it is returning?