Insert images in SQL Server

Question

Insert images in SQL Server

giraldolr

Grasshopper

Points: 11
More actions
July 28, 2005 at 1:23 pm

#186865

Please!!! I need to insert images in a SQL Server table but I don´t know how to do it!!!!

Viewing 5 posts - 1 through 4 (of 4 total)

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

Ray M SSC-Insane Points: 21093 More actions · Answer 1

Very difficult to explain the entire process in a forum thread, but using books online it supplies a bunch of information on how to handle suck a predicament

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_13_8y7n.asp

Eros-158633 SSC Enthusiast Points: 139 More actions · Answer 2

Can u write VB program?

If yes, then the following code would help you..

Put the Image file into DB

Public Function AddImage(rs As ADODB.Recordset, ByVal ImageFieldName As String, ByVal FileName As String) As Boolean

On Err GoTo AddImage_err

Dim stm As ADODB.Stream

AddImage = False

Set stm = New ADODB.Stream

With stm

.Type = adTypeBinary

.Open

.LoadFromFile FileName

'Insert the binary object into the table.

rs.Fields(ImageFieldName).Value = .Read

rs.Update

.Flush

.Close

End With

Set stm = Nothing

AddImage = True

Exit Function

AddImage_err:

AddImage = False

End Function

Get the Image from DB to a Specific Filename

Public Function ReadImage(rs As ADODB.Recordset, ByVal ImageFieldName As String, ByVal FileName As String) As Boolean

On Err GoTo ReadImage_err

ReadImage = False

Dim stm As ADODB.Stream

Set stm = New ADODB.Stream

With stm

.Type = adTypeBinary

.Open

.Write rs.Fields(ImageFieldName).Value

If Dir(FileName) <> "" Then Kill FileName

.SaveToFile FileName

.Close

End With

Set stm = Nothing

ReadImage = True

Exit Function

ReadImage_err:

ReadImage = False

End Function

lionfan91 SSCrazy Eights Points: 8794 More actions · Answer 3

Here's how I do it...

Table structure:

FileID uniqueidentifier

FileNM varchar(255)

SizeDM bigint

ContentTypeID varchar(100)

UploadDT smalldatetime 4 0

ContentBD image -- FYI, BD stands for binary data

ExtensionCD varchar(10) --> computed column, formula is ([dbo].[fnFileExtension]([FileNM]))

Stored procedures:

CREATE PROCEDURE dbo.procFile_ADD

@FileNM varchar(255),

@SizeDM bigint,

@ContentTypeID varchar(100),

@ContentBD image,

@FileID uniqueidentifier OUTPUT

AS

SET NOCOUNT ON

BEGIN

SET @FileID = NEWID()

INSERT INTO tblFile (FileID, FileNM, SizeDM, ContentTypeID, UploadDT, ContentBD)

VALUES (@FileID, @FileNM, @SizeDM, @ContentTypeID, GETDATE(), @ContentBD)

END

CREATE PROCEDURE dbo.procFile_GET

@FileID uniqueidentifier

AS

SET NOCOUNT ON

BEGIN

SELECT FileNM, SizeDM, ContentTypeID, UploadDT, ExtensionCD, ContentBD

FROM tblFile WHERE FileID = @FileID

END

Works from VB, ASP, etc. The biggest issue is reading the source image and getting it to binary data that can be uploaded. For ASP I use Persits AspUpload, for VB I use an ADO stream object.

Wayne West SSC-Insane Points: 22586 More actions · Answer 4

I've used Access to do it as I'm a lazy SOB when it comes to coding. I love writing T-SQL, hate writing VB. It's balky and a bit of a pain in the posterior, but it works.

Coding it in VB/Vwhatever is definitely the way to go.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]