Save an image to SQL Server

Is it possible to save an image file to a sql server table containing a field of image type? The following code is not working (gives a type mismatch error):


'---------------------------------------------------------------------------------------
'--OPEN RECORDSET
'---------------------------------------------------------------------------------------
strSQL = "SELECT * FROM signature_images"
dsResult.Open strSQL, dbConn

'---------------------------------------------------------------------------------------
'--GET TEMPORARY DIRECTORY
'---------------------------------------------------------------------------------------
Dim tempDir
Dim tempFile
Dim imgSize
      
tempDir = FSO.GetSpecialFolder(2).Path & "\"

'---------------------------------------------------------------------------------------
'--SAVE BINARY DATA TO BMP IN TEMPORARY DIRECTORY
'---------------------------------------------------------------------------------------
Dim oStream
      
set oStream = createobject("Adodb.Stream")
Const adTypeBinary = 1
Const adSaveCreateOverWrite = 2
Const adSaveCreateNotExist = 1
            
oStream.type = adTypeBinary
oStream.open
oStream.write dsResult(2)
            
tempFile = tempDir & dsResult(0) & ".bmp"
' Overwrite an existing file
oStream.savetofile tempFile, adSaveCreateOverWrite

'--------------------------------------------------------------------------------------
'--SAVE FILE TO DATABASE
'-------------------------------------------------------------------------------------
Dim objFile
      
Set objFile = FSO.GetFile(tempFile)
imgSize = objFile.Size
Set objFile = Nothing

strSQL = "INSERT INTO common_Images_t (image_type, key_id, image_format, image_size, image_data, image_mod_date) "_
      & "VALUES (2," & dsResult(0) & ", 1, " & imgSize & ", " & oStream.Read & ", '" & dsResult(1) & "')"

dbConn2005.Execute strSQL
oStream.close
set oStream = nothing
      
core1966Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

WMIFCommented:
is there a specific reason you need to write the image inside the db?  generally it is not a good idea because its a pain and it bloats the db.  if you can, you should save the image in another directory, and store the location of that image in your db.
core1966Author Commented:
Yes, I know about the bloat and all the reasons not to...  There are some that you cannot get to see the light....  I am forced to write the data to the db.  Basically, I am migrating data from an earlier app to a new version.
WMIFCommented:
from MS BOL:
Columns of image data can be used to store variable-length binary data exceeding 8 KB, such as Microsoft Word documents, Microsoft Excel spreadsheets, and images that include bitmaps, Graphics Interchange Format (GIF), and Joint Photographic Experts Group (JPEG) files.


so there should be no problem using the image type to store binary data.  what line are you getting the error on?
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

core1966Author Commented:
strSQL = "INSERT INTO common_Images_t (image_type, key_id, image_format, image_size, image_data, image_mod_date) "_
      & "VALUES (2," & dsResult(0) & ", 1, " & imgSize & ", " & oStream.Read & ", '" & dsResult(1) & "')"

It is the "oStream.Read"
WMIFCommented:
im thinking that you might have to get away from the query format of an insert, and use the ado objects.  check out the answer in this quesiton: http:Q_20101551.html

note this line:
objRst("logo") = objStm.Read
core1966Author Commented:
I tried that route already and got the error "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. " on the Opportunistic Locking
WMIFCommented:
do you still have the code from your attempt with that method?  ive been looking for examples that would use the query method that you are trying above, but i cannot find any.
core1966Author Commented:
Here is the code with the failed update from using ADO objects:

     dsTemp.Open "common_image_types_t", dbConn2005, adOpenKeyset,adLockOptimistic, adCmdTable
     dsTemp.AddNew
           dsTemp(0) = 2
           dsTemp(1) = dsResult(0)
           dsTemp(2) = 1
           dsTemp(3) = imgSize
           dsTemp(4) = oStream.Read
           dsTemp(5) = dsResult(1)
     dsTemp.Update
     
WMIFCommented:
lets try it like this and see what happens.


