store/update a image in sql server using vb.net
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2

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

  1. #1
    Join Date
    May 2008
    Posts
    74

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

    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

  2. #2
    Join Date
    Jun 2005
    Location
    JHB South Africa
    Posts
    3,770

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

    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
    Code:
    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
    Code:
            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
    Code:
        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..
    Code:
        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
    Code:
        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...
    Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
    WPF Articles : 3D Animation 1 , 2 , 3
    Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
    Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
    All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center