How to insert an image

Question

How to insert an image

yosoy

SSC Enthusiast

Points: 123
More actions
May 13, 2003 at 7:09 am

#159946

Hi there
I am developing an applicatrion in which I need to insert images into a table in my sql database and I can't find a way to do it. Please help me out.
Best Regards
yo soy

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi,

all you need is an image data field in your table and, case you're doing this with a vb derivat some experience with the ADO Stram Object. something like this

Dim strStream As ADODB.Stream

Set strStream = New ADODB.Stream

strStream.Type = adTypeBinary

strStream.Open

strStream.LoadFromFile sFileName

DoEvents

.Fields("MsgAttachment").Value = strStream.Read

This will save binary data to your db.

...

Set strStream = New ADODB.Stream

strStream.Type = adTypeBinary

strStream.Open

strStream.Write rs.Fields("MsgAttachment").Value

strStream.SaveToFile CommonDialog1.FileName, adSaveCreateOverWrite

This reads the stream and writes back to file.

HTH

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Dave Mitchell SSC Rookie Points: 38 More actions · Answer 2

Hi Frank,

I am trying to do something similar, i.e. trying to store a word document as a binary file in a SQL Server database, (using VB6 and SQL Server 2000).

The steps I have taken are these, perhaps you could point out wheere I might be going wrong.

Step 1. Populate a Stream object from a file dialog box

Set objStream = New ADODB.Stream

objStream.Type = adTypeBinary

objStream.Open

objStream.LoadFromFile CommonDialog1.fileName

Step 2. Insert the stream into SQL Server

ReDim varArr(1 To 3)

varArr(1) = "test"

varArr(2) = "title"

varArr(3) = objStream.Read

Set objDataMgt = New clsDataManagement

Set rs = objDataMgt.Add(uspADDCAF_WORDTEMPLATE_INS, varArr())

objStream.Close

---------------------------------------------------

In my table the datatype for the "blob column" is Image (16)

I use ADO 2.8 to connect to SQL Server, and have tried using all of the following DataTypeEnum (205, 204, 128, 12)

However I keep getting the following error:-

"Application uses a value of the wrong type for the current operation."

Any help would be vastly appreciated.

Dave Mitchell

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

This is the by far most complete link to BLOBs and SQL Server, that I know of: http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part3/c1161.mspx

As the above post is from the old forum software, here's what I use

Dim strStream As ADODB.Stream

Set strStream = New ADODB.Stream

strStream.Type = adTypeBinary

strStream.Open

strStream.LoadFromFile sFileName

DoEvents

Fields("MsgAttachment").Value = strStream.Read

....

Neither can I see what happening inside your class, nor have I come across this error message yet. Hope someone else will jump here right in

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Dave Mitchell SSC Rookie Points: 38 More actions · Answer 4

Hi Frank,

Thanks for the tip, but I'm still getting my error:

"Application uses a value of the wrong type for the current operation."

--------------------------------------------------------------------------

I think my problem arises, because the database connection class I'm using takes in a stored procedure with parameters in the following format:-

"uspADDCAF_WORDTEMPLATE_INS;T:4|r;P:I|200|255;I|200|255;I|128|8000;"

The parameters come after the "P:" and are defined by the DataTypeEnum constants in ADO 2.8 API Reference

e.g.

I|200|255; = adVarChar(255)

I|128|8000; = adBinary(8000)

I can't find a datatype to match the "Image" datatype I have as my "BLOB column" (See link below for full list). Do you know which of these constants I should be using, or can't I do this operation with my current DB connection class.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdcstdatatypeenum.asp

Thanks so far for your advice,

Best regards,

Dave

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Sorry, I'm not that much into VB anymore, but after reading your link, have you tried adVarLongBinary? But I think there are MS hosted newsgroups about VB and databases. Maybe worth a try to find a workaround if you don't want to change your app logic.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Dave Mitchell SSC Rookie Points: 38 More actions · Answer 6

I have now managed to achieve this, so thought I'd post my solution for any other poor souls who are attempting the same thing.

The following VB6 code will save the BLOB object into SQL Server.

Private Sub cmdBlobSave_Click()

Dim objStream As ADODB.Stream

CommonDialog1.DialogTitle = "Export Addresses to..."

CommonDialog1.flags = cdlOFNExplorer 'cdlOFNAllowMultiselect +

CommonDialog1.CancelError = True

CommonDialog1.ShowSave

Set objStream = New ADODB.Stream

objStream.Type = adTypeBinary

objStream.Open

objStream.LoadFromFile CommonDialog1.FileName

ReDim varArr(1 To 3)

varArr(1) = CommonDialog1.FileName

varArr(2) = txtTemplateName.Text

varArr(3) = objStream.Read

Set objDataMgt = New clsDataManagement

Set rs = objDataMgt.Add("uspADDCAF_WORDTEMPLATE_INS;T:4|r;P:I|200|255;I|200|255;I|205|" & (objStream.Size + 2000) & ";", varArr())

'rs.Close

objStream.Close

Set rs = Nothing

Set objDataMgt = Nothing

End Sub

Dave Mitchell SSC Rookie Points: 38 More actions · Answer 7

If you are writing out large files, such as TIFs or Word cocuments, you may also need to modify your database connection class to use the "AppendChunk" method.

These are the changes I made to mine:-

Dim nChunkSize As Integer

Dim nRem As Integer

Dim objStream As New ADODB.Stream

'Test whether it is a binary datatype

If cmd.Parameters(intCmdParam).Type <> adLongVarBinary Then

cmd.Parameters(intCmdParam).Value = vntParams(intInputParam)

Else

objStream.Type = adTypeBinary

objStream.Open

objStream.Write vntParams(intInputParam)

nChunkSize = objStream.Size / 4096

nRem = objStream.Size Mod 4096

objStream.Position = 0

Do Until objStream.Position >= objStream.Size - nRem

cmd.Parameters(intCmdParam).AppendChunk objStream.Read(4096)

Loop

If nRem > 0 Then

cmd.Parameters(intCmdParam).AppendChunk objStream.Read(nRem)

End If

objStream.Close

End if

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

Glad to see it works.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

How to insert an image

Cookies on SQLServerCentral