Adding images in SQL 2010

Lefteris

New member
Joined
Jan 11, 2010
Messages
4
Programming Experience
3-5
Dear's
I have deploy my code in VB.NET in order to select my imges and pass them into an SQL Server and later open them by a WEB page in a web site
Well until the point before of writing the image all goes smoothly
But when i'm trying to pass the picture to the remote SQL server then i receive the error of 'Access Denied'
Please take the code of the sub where the image try to pass to the table

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

Public Sub WriteFileStream(ByVal imSource As Byte(), ByVal imSize As Integer, ByVal imTy As Type, ByVal sender As Object, ByVal e As EventArgs)
Dim subProvider As String = Nothing
Dim subDataSource As String = Nothing
Dim subUid As String = Nothing
Dim subPwd As String = Nothing
Dim subDataBase As String = Nothing
Dim subPSI As Boolean = Nothing
Dim ParamXML() As String = Nothing
Dim TypeOfServer As String = "Remote"
Dim imParam(3) As String
Dim imTable(1) As String
Dim RemoteSQLcmd As SqlCommand
Dim RemoteSQLrd As SqlDataReader
Dim tokenReader As SqlDataReader
'-------------------------------------------------
Dim fileToken As SqlBinary
Dim AbsRecord As Int64 = 0
Dim RowCount As Integer
Dim FileGUI As System.Guid
Dim GUIstring As String
'-------------------------------------------------
ParamXML = Split(loadXmlFile(TypeOfServer, sender, e), "|")
subUid = ParamXML(3)
Dim SchemaID As String = Convert.ToInt16(ParamXML(8))
Dim SchemaName As String = Nothing
If SchemaID = 1 Then
SchemaName = subUid
ElseIf SchemaID = 2 Then
SchemaName = "dbo"
ElseIf SchemaID = 0 Then
SchemaName = "dbo"
End If
'-------------------------------------------------
imTable(0) = "tPDetails"
imTable(1) = "tPImages"
Try
imParam(0) = Me.TextBox1.Text.Trim.ToString 'Name
imParam(1) = Me.TextBox2.Text.Trim.ToString 'Code
imParam(2) = Me.TextBox3.Text.Trim.ToString 'Price
imParam(3) = Me.TextBox4.Text.Trim.ToString 'Comments
'=======================
SQL_Connection(TypeOfServer, TypeOfServer & "Conn.xml", sender, e)
RemoteSQLConn.Open()
'----------------------
Dim imHolder As Image = Image.FromStream(imStream)
Dim imHeight As Integer = imHolder.Height
Dim imWidth As Integer = imHolder.Width
Dim imLength As Integer = imHolder.PropertyItems.Length
Dim imType As Type = imTy
'----------------------
Dim FirstColumnNames As String = _
imTable(0) & "_Code, " & _
imTable(0) & "_Price, " & _
imTable(0) & "_Title, " & _
imTable(0) & "_Type, " & _
imTable(0) & "_Height, " & _
imTable(0) & "_Width, " & _
imTable(0) & "_Comments "
Dim FirstFieldsValues As String = "'" & imParam(1) & "', '" & _
imParam(2) & "', '" & _
imParam(0) & "', '" & _
imType.ToString & "', '" & _
imHeight & "', '" & _
imWidth & "', '" & _
imParam(3) & "' "
''-----------------------------------------------
RemoteSQLcmd = New SqlCommand("INSERT INTO " & _
SchemaName & "." & imTable(0) & " (" & FirstColumnNames & ") VALUES (" & FirstFieldsValues & ") ", RemoteSQLConn)
RemoteSQLcmd.ExecuteNonQuery()
'---------------------------------------
RemoteSQLcmd = New SqlCommand("SELECT * FROM " & SchemaName & "." & imTable(0) & _
" WHERE " & imTable(0) & "_Code = " & "'" & imParam(1) & "'", RemoteSQLConn)
AbsRecord = RemoteSQLcmd.ExecuteScalar
'-----------------------------------
RemoteSQLcmd = New SqlCommand("INSERT INTO " & SchemaName & "." & imTable(1) & _
" VALUES (newid(), " & AbsRecord & ", CAST('' as varbinary(max)))", RemoteSQLConn)
RemoteSQLcmd.ExecuteNonQuery()
'----------------------------------
RemoteSQLcmd = New SqlCommand("SELECT " & imTable(1) & "_Image.PathName() FROM " & _
SchemaName & "." & imTable(1) & " WHERE " & imTable(1) & "_" & imTable(0) & "_ID = " & AbsRecord, RemoteSQLConn)
Dim filePathName As String = Nothing
Dim pathObj As Object = RemoteSQLcmd.ExecuteScalar()
'----------- Path Name
If Not pathObj.Equals(DBNull.Value) Then
filePathName = DirectCast(pathObj, String)
Else
Throw New System.Exception("Image.PathName() failed to read the path name for the Image column.")
End If
'---------------- GET_FILESTREAM_TRANSACTION_CONTEXT()
Dim RemoteSQLtx As SqlTransaction = RemoteSQLConn.BeginTransaction("MainTransaction")
RemoteSQLcmd = New SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", RemoteSQLConn, RemoteSQLtx)
Dim tokenObject As Object = RemoteSQLcmd.ExecuteScalar()

'------------ File Token
tokenReader = RemoteSQLcmd.ExecuteReader(CommandBehavior.SingleRow)
tokenReader.Read()
fileToken = DirectCast(tokenObject, Byte())
tokenReader.Close()

Dim imImage As Byte() = New Byte(imStream.Length) {}
Dim bytesRead As Integer = imStream.Read(imImage, 0, imStream.Length)
'==================================
RemoteServerConnection("Remote", sender, e)
'==================================
Dim sqlFile As SqlFileStream
sqlFile = New SqlFileStream(filePathName, fileToken, FileAccess.Write)
While bytesRead > 0
sqlFile.Write(imImage, 0, bytesRead)
bytesRead = imStream.Read(imImage, 0, imSize)
End While
RemoteSQLtx.Commit()

Catch ex As Exception
MessageBox.Show(ex.Message, "WriteFileStream ", MessageBoxButtons.OK, MessageBoxIcon.Stop)
Finalize()
Finally
RemoteSQLConn.Close()
imStream.Close()
End Try

End Sub

@@@@@@@@@@@@@@@@@@@@@

Now the error comes when i'm trying to execute the line
sqlFile = New SqlFileStream(filePathName, fileToken, FileAccess.Write)
This execution return me the error of Acces Denied

Until now i have made the most posible combinations around the Login name and the User name in my database but nothing the server returns me the same error

Now PLEASE is there anybody which he has face this issue before or he has any idea to over come it?

Just tell me anything pass throu your mind (regarding the above) it will be very helpfull
 
Top Bottom