An uncommittable transaction was detected at the beginning of the batch. The transaction was rolled back. This was caused by an error that occurred during the processing of a FILESTREAM request in...

See more:

VB

Hi, i'm trying to save documents into my sql database using SqlFileStream. the database filestream setup seems to be working ok and when i run a query on to add a document it seems to be saving it but when i try saving a document through vb.net i'm getting this error

"An uncommittable transaction was detected at the beginning of the batch. The transaction was rolled back. This was caused by an error that occurred during the processing of a FILESTREAM request in the context of this transaction."

here is my vb.net code

'Declare Database objects
           Dim SQLCon As SqlConnection
           Dim objThisTransaction As SqlTransaction
           Dim objThisCommand As New SqlCommand


           Dim strPath As String
           Dim objPatIdParam As SqlParameter
           Dim objFileTypeParam As SqlParameter
           Dim objFilePathParam As SqlParameter
           Dim objActionTakenByParam As SqlParameter

           Dim objDataAdapter As New SqlDataAdapter


           Dim buffer As Byte() = New Byte(CInt(FileUpload1.FileContent.Length) - 1) {}
           FileUpload1.FileContent.Read(buffer, 0, buffer.Length)
           If FileUpload1.FileContent.Length > 0 Then

               Dim conn As String = "Data Source=.;Initial Catalog=Review;Integrated Security=true;Trusted_Connection=Yes;"
               SQLCon = New SqlConnection(conn)
               SQLCon.Open()

               objThisTransaction = SQLCon.BeginTransaction()

               ' Specify the details of the Stored Procedure to execute
               objThisCommand = New SqlCommand("proc_Review_put_StreamFiles", SQLCon, objThisTransaction)
               objThisCommand.CommandType = CommandType.StoredProcedure

               ' Clear the parameters list
               objThisCommand.Parameters.Clear()

               objPatIdParam = objThisCommand.Parameters.Add("@PatId", SqlDbType.VarChar, 75)
               objPatIdParam.Value = Session("PatId").ToString
               objFileTypeParam = objThisCommand.Parameters.Add("@FileType", SqlDbType.VarChar, 5)
               objFileTypeParam.Value = System.IO.Path.GetExtension(FileUpload1.FileName)
               objFilePathParam = objThisCommand.Parameters.Add("@FilePath", SqlDbType.VarChar, -1)
               objFilePathParam.Direction = ParameterDirection.Output
               objActionTakenByParam = objThisCommand.Parameters.Add("@ActionTakenBy", SqlDbType.VarChar, 50)
               objActionTakenByParam.Value = Session("UserId").ToString()
               ' Now execute the stored procedure
               objThisCommand.ExecuteNonQuery()

               'file path of the document
               strPath = CStr(objFilePathParam.Value)

               objThisCommand = New SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", SQLCon, objThisTransaction)
               Dim objContext As Byte() = DirectCast(objThisCommand.ExecuteScalar(), Byte())


               Dim objSqlFileStream As New SqlFileStream(strPath, objContext, FileAccess.Write)

               objSqlFileStream.Write(Buffer, 0, Buffer.Length)
               objSqlFileStream.Close()

               objThisTransaction.Commit()
               SQLCon.Close()
               'imp.ImpersonateStop()


           End If

the code seems to fail on "

objThisTransaction.Commit()

"