inserting images to the correct id

vishnu suresh

11y1.5k.NET

In my program I want to insert the images of students which are in a folder to the sql table according to id. Currently I used this querry.

declare EmployeCursor cursor fast_forward for

select num

from login

declare @sql nvarchar(4000)

declare @num nvarchar(50)

open EmployeCursor

while (1=1) begin

fetch next from EmployeCursor into @num

if @@FETCH_STATUS<>0 break

set @sql = N'UPDATE login

SET image =

(SELECT * FROM

OPENROWSET(BULK N''D:\img\' + cast(@num as nvarchar(50)) + N'.jpg'', SINGLE_BLOB) AS img)

WHERE num = ' + cast(@num as nvarchar(max))

exec(@sql)

end /* while */

close EmployeCursor

deallocate EmployeCursor

But its have problem.My image names are in the format 12-1.jpg and id in the format 12/1. so how can I add images? pls help

13 Replies

Know the answer? Post it — somebody with the same question will find it here.

vishnu suresh11y ago
sir is there any way I can update the remaining coloumns
0
Khargesh Rajput11y ago
you cant convert 111-1 to int because it contains delimeter - character.
and 30 rows are updated becauses you may update with where cluase.
0
vishnu suresh11y ago
I have used the above code, this code have no convertion error. but it only insert 30 images, remaining not. Could u pls check this code
0
vishnu suresh11y ago
DECLARE @id varchar
DECLARE image_cursors CURSOR FOR
SELECT CODE FROM MyMast WHERE img IS NULL
OPEN image_cursors;
FETCH NEXT FROM image_cursors
INTO @id;
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @sql VARCHAR(MAX)
DECLARE @imagePath VARCHAR(255)
SET @imagePath = 'D:\images' + RTRIM(LTRIM(@id)) + '.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 id = ''' + @id + ''';'
BEGIN TRY
EXECUTE sp_executesql @sql
END TRY
BEGIN CATCH
END CATCH
FETCH NEXT FROM image_cursors
INTO @id;
END
CLOSE image_cursors;
DEALLOCATE image_cursors;
SELECT id, img FROM MyMast WHERE img IS NOT NULL

0
vishnu suresh11y ago
great help sir, thank u, sir I hv changed the code to 111-1 format and used above code to add all the details. but it only updates around 22 items only, remaining cannot. I have a large collection of images and id. Is my code above give have any pblm? pls help. It shows error when converting the 111-1 to int
0
Khargesh Rajput11y ago
update tablename set stu_id=cast(replace(cast(stu_id as nvarchar(max)),'/','-') as ntext)
0
vishnu suresh11y ago
sorry sir, I have a lot of collection of stud id, so it cannot be changed. is there any other coding available to insert 111-1.jpg to 111/1 id place,
eg: code:
replacing / with - for id
0
Khargesh Rajput11y ago
when you are inserting student id then you use format of string as 111/1
change it to 111-1
0
vishnu suresh11y ago
the students id are like 111/1,111/2 etc and the corresponding image names are 111-1.jpg,111-2.jpg etc.
0
vishnu suresh11y ago
sir could u pls explain it
0
Khargesh Rajput11y ago
insert stud like 110-1 format as for image name
0
vishnu suresh11y ago
my file name is like 110-1.jpg and id of stud like 110/1.
0
Khargesh Rajput11y ago
File name not accept characters /\:*?<> when we save any file name or folder.
e.g. save any file with name abc/.jpg not accept
0