Insert BLOB in SQL server

[humpydumpy2000]

Hola a todos,

I have this function working smoothly in MySQL database however I am puzzled why it does not work in SQL Server. Any thoughts?
Thanks in advance!



Dim objStream As Object 'ADODB.Stream
Dim objCmd As Object 'ADODB.Command
Dim varFileBinary

'Empty any matching record
CurrentDb.Execute "DELETE FROM tblBLOB WHERE FKTable = '" & strTable & "' AND FKID = " & lngFKID

Set objStream = CreateObject("ADODB.Stream")
objStream.Type = 1 'adTypeBinary
objStream.Open
objStream.LoadFromFile strFileName
varFileBinary = objStream.Read
objStream.Close
Set objStream = Nothing

Set objCmd = CreateObject("ADODB.Command")
With objCmd
.CommandText = "PARAMETERS paramID Long, paramTable Text(255), paramDesc Text(255), paramExtn Text(5), paramFile LongBinary;" & _
"INSERT INTO tblBLOB (FKID, FKTable, BLOBDesc, FileExtn, BLOB) " & _
"SELECT paramID, paramTable, paramDesc, paramExtn, paramFile"
.CommandType = 1 'adCmdText
.Parameters.Append .CreateParameter("paramID", 3, 1, 4, lngFKID)
.Parameters.Append .CreateParameter("paramTable", 200, 1, 255, strTable)
.Parameters.Append .CreateParameter("paramDesc", 200, 1, 255, strDesc)
.Parameters.Append .CreateParameter("paramExtn", 200, 1, 5, Right(strFileName, Len(strFileName) - InStrRev(strFileName, ".")))
.Parameters.Append .CreateParameter("paramFile", 205, 1, 2147483647, varFileBinary)
Set .ActiveConnection = CurrentProject.Connection
.Execute , , 128
End With

InsertBLOB = True

 

[Blorf]

I have used functions found here successfully with SQL server 2014 and access 2013.

http://www.ammara.com/access_image_faq/read_write_blob.html

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That DonÆt.

http://www.thelegacyreturns.com/

 

[ByteMyzer]

For MSSQL, Try changing:

.CommandText = "PARAMETERS paramID Long, paramTable Text(255), paramDesc Text(255), paramExtn Text(5), paramFile LongBinary;" & _
"INSERT INTO tblBLOB (FKID, FKTable, BLOBDesc, FileExtn, BLOB) " & _
"SELECT paramID, paramTable, paramDesc, paramExtn, paramFile"

...to:

.CommandText = "INSERT INTO tblBLOB (FKID, FKTable, BLOBDesc, FileExtn, BLOB) " & _
"SELECT ?, ?, ?, ?, ?;"

 

[humpydumpy2000]

thanks a lot