How can I insert & update image

• shifan

  SSC Enthusiast

  Points: 153

  More actions

  April 3, 2006 at 3:01 am

  #112465

  Dear All,

  How can i insert, and update image to SQL Server Table which consist a column with image data type.

  kind regards

   

   

  
  shifan

• Hadrian

  Hall of Fame

  Points: 3742

  More actions

  April 3, 2006 at 4:09 am

  #630338

  Hi,

  You can use BII from SQL RKits 2000 , textcopy utility, or  a simple application built in .Net.

• lionfan91

  SSCrazy Eights

  Points: 8794

  More actions

  April 4, 2006 at 6:49 am

  #630640

  If you're doing this thru an application (VB, ASP, etc) you can also use stored procedures that pass image parameters called via ADO, ADO.NET, etc.

• shifan

  SSC Enthusiast

  Points: 153

  More actions

  April 4, 2006 at 9:26 pm

  #630889

  is it system supplied SP? can you send the SP?

  regards

   

   

  
  shifan

• lionfan91

  SSCrazy Eights

  Points: 8794

  More actions

  April 5, 2006 at 6:39 am

  #630954

  Here is the table structure I use:

  FileID uniqueidentifier

  FileNM varchar(255) -- just the filename, not the path info

  SizeDM bigint

  ContentTypeID varchar(100) -- MIME type

  UploadDT smalldatetime

  ContentBD image -- binary data content

  ExtensionCD varchar -- a computed column set to ([dbo].[fnFileExtension]([FileNM]))

  -- Extracts an extension from a filename.

  CREATE FUNCTION dbo.fnFileExtension ( @FileNM varchar(255) )

  RETURNS varchar(10)

  AS

  BEGIN

  DECLARE @DotPos int, @Ext varchar(10)

  SET @FileNM = REVERSE(@FileNM)

  SET @DotPos = CHARINDEX('.', @FileNM)

  IF @DotPos = 0

  SET @Ext = ''

  ELSE BEGIN

  SET @Ext = SUBSTRING(@FileNM, 1, @DotPos - 1)

  SET @Ext = REVERSE(@Ext)

  END

  RETURN @Ext

  END

  This is the SP which adds a new file:

  CREATE PROCEDURE dbo.procFile_ADD

  @FileNM varchar(255),

  @SizeDM bigint,

  @ContentTypeID varchar(100),

  @ContentBD image,

  @FileID uniqueidentifier OUTPUT

  AS

  SET NOCOUNT ON

  BEGIN

  SET @FileID = NEWID()

  INSERT INTO tblFile (FileID, FileNM, SizeDM, ContentTypeID, UploadDT, ContentBD)

  VALUES (@FileID, @FileNM, @SizeDM, @ContentTypeID, GETDATE(), @ContentBD)

  END

  GO

  Hope this helps. Sorry my post isn't nicely formatted. I have to use the non-WYSIWYG form on SSC.com because the fancy one never loads for me.

• shifan

  SSC Enthusiast

  Points: 153

  More actions

  April 5, 2006 at 8:40 pm

  #631135

  thanks a lot Milzs.

  
  shifan