CODEWITHSUNDEEP
sql-servervba
Zach
Zach

Reputation: 347

Inserting files into SQL Server using vba issue

I'm trying to incorporate the following code into my existing code.

Here is my test code that works:

Sub test()
Dim con As ADODB.Connection
Dim rs As ADODB.recordSet
Set con = New ADODB.Connection
Set rs = New ADODB.recordSet

con.Open "Provider=SQLOLEDB;Password=;User ID=;Initial 
Catalog='" & Worksheets("Settings").Range("C2").Value & _
        "';Data Source='" & Worksheets("Settings").Range("A2").Value & "';"

'Open database connection
rs.ActiveConnection = con

rs.Open "select * from QuoteData", con, adOpenKeyset, 
adLockOptimistic

Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.LoadFromFile "c:\temp\pictures\pic1.jpg"
rs.Fields("PII_Image1").Value = mstream.Read
rs.Update
End Sub

This is the code I'm using in my existing code. UpdateQuery is part of a larger string. I want to integrate it with my existing update query.

Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.LoadFromFile "c:\temp\pictures\pic1.jpg"
updateQuery = updateQuery & "PII_Image1=convert(varbinary(MAX),'mstream.Read'),"
mstream.Close

This code runs, but it only inserts something into the database that looks like '0x6D73747265616D2E52656164' which isn't correct. I'm trying to figure out why. If I take away the convert piece, it says it can't convert varchar to varbinary(MAX).

Upvotes: 1

Views: 491

Answers (1)

Ken Love
Ken Love

Reputation: 178

Use updateQuery = updateQuery & "PII_Image1 = 0x" & BinaryToHex(mstream.Read) & "),"

Also, add the following function to your module:

'Simple binary-to-hex Function
'2003 Antonin Foller, Motobit Software
'BinaryToHex is byte-to-byte VBS function and it is suitable only for small amount of binary data - up to 100kB.
'If you want to work with bigger size, please see HexString property of Motobit's ScriptUtil.ByteArray object.

Private Function BinaryToHex(Binary)
  Dim c1, Out, OneByte

  'For each source byte
  For c1 = 1 To LenB(Binary)
    'Get the byte As hex
    OneByte = Hex(AscB(MidB(Binary, c1, 1)))

    'append zero For bytes < 0x10
    If Len(OneByte) = 1 Then OneByte = "0" & OneByte

    'join the byte To OutPut stream
    Out = Out & OneByte
  Next

  'Set OutPut value
  BinaryToHex = Out
End Function

Upvotes: 1

Related Questions