store/update a image in sql server using vb.net

[jason213123]

hi,
i looking for a easy way to save a image to a sql server i
have found a lot of examples but many is for asp.net and others have
many functions for do a simple process that with vb6 i do with a few lines:
"mstream.LoadFromFile .FileName
RS.Fields("imagem_sql").Value = mstream.Read
Image1.Picture = LoadPicture(.FileName)
RS.update"

so i looking for a simple way to do that i have found this:

connetionString = "Data Source=ip;Initial Catalog=bd;User ID=user;Password=pass"

Dim con As String = connetionString
Dim str As String = "select * from store where id like '" & TextBox23.Text & "'"
Dim da As New SqlDataAdapter(str, con)
Dim cmd As New SqlCommand(str, New SqlConnection(con))
Dim MyCB As SqlCommandBuilder = New SqlCommandBuilder(da)
Dim ds As New DataSet()

connection.Open()
command = New SqlCommand(sql, connection)
adapter.SelectCommand = command
adapter.Fill(ds)
adapter.Dispose()
command.Dispose()
connection.Close()


Dim fs As New FileStream(txtImageFile.Text, FileMode.OpenOrCreate, FileAccess.Read, FileShare.ReadWrite)
Dim MyData(fs.Length) As Byte
fs.Read(MyData, 0, fs.Length)
fs.Close()
ds.Tables(0).Rows(0)("imagem_sql") = MyData

da.Update(ds, 0)

fs = Nothing
MyCB = Nothing
ds = Nothing
da = Nothing
cmd.Connection.Close()
con = Nothing

but this simple dont update nothing
i have tryed to update a text value but the result is the same nothing is updated!
but he actully dont return any error and i can see that the record is opened but simple can´t update

any one can help me to understand what is wrong and why this dont update?
thanks a lot for your help

[GremlinSA]

When it comes to SQL, most of my projects are using Stored procedures to access the data, so my examples will use this method...

SQL Stored Procs

CREATE PROCEDURE [dbo].[New_logo]
(
	@Name nvarchar(50),
	@Logo image
)
AS
BEGIN

	INSERT
	INTO [Logos]
	(
		[Name],
		[Logo]
	)
	VALUES
	(
		@Name,
		@Logo
	)

END

CREATE PROCEDURE [dbo].[Update_Logos]
(
	@ID int,
	@Name nvarchar(50),
	@Logo image
)
AS
BEGIN

	UPDATE [Logos]
	SET
		[Name] = @Name,
		[Logo] = @Logo
        WHERE [ID] = @ID

END

CREATE PROCEDURE [dbo].[Get_Logos]
AS
BEGIN

	SELECT
		[ID],
		[Name],
		[Logo]
	FROM [Logos]

END

CREATE PROCEDURE [dbo].[Select_Logos]
(
	@ID int
)AS
BEGIN

	SELECT
		[ID],
		[Name],
		[Logo]
	FROM [Logos]
        WHERE [ID] = @ID

END

I then use functions to call each of the stored prcos...

