Cannot able to Add Images into SQL Server Database

[sidind123]

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

[Oxiegen]

The first thing you should do, is to use ADO.NET System.Data.SqlClient instead of Odbc. That might be cause of the problem.
Otherwise the code looks good.
Take a look at this for reference.

[sidind123]

Dear Oxiegen,

Thanx for ur prompt reply. I know that it would have been better if i use sqlconnection instead of ODBC but i am using DSN to connect to DB so i cannot use sqlconnection. Please help me.

Thanks in advance
Sid

The first thing you should do, is to use ADO.NET System.Data.SqlClient instead of Odbc. That might be cause of the problem.
Otherwise the code looks good.
Take a look at this for reference.

[Oxiegen]

My apologies.

Try this.
If it doesn't work. Please tell where/on what line the error occurs.
And what the error message says.

Dim fs As New FileStream(picPhoto.ImageLocation, FileMode.Open)
Dim picture As Byte() = New Byte(fs.Length) {}
fs.Read(picture, 0, fs.Length)
fs.Close()

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

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

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

CMD.ExecuteNonQuery()
CN.closeconnection()

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