Database Empty After Insert? 2

[Ferlin]

Hello All,

I have been fighting this for DAYS now, I have a picturebox that I place an .png image in, I then have an Update button to add this image to a Database.

I have tried severl tutorials on adding images to a database all with the same results.

There is no exception throwen or error of any kind, but when I open the database it is empty.

Here is the code I got from Microsofts Web Site last and tried. I was article number 317670 here is the code I have.

    Private Sub btnUpload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpload.Click
        Try
            If Trim(txtImageName.Text) = "" Then
                MsgBox("Please Select An Image.")
                Exit Sub
            End If

            Dim cn As New SqlConnection(strCon)
            Dim cmd As New SqlCommand("INSERT INTO Images ([ImageName], [ImageData]) VALUES (@ImageName, @ImageData)", cn)
            Dim ms As MemoryStream = New MemoryStream()
            pbIconImage.Image.Save(ms, ImageFormat.Jpeg)
            Dim bytIconData(ms.Length - 1) As Byte
            ms.Position = 0
            ms.Read(bytIconData, 0, ms.Length)
            ms.Close()
            Dim prmName As New SqlParameter("@ImageName", SqlDbType.NVarChar, 50, ParameterDirection.Input, False, 0, 0, Nothing, DataRowVersion.Current, Trim(lblIconName.Text))
            Dim prmImage As New SqlParameter("@ImageData", SqlDbType.Image, bytIconData.Length, ParameterDirection.Input, False, 0, 0, Nothing, DataRowVersion.Current, bytIconData)
            cmd.Parameters.Add(prmName)
            cmd.Parameters.Add(prmImage)
            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
        Catch ex As System.Data.SqlClient.SqlException
            MsgBox(ex.Message)
        End Try
    End Sub

I tried changing it from varbinary to image on the sqldbtype with same results.

I load the image in the picturebox, click the button, and then look at the table, which is still empty.

Any help would be greatly appreciated, and by the way, I know people say it is BAD practice to store images in a databse, just store a path, but I do NOT want 1800 images in a directory when my clients use my program.

Thanks in advance.

Ferlin.

 

[AlexCuse]

Hi Ferlin,

I have never done what you are trying to do here, for the very reason you mention. But I think this article will help you:

http://www.dbazine.com/sql/sql-articles/larsen13

There is also another method that involves sending a byte array to SQL Server that was discussed in the C# forum a while back, I will see if I can track it down (it was a subscription only article I'm afraid :-( )

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[PsychoCoder]

Ferlin,

This should work for you

Dim loConnection As SqlClient.SqlConnection
      Dim loCommand As SqlClient.SqlCommand
      Dim loProductImage As SqlClient.SqlParameter
      Dim loImageValue() As Byte
      Dim loFile As IO.FileStream
      Dim lnByteCount As Integer

      'Instantiate new connection object
      loConnection = New SqlClient.SqlConnection()

      With loConnection
         'Prepare connection string and open connection to database
         .ConnectionString = "Data Source='ExampleServer';" & _
         "Initial Catalog=Examples;Integrated Security=SSPI"

         .Open()

         'Open file with product image as a stream
         loFile = New System.IO.FileStream("YourImageFile",IO.FileMode.Open)

         'Get length of stream (lengt of file) in bytes
         lnByteCount = loFile.Length()

         'Reallocate storage space for an array variable to the
         'size of image file
          ReDim loImageValue(lnByteCount - 1)

         'Load stream into array of bytes.
         'lnByteCount will get real number of bytes which
         'were retrieved from stream
         lnByteCount = loFile.Read(loImageValue, 0, lnByteCount)

         'Close stream
         loFile.Close()
         loFile = Nothing

         'Instantiate new command object which we will
         'use to insert image into table
         loCommand = New SqlClient.SqlCommand()
         loCommand.CommandType = CommandType.Text
         loCommand.CommandText = "INSERT INTO tblProductImages(Product_Image) VALUES (@ProductImage)"
         loCommand.Connection = loConnection

         'Add parameter, which will accept image value,
         'to command object
         loProductImage = loCommand.Parameters.Add("@ProductImage ", Data.SqlDbType.Image)

         'Set type of parameter to Image
         loProductImage.SqlDbType = SqlDbType.Image

         'Load parameter with actual data from array of bytes
         loProductImage.Value = loImageValue

         'Execute prepared query to provide actual inserting of prepared data
         loCommand.ExecuteNonQuery()
         loCommand.Dispose()
         loCommand = Nothing

         .Close()
         .Dispose()
      End With

      loConnection = Nothing

Application Developer: VB.Net, Qik III, SQL 2000/2005

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"Let's fact it, most computer users have the brain of a Spider Monkey"
~ Bill Gates ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[Ferlin]

AlexCuse and PsychoCoder,

Thanks for the replies, I found both very informative, I copied and pasted the code above, and tried to use it, and still no data in databse, I then removed all the image stuff and tried to INSERT a hardcoded 'Test' in the ImageName field, and still no data.

I was using a Database that was Attached, so I deleted it, and created a new Database in the SQLServer 2005, and used that database, and it works fine, I'll just go that route, and backup and restore when I move to another computer with it I guess.

Again thanks for the help.

Later.

Ferlin.