Save Images in MSSQL 2008 Using VB.NET and Stored Procedure
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