query = "select image_type, key_id, image_format, image_size, image_data, image_mod_date from common_Images_t where 0 = 1"
     dsTemp.Open query, dbConn2005, adOpenKeyset,adLockOptimistic, adCmdTable
     dsTemp.AddNew
          dsTemp("image_type") = 2
          dsTemp("key_id") = dsResult(0)
          dsTemp("image_format") = 1
          dsTemp("image_size") = imgSize
          dsTemp("image_data") = oStream.Read
          dsTemp("image_mod_date") = dsResult(1)
     dsTemp.Update
core1966Author Commented:
LOL - Exactly what I got when I removed opportunistic locking:

Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

BTW - This is a SQL Server 2005 Database.
WMIFCommented:
hmm, this is getting over my head then.  you should drop a pointer question in the SQL topic area.
Anthony PerkinsCommented:
This:
dsTemp.Open query, dbConn2005, adOpenKeyset,adLockOptimistic, adCmdTable

Should be:
dsTemp.Open query, dbConn2005, adOpenKeyset, adLockOptimistic, adCmdText
Anthony PerkinsCommented:
Try it this way (assuming your Recordset is open and called rs):

Dim stm

Set stm = Server.CreateObject("ADODB.Stream")
With stm
  .Type = adTypeBinary
  .Open
  .LoadFromFile "Your filename goes here"
   rs.AddNew
  rs.Fields("image_type").Value = 2
  rs.Fields("key_id").Value = dsResult(0)
  rs.Fields("image_format").Value = 1
  rs.Fields("image_size").Value = imgSize
  rs.Fields("image_data").Value = .Read
  rs.Fields("image_mod_date").Value = dsResult(1)
  rs.Update
  .Close
End With
Set stm = Nothing

End Sub
core1966Author Commented:
Nope -- still getting "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. "  on the opportunistic locking...
Anthony PerkinsCommented:
Than you must be doing something wrong.  Without seeing your code, it is impossible to go further.
Anthony PerkinsCommented:
It seems your problem has nothing to do with saving images and everything to do with opening the table.  So I would get that fixed first.

Instead of:
dsTemp.Open query, dbConn2005, adOpenKeyset, adLockOptimistic, adCmdText

Try:
dsTemp.Open query, dbConn2005, adOpenDynamic, adLockOptimistic, adCmdText

And make sure you have declared the ADO constants so that adOpenDynamic, adLockOptimistic, adCmdText actually mean something.  Or at the very least include Option Explciit, so that you know the difference.


PS It is not "opportunistic locking" but rather "optimistic locking" :)
core1966Author Commented:
*PS - Doh!  You are so right!  Trying to do too many things at once...  All my code is included at the top of this thread -- all that is missing is the connection string so here it is:

Dim dbConn2005

Set dbConn2005 = Server.CreateObject("ADODB.Connection")
dbConn2005.Open "PROVIDER=SQLNCLI;Data Source=SERVERNAME;Initial Catalog=TABLENAME;DataTypeCompatibility=80;MARS Connection=True;UID=USERID;PWD=PWORD;"
Anthony PerkinsCommented:
This is how you do it (I am assuming you have declared all the ADO constants):

1. Create a function like this:

Sub AddImage(rs, ByVal FileName)
Dim stm

Set stm = Server.CreateObject("ADODB.Stream")
With stm
  .Type = adTypeBinary
  .Open
  .LoadFromFile FileName
 
  'Insert the binary object into the table.
  rs.AddNew
  rs.Fields("ImageColName").Value = .Read
  rs.Update
  .Close
End With
Set stm = Nothing

End Sub

2. Call it as follows:
AddImage rs, "Your File Name goes here"

I tested it successfully as follows:

Dim cn, rs

Set cn = Server.CreateObject("ADODB.Connection")
cn.Open MY_CONN
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "Select ImageCol From Test Where 1=0", cn, adOpenDynamic, adLockOptimistic
AddImage rs, Server.MapPath("C:\temp\Temp.pdf")               ' I used a PDF, but it could have been any file.
rs.Close
Set rs = Nothing

cn.Close
Set cn = Nothing

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.