Inserting files into SQL Server using vba issue

Question

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).

You're saying the first section of code runs even with the stray ' quote? Looking at the lines above it, I think there's an issue with that part of code. Also, what are you expecting to be added to the database? A file? — ashleedawg
– ashleedawg, Commented Oct 10, 2018 at 15:46
Unless I'm missing something there is no stray quote, but the code I have does run and inserts into the database correctly. I'm expecting to add JPG image files and maybe pdf's eventually. — Zach
– Zach, Commented Oct 10, 2018 at 15:51

Ken Love · Accepted Answer · 2018-10-12 14:39:36Z

1

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

edited Oct 12, 2018 at 14:39

answered Oct 10, 2018 at 20:13

Ken Love

1786 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Zach Over a year ago

That gives "Incorrect syntax near '0xd8ff'. Using ' " & mstream.Read & " ' gets further saying "Unclosed quotation mark after the character string '?????????Ea'.

Ken Love Over a year ago

Try updateQuery = updateQuery & "PII_Image1=" & mstream.Read & ",". This intentionally avoids the convert and single quotes, because if your underlying field is binary then you can set it directly to a binary value.

Zach Over a year ago

That line gives "Incorrect syntax near ','." which I can fix by adding apostrophes like updateQuery = updateQuery & "PII_Image1='" & mstream.Read & "'," It then complains about not being able to convert from varchar to varbinary(max). We should be close because if I add msgbox mstream.read it does match both my working example and the in progress example.

Ken Love Over a year ago

Just to confirm, what is the SQL data type of the field "PII_Image1"?

Zach Over a year ago

It's varbinary(MAX).

|

Collectives™ on Stack Overflow

Inserting files into SQL Server using vba issue

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related