problem in inserting multiple images to the database.

Question

0.00/5 (No votes)

See more:

I have a database in which contain product details The product code, name etc are the columns, I have the product images in the system in a folder I need to add the images directly to the database according to the proper rows regarding the product code, The product code and images are sam, the product code is like 110-1,110-2 etc, and image names are 110-1jpg,110-2jpg etc I have a program but its not updating all the rows pls help

SQL

DECLARE @CODE varchar

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 = 0

BEGIN

DECLARE @sql VARCHAR(MAX)

DECLARE @imagePath VARCHAR(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 Picture), SET PictureFileName = ' + @imagepath

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 6-Jan-15 21:29pm

Member 11357862

Updated 6-Jan-15 21:56pm

Tomas Takac

v2

Add a Solution

1 solution

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)

jaket-cp · Answer 1 · 2015-01-07T01:55:00

Solution 1

A few minor errors need to be addressed.

SQL

--length of variable required
DECLARE @CODE varchar(20);

SQL

--string concatenation issues
--also an addition SET to be removed from SET PictureFileName
--the initial @imagePath has single quotes surrounding it on the generated sql but the second one does not
SET @sql = 'UPDATE Mymast'
SET @sql = @sql + 'SET img = (SELECT BulkColumn FROM OPENROWSET( BULK ''' + @imagePath + ''', Single_Blob) AS Picture), SET PictureFileName = ' + @imagepath
SET @sql = @sql + 'WHERE CODE = ''' + @CODE + ''';'
--on the concatenation side the above will produce UPDATE MymastSET and the WHERE will be appended to generated @imagepath

SQL

--the @sql needs to be of nvarchar for the statement to be executed using sp_executesql
EXECUTE sp_executesql @sql
--or use
EXEC (@sql);

sp_executesql (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms188001.aspx[^]

I believe all the issues have been pointed out.
Consider using an alternative to a cursor as they are considered bad practice:
http://stackoverflow.com/questions/58141/why-is-it-considered-bad-practice-to-use-cursors-in-sql-server[^]

Also possibly get in the habit of using the semicolon statement terminator:
Features Not Supported in a Future Version of SQL Server[^]

SQL

--here are the changes as specified above
--only slight modification of your original code
DECLARE @CODE varchar(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 = 0 BEGIN
			DECLARE @sql VARCHAR(MAX);
			DECLARE @imagePath VARCHAR(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 Picture),  PictureFileName = ''' + @imagepath + ''' ';
			SET @sql = @sql + 'WHERE CODE = ''' + @CODE + ''';';
			BEGIN TRY
--				EXECUTE sp_executesql @sql
				EXEC (@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;
--if it does not work, ensure all images in CODE column are present in D:\images\ folder.

Posted 7-Jan-15 1:55am

jaket-cp

Updated 7-Jan-15 5:23am

v2

Comments

Member 11357862 7-Jan-15 9:41am

sir till now my issue is not reolved

jaket-cp 7-Jan-15 9:51am

Okay so you have implemented the varchar(20) - that is good.
Did you implement the other points?

Member 11357862 7-Jan-15 9:54am

ya I have removed the set before the picture file name. I didn't get the details abt string concatenation

jaket-cp 7-Jan-15 10:00am

okay that is good.
The string concatenation, means all the string values joined together to make a longer string.
In this case the query which is stored in @sql.
Note when @sql is generated the result on a portion of that string will be MymastSET this is not good as no space between Mymast and SET, so this needs to be fixed by adding a space, also add spaces before the Where.

Member 11357862 7-Jan-15 10:24am

ya I hv given space, but it doesn't resolved issue, can u pls give me any other code for inserting multiple images?

jaket-cp 7-Jan-15 10:31am

okay, did you put single quote around @imagepath?
And did you change @sql type to nvarchar when using sp_executesql?
That is the fun of coding, when it works - so persist and you will triumph :)

Member 11357862 7-Jan-15 10:47am

I have changed @sql nvarchar(max) and the code to SET @sql = @sql + 'SET img = (SELECT BulkColumn FROM OPENROWSET( BULK ''' +' @imagePath' + ''', Single_Blob) AS Picture),PictureFileName = ' + '@imagepath' but same pblm

jaket-cp 7-Jan-15 10:54am

try this:

SET @sql = @sql + 'SET img = (SELECT BulkColumn FROM OPENROWSET( BULK ''' + @imagePath + ''', Single_Blob) AS Picture),  PictureFileName = ''' + @imagepath + ''' ';

To see what the generated @sql is, do print @sql; in place of EXECUTE sp_executesql @sql.

Member 11357862 7-Jan-15 11:02am

sir I have done this query correction, but its not updating the remaining coloumns.

Member 11357862 7-Jan-15 11:03am

DECLARE @CODE varchar(20)
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 = 0
BEGIN
DECLARE @sql NVARCHAR(MAX)

DECLARE @imagePath VARCHAR(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 Picture), PictureFileName = ''' + @imagepath + ''' ';
SET @sql = @sql + 'WHERE CODE = ''' + @CODE + ''';'
BEGIN TRY
PRINT @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

jaket-cp 7-Jan-15 11:10am

okay did you look at the generated print?
you need to add space between MyMast and SET, like so:
SET @sql = 'UPDATE MyMast '
After that change back the print to the Execute.

Member 11357862 7-Jan-15 11:22am

ya done, I think the problem is in any other parts, still hv same issue, is there any code available to insert this image in asp.net using c#?

jaket-cp 7-Jan-15 11:28am

okay.
I have updated the solution so you can compare.
It should work, I have tested it on my end, mind I do not know what is being stored in the img column apart from it is not null and a binary object.

Also ensure the images in CODE column also present in D:\images\ folder?

Member 11357862 7-Jan-15 11:33am

sir, thank u for ur great help, but still it is updating only 22 rows.I my D:\images folder there is images in the name 110-1.jpg,110-2.jpg etc. In my sql table there is the product code 110-2,110-1 etc.

jaket-cp 7-Jan-15 11:57am

not a problem, just a bit stumped why it is not working..

I suggest, revert back to the PRINT version and get the generated PRINT @sql and test each one individually to see what the issue could be.
Apart from that, nothing else comes to mind at the moment.

Member 11357862 13-Jan-15 9:11am

sir I checked my whole pgm, I think that it is due to the convertion error. The image will insert to the code like 110,111 etc. But image will not insert into the code like 110-1,D110-1 etc. How can I resolve this

jaket-cp 13-Jan-15 10:02am

Not sure what you mean.
Your sql is updating the table MyMast, and generating the filename of where the image location should be by using the CODE value.
You should check what is in the CODE column.