VB.NET Functions

        Public Function GetLogos() As List(Of Logo)
            Dim sqlConn As New SqlConnection(GetConnectionString())
            Dim cmd As New SqlCommand
            Dim dr As SqlDataReader

            Getlogos = New List(Of Logo)
            Try
                sqlConn.Open()
                With cmd
                    .Connection = sqlConn
                    .CommandType = CommandType.StoredProcedure
                    .CommandText = "Get_logo"
                    .Connection = sqlConn
                    dr = .ExecuteReader()
                    If Not dr Is Nothing Then
                        While dr.Read
                            Dim Thislogo As New Logo
                            With ThisLogo
                                .ID = dr("ID")
                                .Name = dr("Name")
                                .Logo = dr("Logo")
                            End With
                            GetLogos.Add(ThisLogo)
                        Wend
                    End If
                End With

            Catch ex As Exception
                ErrorLogInsert( ex.ToString)
            End Try
        End Function

        Public Function SelectLogo(ID as Integer) As Logo
            Dim sqlConn As New SqlConnection(GetConnectionString())
            Dim cmd As New SqlCommand
            Dim dr As SqlDataReader

            SelectLogo = New Logo
            Try
                sqlConn.Open()
                With cmd
                    .Connection = sqlConn
                    .CommandType = CommandType.StoredProcedure
                    .CommandText = "Select_logo"
                    .Connection = sqlConn
                    dr = .ExecuteReader()
                    If Not dr Is Nothing Then
                        If dr.Read
                            With SelectLogo
                                .ID = dr("ID")
                                .Name = dr("Name")
                                .Logo = dr("Logo")
                            End With
                        End If
                    End If
                End With

            Catch ex As Exception
                ErrorLogInsert( ex.ToString)
            End Try
        End Function

        Public Sub Updatelogo(ByVal logoItem As logo)
            Dim sqlConn As New SqlConnection(GetConnectionString())
            Dim cmd As New SqlCommand
            Dim dr As SqlDataReader

            Try
                sqlConn.Open()
                With cmd
                    .Connection = sqlConn
                    .CommandType = CommandType.StoredProcedure
                    .CommandText = "Update_logo"
                    .Parameters.AddWithValue("@ID", logoItem.ID)
                    .Parameters.AddWithValue("@Logo", logoItem.Logo)
                    .Parameters.AddWithValue("@Name", logoItem.Name)
                    .Connection = sqlConn
                    dr = .ExecuteReader()

                End With

            Catch ex As Exception
                ErrorLogInsert( ex.ToString)

            End Try
        End Sub

        Public Sub Newlogo(ByVal Logoitem As Utility)
            Dim sqlConn As New SqlConnection(GetConnectionString())
            Dim cmd As New SqlCommand
            Dim dr As SqlDataReader

            Try
                sqlConn.Open()
                With cmd
                    .Connection = sqlConn
                    .CommandType = CommandType.StoredProcedure
                    .CommandText = "New_Logo"
                    .Parameters.AddWithValue("@Logo", Utilityitem.Logo)
                    .Parameters.AddWithValue("@Name", Utilityitem.Name)
                     .Connection = sqlConn
                    dr = .ExecuteReader()

                End With

            Catch ex As Exception
                ErrorLogInsert(ex.ToString)
            End Try
        End Sub

After all of that is done ... its a simple case of using memory streams to transfer the data to and from the picturebox..

To get it from SQL into a Picturebox

    Private Sub ShowLogo(ByVal ID As Integer)
        LogoData = SelectLogo(ID)
        With LogoData
            TxtName.Text = .Name
            TxtID.Text = CStr(.ID)
            If Not .Logo Is Nothing Then
                If .Logo.Length > 4 Then
                    Dim ms As New IO.MemoryStream(.Logo, 0, .Logo.Length)
                    PBlogo.Image = Image.FromStream(ms)
                End If
            End If
        End With
    End Sub

And to save a New logo into SQL, I normally create a temporary filestreamed copy from the original file..

    Private Sub Logo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim Res As System.Windows.Forms.DialogResult
        OFDialog.Filter = "All Images|*.jpg;*.bmp;*.gif;*.jpeg|Jpeg Files|*.jpg;*.jpeg|Bitmap Files|*.bmp|Gif Files|*.gif|All Files|*.*"
        OFDialog.FileName = ""
        Res = OFDialog.ShowDialog()
        If Res = Windows.Forms.DialogResult.OK Then
            If OFDialog.FileName <> "" Then
                If File.Exists(OFDialog.FileName) Then
                    PBlogo.Load(OFDialog.FileName)
                    Dim fs As New FileStream(OFDialog.FileName, FileMode.Open, FileAccess.Read)
                    Dim temp(fs.Length) As Byte
                    fs.Read(temp, 0, fs.Length)
                    Tmp.Logo = temp
                End If
            End If
        End If
    End Sub

And then to save (or to Update) you can use

    Private Sub SaveNewLogo()
        Dim LogoData As New Logo
        With LogoData
            .Name = TxtName.Text
            .ID = 0
            If Not Tmp.Logo Is Nothing Then
                .Logo = Tmp.Logo
            Else
                ReDim .Logo(1)
            End If
        End With
        NewLogo(LogoData)
    End Function

    Private Sub UpdateLogo()
        With LogoData
            .Name = TxtName.Text
            .ID = Cint(TxtID.Text)
            If Not Tmp.Logo Is Nothing Then
                .Logo = Tmp.Logo
            Else
                ReDim .Logo(1)
            End If
        End With
        UpdateLogo(LogoData)
    End Function

Hope These Help you ....

Gremmy...