Click here to Skip to main content
15,895,709 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have web application in asp.net in which it contain product codes and details. The product details are in sql table. Now I have the images of the products in my system folder. I need to add the images directly to the sql table according to the each code. The code names are like 110-1, 110-2 etc and image names are like 110-1.jpg,110-2.jpg. I have tried sql commands to insert all the images, but it fails. Is there any code available in c# asp.net to update the images to the code. pls help

[Code from OP's comment]
SQL
DECLARE @CODE nvarchar(50)
DECLARE image_cursor CURSOR FOR 
SELECT CODE FROM MyMast WHERE img IS NULL
OPEN image_cursor;
FETCH NEXT FROM image_cursor 
INTO @CODE;
WHILE @@FETCH_STATUS = 1

BEGIN
    DECLARE @sql NVARCHAR(MAX)
    DECLARE @imagePath NVARCHAR(255)
	SET @imagePath = 'D:\images\'+ RTRIM(LTRIM('@CODE')) + '.jpg'
    SET @sql = 'UPDATE MyMast '
    SET @sql = @sql + 'SET img = (SELECT BulkColumn FROM OPENROWSET( BULK ''' + @imagePath + ''', Single_Blob) AS img) '

SET @sql = @sql + 'WHERE CODE = ''' + (@CODE) +''';';
    BEGIN TRY
        EXECUTE sp_executesql @sql 
    END TRY
    BEGIN CATCH

    END CATCH   

    FETCH NEXT FROM image_cursor 
    INTO @CODE;
END
CLOSE image_cursor;
DEALLOCATE image_cursor;

SELECT CODE, img FROM MyMast WHERE img IS NOT NULL
Posted
Updated 11-Jan-15 3:08am
v2
Comments
OriginalGriff 11-Jan-15 5:18am    
What have you tried?
Show us the relevant code fragments.

Use the "Improve question" widget to edit your question and provide better information.
Kornfeld Eliyahu Peter 11-Jan-15 5:19am    
Can you show us your SQL and comment on the error/failure?
Member 11357862 11-Jan-15 9:05am    
DECLARE @CODE nvarchar(50)
DECLARE image_cursor CURSOR FOR
SELECT CODE FROM MyMast WHERE img IS NULL
OPEN image_cursor;
FETCH NEXT FROM image_cursor
INTO @CODE;
WHILE @@FETCH_STATUS = 1

BEGIN
DECLARE @sql NVARCHAR(MAX)
DECLARE @imagePath NVARCHAR(255)
SET @imagePath = 'D:\images\'+ RTRIM(LTRIM('@CODE')) + '.jpg'
SET @sql = 'UPDATE MyMast '
SET @sql = @sql + 'SET img = (SELECT BulkColumn FROM OPENROWSET( BULK ''' + @imagePath + ''', Single_Blob) AS img) '

SET @sql = @sql + 'WHERE CODE = ''' + (@CODE) +''';';
BEGIN TRY
EXECUTE sp_executesql @sql
END TRY
BEGIN CATCH

END CATCH

FETCH NEXT FROM image_cursor
INTO @CODE;
END
CLOSE image_cursor;
DEALLOCATE image_cursor;

SELECT CODE, img FROM MyMast WHERE img IS NOT NULL

Kornfeld Eliyahu Peter 11-Jan-15 9:11am    
It is unclear what are you trying to put in to img column...
1. The path of the image?
2. The binary content of the image file as BLOB?
Member 11357862 11-Jan-15 9:18am    
img is my image column in database. in this column I want to insert the images. the path of image is this 'D:\images.

"The drive D: is a drive on the SQL server?"

"no, its on my system"


So how do you expect SQL server to (a) know that; and (b) access a hard drive on a different computer without establishing a share? Which is a bad idea...

Do it in C#: read the file data, send it to SQL as a parameterised INSERT or UPDATE command. The code to do that is simple, and is covered here: Why do I get a "Parameter is not valid." exception when I read an image from my database?[^]
 
Share this answer
 
Comments
Member 11357862 11-Jan-15 10:21am    
No, my current coding is updating, but only for 22 rows remaining not.
According to OP's comment...

Notice that SQL server suns your command on the server, so any reference to storage (like D: in your case) is relative to the server itself!
In your case SQL server can not find the path you provide as you created it in the context of your PC but SQL interpreted it in its own context...
You can do one of the two:
1. Share your path and let SQL access it (it can be goo if this is a one-time-only data manipulation)
2. Insert the BLOB from code
 
Share this answer
 
Comments
Member 11357862 11-Jan-15 10:21am    
No, my current coding is updating, but only for 22 rows remaining not.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900