how to add the file name for images in database

Question

3.00/5 (1 vote)

See more:

I have sql code to insert images from my system to the database. In this code the file name is not printing. How can I print the file name?

SQL

CREATE TABLE [dbo].[Images](
     [id] [int] IDENTITY(1,1) NOT NULL,
     [Filename] varchar(100),

    [Path] varchar(200),

    [document] [image] NULL    )


DECLARE @imgString varchar(80), @imgNumber int
DECLARE @insertString varchar(3000)

SET @imgNumber = 13
WHILE (@imgNumber < 19)
BEGIN

    SET @imgString = 'D:\images\43-' + CONVERT(varchar,@imgNumber) + '.jpg'
    SET @insertString = N'INSERT INTO Images(document)
                        SELECT * FROM OPENROWSET(BULK N''' + @imgString + ''', SINGLE_BLOB) as tempImg'


    EXEC(@insertString)
    SET @imgNumber = @imgNumber + 1
 EXEC(@insertString)
END
select * from images

also for images that are not present in system it shows error

Posted 11-Jan-15 20:40pm

Member 11357862

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

manvendra patel · Answer 1 · 2015-01-11T20:48:00

Solution 1

Try this link below.

http://www.aspsnippets.com/Articles/Retrieve-images-using-a-file-path-stored-in-database-in-ASPNet.aspx[^]

http://www.aspdotnet-suresh.com/2011/03/how-to-save-images-into-folder-and.html[^]

Posted 11-Jan-15 20:48pm

manvendra patel

Wendelius · Answer 2 · 2015-01-11T21:18:00

Solution 2

In your INSERT statement you're only defining the document field. Because of this all other fields are left empty so they get their default values.

Try changing your insert to something like

SQL

SET @insertString = N'INSERT INTO Images(filename, path, document)...

Just make sure that you provide corresponding values in the SELECT portion

Posted 11-Jan-15 21:18pm

Wendelius

Comments

Member 11357862 12-Jan-15 3:35am

could u pls explain

Wendelius 12-Jan-15 3:52am

The insert statement requires you to provide the values for specified columns. In your current insert statement you define value only to the image column and because of this the filename and path columns are left empty.

In order to define the value for filename in the insert statement you must define the column in the INSERT clause and also provide a value in the SELECT clause.

For information about the INSERT syntax, refer to INSERT (Transact-SQL)[^]

Er. Ajay Chauhan · Answer 3 · 2015-01-11T22:04:00

Solution 3

don't use Image Data-type in your apps.
before using data types is SQL Server, read this on -> Click Here

Posted 11-Jan-15 22:04pm

Er. Ajay Chauhan