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
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()
objThisCommand = New SqlCommand("proc_Review_put_StreamFiles", SQLCon, objThisTransaction)
objThisCommand.CommandType = CommandType.StoredProcedure
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()
objThisCommand.ExecuteNonQuery()
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()
End If
the code seems to fail on "
objThisTransaction.Commit()
"