How to insert a image

Question

How to insert a image

sangeetha-454661

Grasshopper

Points: 17
More actions
September 3, 2007 at 5:52 am

#180755

I am new to SQL Server. Will anyone guide me for inserting a image in a table. Plz reply as soon as possible. Thanks in advance...

Viewing 7 posts - 1 through 6 (of 6 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

See the topic Adding ntext, text, or image Data to Inserted Rows in Books Online. Please post again if there's anything specific you don't understand.

John

sangeetha-454661 Grasshopper Points: 17 More actions · Answer 2

Will u explain with example . I can't get any idea from books online. am new to this concept.

thanks john in advance...

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 3

In SQL Server 2005 you can use OPENROWSET(Bulk as:

INSERT INTO mytable
        ( somecolumn1
        , imagefromfile
        )
        SELECT  1
              , BulkColumn
        FROM    OPENROWSET(BULK 'C:\mypicture.jpg', Single_Blob) AS img

About openrowset you can read: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/f47eda43-33aa-454d-840a-bb15a031ca17.htm

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Venkatesan Prabu SSCommitted Points: 1892 More actions · Answer 4

Hi Sangeetha,

Please give us a clear picture about your problem. so that, we can provide an apt solution for your problem. Am assuming that you are trying to insert an image from the front end and it should be stored in the back end.

Create a table with column as "Image" type,

For ex: Create table ImageTable(Imagecolumn Image)

In the front end,

1) Get a File upload control, and read the image using an input stream.

int imagelen = Upload.PostedFile.ContentLength;

byte[] picbyte = new byte[imagelen];

Upload.PostedFile.InputStream.Read(picbyte, 0, imagelen);

2) Connect to the database and execute your sql command to bind the parameters and execute your command using ExecuteNonQuery.

3) Close your connection.

Closing my article assuming that I have satisfied your request. If you need the exact code do mail me to venkatesanj@hcl.in. Please give your feedback too.

Regards,

Venkatesan Prabu,

HCL Technologies

Thanks and Regards,
Venkatesan Prabu, 😛
My Blog:

http://venkattechnicalblog.blogspot.com/

Michelle Woodard Newbie Points: 9 More actions · Answer 5

I am trying to use the method Andras explained to create a stored procedure. The code works perfectly when I run it as a query but when I try to create the procedure I'm getting a syntax error at the variable name used for the datafile parameter. Any suggestions??

Michelle

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 6

Hi Michelle,

OPENROWSET accepts only string literals as the filepath, so you would need to construct this query dynamically, something like:

CREATE PROC insertBulk
    ( @filename NVARCHAR(260)
    )
AS 
    BEGIN
        DECLARE @query NVARCHAR(500)
        SET @query = N'INSERT INTO mytable
        ( somecolumn1
        , imagefromfile
        )
        SELECT  1
              , BulkColumn
        FROM    OPENROWSET(BULK ''' + @filename + ''', Single_Blob) AS img'
        EXEC ( @query )

    END

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software