Closed Thread
Results 1 to 7 of 7

varbinary image not being created

  1. #1
    Registered User
    Join Date
    12-13-2019
    Location
    Essex
    MS-Off Ver
    365
    Posts
    3

    varbinary image not being created

    Hi, I have a problem displaying a png image via vba/adodb/sql server and I can't see why it won't work.

    The image is stored as varbinary in an sql table which I've dumped at the bottom of this thread.

    I retrieve like so, select....... convert(varchar(max),S.SignatureData,1)......etc

    I then use the following to create a temp image on the desktop

    Please Login or Register  to view this content.
    All I get is an empty image and I can't get to the nub of the issue.

    Any pointers would really help.




    stored binary data
    0x89504E470D0A1A0A0000000D49484452000000600000006408060000007909352E000000017352474200AECE1CE90000000467414D410000B18F0BFC6105000000097048597300000EC300000EC301C76FA8640000081949444154785EED9D47E8D44E14C7D7DE2F82D815B18BE845F164176C58D08B0D3CA820A87814155144112C88D8F0A082D83DA858B081072BA2880A2262051BF6DE35FFFF777EEF8D936CB22DC94C76331F78BA3B939D7979DF4D36537F19C7A2853F7FFED02B37568098B970E18293C96484F96105D08015C0302CC0E9D3A729E51F56000D7CFEFC39F02AB00268C20A609876EDDA0901C68E1D4B2955580134E27715580134C2027CFAF48952AC005A3975EA9410A076EDDA946205D08EF7366405D04C8F1E3D84003366CC10EFAD000650AF022B8001AC008661011E3E7C680530C1D4A953850073E7CEB50298A067CF9E42803D7BF658014CC0B7A077EFDE59014CC00288D7E25F8B36E6CC992382DFA04103F1DE0AA019F5DB0FAC001AE1E0AF5FBF9E52AC005A18376E9C0CFED2A54B29B58A9205C02F381A12D7AF5F17FFE3BDE51F376EDC904167F323A700BF7FFF76468C189155503156B3664DE7C58B175462E5F2E1C307A773E7CE59E7EF1D01F3E22B40DDBA75B30A1A3C7830E516469F3E7DB2CA607BF5EA151D559EBC7CF9D2E9D6AD9BEFB9C1CE9C394347E6C7250082CC85D4A9538752A3C34FD85DBB76516E32516734F859A3468DE8C8D29002A885EA60CD9A35AE3A61EA509D69BCBEC1BE7CF942B9D121A2BD72E54A598909366EDCE83A515C29A650FD406759DC8888738549C07B9BFAF5EB17E5C4CBC2850B659D18BBD545E20460366DDA24FD823D7EFC9872A247AD4B3799B56BD78A8A7BF7EE4D49C9E2CE9D3B3238B0A81F69316D9CCB364166D1A245A2F2E1C387535232C115C08182E1E9240AB8BC83070F528A5E323F7EFC904E94036879B3BF617DFEF6ED9BF17317359B76A214E6CD9B27FD6ED9B225A51647AD5AB5C4E7F7EFDF4F29FA1151E73EEA721301701061CF9E3DA3D4C248C239CBDAD1A28333E8BB2937BE7FFF2E835968CB34D79C7D9DB86AAF51A346229C2A95162D5A14ECFFCC9933C571DDBB77A714336479DABA756B7912E80D2D37C45C1BF27FD5AA55949A0D1F631A5F0FEEDDBB271D1C397224A59617EC7F50B706E79B26A707D5AA55938EE23E5B6E601A7850A083D27593D7036F03A8DC18346890AFEF49399F823D50477BCAED49E9C081035901F7BE3745D11E346BD64C3A5FBD7A754A4D3E77EFDE957EFFFCF953BE364DC91E74EAD4499E042CAABE9938F9FAF5ABCB679869427B70E8D021D709E13136C9A87D5F30D344E601DA0CEA89C192FA089B94563088C583478F1EB9848075E9D285729381EA9B4962AFFDFDFBF7E2C75A3D61187A334DE2F5C714DA6B5EB26449D6C9C3962F5F4E47E861F2E4C9593E98C0ECF5F73F63C68CC90A046CC08001E2EA8913AE0BED1AFC6F6254D0B8005E264E9C2803E3350CBC9C3871828E0C0F978BF93EFC5A378913C08F810307CA0005195AEA18D72D6672D7E1C387E5E7B19912BFD649590810047EC8D59679A186B180CB972F57ADD1A234A0BED645590B900F5C0DB366CD72DAB66D2BEFF3A558DFBE7D9DE3C78F173DE45908152D40A170A09F3C79225FFB4D24F6B3B083565600C21BD87CDFF6D9B367CB63B13565A9580108752816966F62AE7AEC962D5B28B578522340C78E1D5D418BCAFEFEFD4B3594462A04F0060DAB77D8BCE978E45DBC78B19893FAFCF973D1180CDA76380A522140AEB161D3A4E6162436C650BEED58549704522300B37AF56A971053A64CA11C33A44E00C63B80D4AB572FCAD14B6A055051A764366CD89052F560055068DFBEBDEBAAC0C85EDCA452808F1F3F3AFBF6ED73162C58E06CDFBEDDB97AF5AAEB51D3BB6A33CEE1D45409A006B554434F6A94A452002C48C905FA795AB56AE50ABC9F4541EA6E41610388B16B6CBA87C58D51903A01001665B008A6777249A5004CD8AB210A522D0050AF86264D9A50AA3E522F00A35E0D5898A20B2B808761C386B9C4D8B06103E5C483152080D7AF5FBB84E8D7AF1FE5444B4509F0F6EDDB50C3837EA89B79C0D09B1A251523801A24B6366DDA506E7830F4A8961D15152700A34E2BB97FFF3EA58667F7EEDDB2DC8B172F526AE9549C00EAAEB4EA1606C5EEFA980F2E376C8F69C508003828DE3E7D4E8FF2F1F2E9D3A7B2DC3054940080FF64204C5D25AFF6F54701B6F54459786C0D4341DE60A3D52B57AE38E7CE9D133390555BB66C99B00913268856A5BABADE6B989F8963E6CF9F2F3E8BED7DE3DAC4B569D3A6AEBA870E1DEAECDDBB57BE0F33A570DDBA75B29CB00496C01524C1302072E4C811B1CCB4581E3C78E05B26ECFCF9F374547E6EDEBCE9346EDCD8F5F928082C65EBD6ADAEF9347E8691A438D8B66D9B5887ECB7B6CCCF304D1D0DA74240D0EBD7AFEF5B4E21862BFCD6AD5B545A784A92B179F3E6598E21603A993E7DBA53AF5EBD2C3F541B356A94187EF4E3D2A54BF238B079F36667C89021E2D685F60366C91D3B764CE4C54924D791F79BDAB56B57CAD10FA698E7FA8663250D6F068B47564E3745A4357B5B8BB0A3478F52AE59FAF7EF9FE59BD74C105BAD985FAF9E1C56AA2409EFF6976CBA47C862973DAE3E94A8517D647BF3E60DE5C687B688ECD8B1439E585261FF305F885FC3E29C2DA7351ADE55894943DDF08F517762849D3C799272A2417B24B0021E2782AD02920866CC71B055F0ECCFE9B0D1A347534E38B40B70F6EC59DF134C12EA6A7D3F380F86394261D01E0576FCF6EDDB94924CA64D9B267D0D82F361E3C78FA7D4E2D02A003B8BFD9ECB819D3B774A9F733D9EAA0D51745A1643EC02604C55ED21454F6839A16EF017B4092C508F83154A6C02600EA5EA1096FB9733BCB9392CD703C4A44993E4712B56ACA0D4606213007F12054E24E94F5345815F0F6D870E1D9C6BD7AED11155701E3A0C73A1F537A092402FAB77757D9005E338FF016D65B29567DB873A0000000049454E44AE426082

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    7,309

    Re: varbinary image not being created

    Have you tried using a stream instead - something like:

    Please Login or Register  to view this content.
    Rory

  3. #3
    Registered User
    Join Date
    12-13-2019
    Location
    Essex
    MS-Off Ver
    365
    Posts
    3

    Re: varbinary image not being created

    I did try a similar method but it throws a 3001 error. I tried yours and the same.

    Thanks for looking

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    7,309

    Re: varbinary image not being created

    Did you mean to use:

    convert(varchar(max)

    rather than:

    convert(varbinary(max)

  5. #5
    Registered User
    Join Date
    12-13-2019
    Location
    Essex
    MS-Off Ver
    365
    Posts
    3

    Re: varbinary image not being created

    my god it worked thanks!!

    I think I got led into a complete maze of red herrings and the the solution was always much simpler.

    Again thanks!

  6. #6
    Registered User
    Join Date
    06-25-2021
    Location
    Seattle, WA
    MS-Off Ver
    2019
    Posts
    1

    Re: varbinary image not being created

    Hello Guys, I am fairly new to VBA programming please help me on how to display the images in my excel.
    here are the steps I followed.

    1) I have a table with .jpeg storing in Varbinary (Max) column in a sql server.
    2) With Power query its showing all columns from that table except Varbinary (Max) column which has image some like ( "0xFFD8FFE000104A4649460...... " long text)
    3) Since it did not worked with PowerQuery I wrote simple VBA script by opening database connection and read the records and close the connection, but I still have the same issue where I can't see the Varbinary (Max) column.

    I am using below code to retrieve the data from excel sheet:

    Set wsReport = ThisWorkbook.Worksheets.Add
    With wsReport
    For col = 0 To rst.Fields.Count - 1
    .Cells(1, col + 1).Value = rst.Fields(col).Name
    Next col
    .Range("A2").CopyFromRecordset Data:=rst
    End With

    please help me how do I need to convert that Varbinary column in VBA script?

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,896

    Re: varbinary image not being created

    Hi.

    It's a forum rule that you start your own thread, rather than "piggy-backing" on someone else's thread. This helps prevent massive confusion arising - which will happen if it is not clear which question is being answered. So please start your own thread and explain your own problem (make sure that, right from the start, you use a meaningful title (NOT things like "help needed" or "urgent problem"... think of the Google search terms that you would use to find the solution).

    A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures. So, prepare a SMALL sample sheet (10-20 rows, not thousands!!!). Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!


    The yellow banner about sample worksheets, at the top of the screen tells you how to post a sheet here on the Forum.

    I am closing this thread, NOW.
    Glenn



Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 07-14-2016, 04:59 AM
  2. [SOLVED] How to apply Image borders to an image that my excel vba userform pastes in a word doc?
    By CaptainCool in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-11-2014, 05:40 PM
  3. [SOLVED] Wait for Workbook to be created from outside program and then continue once created
    By ggilzow in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-26-2013, 02:41 PM
  4. Replies: 2
    Last Post: 03-26-2013, 08:06 AM
  5. excel 2010 VBA InsertPicInRange only makes shortcut to image instead of copy of image
    By ArjanSpit in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-10-2012, 02:57 PM
  6. Click events on buttons created at runtime only work on the last button created
    By Treacle in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-25-2010, 05:44 AM
  7. Created bell curve, do not understand the curves i created
    By Phokus in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 05-22-2008, 09:16 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1