0
Answer

Save Images in MSSQL 2008 Using VB.NET and Stored Procedure

Siddhartha Saha

Siddhartha Saha

14y
4.9k
1
I want to save image from Picture box to SQL Server DB. I am trying to do this using the following code but cannot able to do so. Please help.

My Code Snippets are as following:  

     Dim fs As FileStream = Nothing
        fs = New FileStream(picPhoto.ImageLocation, FileMode.Open)
        Dim fi As FileInfo = New FileInfo(picPhoto.ImageLocation)
        Dim temp As Long = fi.Length
        Dim lung As Integer = Convert.ToInt32(temp)
        Dim picture As Byte() = New Byte(lung - 1) {}
        fs.Read(picture, 0, lung)
        fs.Close()

        Dim FileName_Renamed As Odbc.OdbcParameter = Nothing
        Dim pic As Odbc.OdbcParameter = Nothing

        CN.OpenConnection()
        CMD = New Odbc.OdbcCommand("SavePhoto", CN.DBConnection)
        CMD.CommandType = CommandType.StoredProcedure

        pic = New Odbc.OdbcParameter("@Picture", Odbc.OdbcType.Image)
        pic.Value = picture
        CMD.Parameters.Add(pic)

        FileName_Renamed = New Odbc.OdbcParameter("@ID", Odbc.OdbcType.VarChar)
        FileName_Renamed.Value = txtID.Text
        CMD.Parameters.Add(FileName_Renamed)

        CMD.ExecuteNonQuery()
        CN.closeconnection()


My Stored Procedure are as following:

USE [EarthMovers]
GO
/****** Object:  StoredProcedure [dbo].[SavePhoto]    Script Date: 06/29/2010 13:22:35 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
-- =============================================
-- Author:        Siddhartha Saha
-- Create date: 29/06/2010
-- Description:   
-- =============================================
ALTER PROCEDURE [dbo].[SavePhoto]
    -- Add the parameters for the stored procedure here
(
@Picture image,
@ID varchar(60)
)
AS
UPDATE ItemDimension SET itmdmnPhoto=@Picture WHERE itmdmnID=@ID