Link to home
Start Free TrialLog in
Avatar of Tony Gardner
Tony GardnerFlag for United States of America

asked on

Need Help with OleDb UPDATE Command (not updating table)

User generated imageHello Experts.
I'm having a little trouble making an UPDATE to one of my data tables. In this case, it's for adding a picture.   No error is being displayed, so I'm guessing the syntax for the UPDATE is okay, but there's probably a step missing which needs to come afterwards. In fact, the image is being shown in the PictureBox, just not being written to the Roster table. Let me explain...

To simplify, let's say that I have two tables; one is called Roster and contains all data for league players. This includes their ID, name, team, and other fields including their profile picture.

The second table is called "MyPlayers" and contains references to all the players on MY team. Some of the data is pointing to Roster columns, and other data is completely local to the table, such as email address, phone and more detailed stats gathered during game play. The form also has a place for the player's picture and I would like for users to be able to click on the "Load Picture" button from that screen and have it stored on the Roster table.

Here's the code I've got so far:
    Private Sub btnDataRosterPicture_Click(sender As Object, e As EventArgs) Handles btnDataRosterPicture.Click, btnMyPlayersPicture.Click
        Dim dlg As New OpenFileDialog
        Dim Width As Double = 90
        Dim Hight As Double = 120
        If dlg.ShowDialog = DialogResult.OK Then
            Try
                Dim toFile As Stream = File.Open(Application.StartupPath & "\RosterImage.png", FileMode.Create, FileAccess.Write)
                ResizeImage(dlg.OpenFile, toFile, Width, Hight)
                Select Case sender.Name
                    Case "btnDataRosterPicture"
                        DataRosterPicture.Load(dlg.FileName)
                    Case "btnMyPlayersPicture"
                        MyPlayersPicture.Load(dlg.FileName)
                        UpdatePlayerPicture(toFile)
                End Select
            Catch ex As Exception
                MessageBox.Show("Cannot read file from disk. Original error: " & ex.Message)
            End Try
        End If
    End Sub
    Private Sub UpdatePlayerPicture(jpgFile As Stream)
        Dim command_builder As New OleDbCommandBuilder(OleDbDataAdapterRoster)
        Dim CmdTxt As String = command_builder.GetUpdateCommand.CommandText
        Dim CurrID As Int32 = nullinator(Me.MyPlayersAPAID.Text)
        Dim FindID As Integer = FindPlayer(CurrID)
        If FindID = 0 Then Exit Sub
        Using con As New OleDbConnection(OleDbConnection.ConnectionString)
            con.Open()
            Using cmd As New OleDbCommand
                cmd.Connection = con
                'cmd.CommandText = CmdTxt
                cmd.CommandText = "UPDATE Roster SET Player_Picture = ? WHERE APA_Player_ID = ?"
                cmd.Parameters.Add(New OleDbParameter("APA_Player_ID", FindID))
                cmd.Parameters.Add(New OleDbParameter("Player_Picture", jpgFile))
                Try
                    cmd.ExecuteNonQuery()
                Catch ex As OleDbException
                    MessageBox.Show(ex.Message)
                Finally
                    Call BindingNavigatorSave_Click(Me.DataRosterSave, Nothing)
                End Try
            End Using
        End Using
    End Sub

Open in new window


The call to BindingNavigatorSave_Click was a "last ditch effort" to simulate clicking the Save button on the Roster form, but I would think that the UPDATE should be sufficient. Here's the code behind that subroutine:
    Private Sub BindingNavigatorSave_Click(sender As Object, e As EventArgs) Handles _
        MyDivisionSave.Click, MyPlayersSave.Click, NotesSave.Click, DataLocationsSave.Click, DataTeamsSave.Click,
        DataRosterSave.Click, DataScheduleSave.Click, DataMatchSave.Click, DataRackSave.Click, DataEventSave.Click
        ' Save Buttons
        Dim DBS As BindingSource = DirectCast(DirectCast(sender, ToolStripButton).GetCurrentParent, BindingNavigator).BindingSource
        Me.Validate()
        DBS.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.SNAPDataSet)
    End Sub

Open in new window


By the way, the image is being written to the Roster table via Sub DataRosterPicture.Load when done from the Roster form.

Any thoughts why the picture isn't getting written to the Roster table when the update occurs from MyPlayers?
Avatar of Robberbaron (robr)
Robberbaron (robr)
Flag of Australia image

are you sure the Stream is correctly processed into the parameter ?

I use a different method to create parameters for a similar situation. it may help.
 note I copy the stream into a  Byte array, and pass that to the Insert

this is my code for an insert...
    

       'save the file to the server 
        Dim objFileStream As System.IO.Stream = ctlUpload.PostedFile.InputStream
        Dim bFile(objFileStream.Length) As Byte
        _status = "Uploading...."
        objFileStream.Read(bFile, 0, objFileStream.Length)

        newId = DbActions.SaveFormsFile(filepath, ContentType, bFile)

........


Public Function SaveFormsFile(fileName As String, contentType As String, fileBlob As Byte()) As Long
        Dim ra As Long
        Dim sqlQuery As String = "", sqlParam As ADODB.Parameter
        Dim myRS As ADODB.Recordset

        Try
            OpenFormsDb()
            _cmdBwforms.CommandType = ADODB.CommandTypeEnum.adCmdText
            sqlQuery = "INSERT INTO Files (Filename, FileSize, ContentType,BinaryData) "

            sqlQuery += "VALUES (?,?, ?, ?);SELECT SCOPE_IDENTITY();"
            _cmdBwforms.CommandText = sqlQuery
            sqlParam = _cmdBwforms.CreateParameter("@Filename", ADODB.DataTypeEnum.adVarWChar, ADODB.ParameterDirectionEnum.adParamInput, fileName.Length, fileName)
            _cmdBwforms.Parameters.Append(sqlParam)
            sqlParam = _cmdBwforms.CreateParameter("@FileSize", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, 4, fileBlob.Length)
            _cmdBwforms.Parameters.Append(sqlParam)
            sqlParam = _cmdBwforms.CreateParameter("@ContentType", ADODB.DataTypeEnum.adVarWChar, ADODB.ParameterDirectionEnum.adParamInput, contentType.Length, contentType)
            _cmdBwforms.Parameters.Append(sqlParam)
            sqlParam = _cmdBwforms.CreateParameter("@BinaryData", ADODB.DataTypeEnum.adLongVarBinary, ADODB.ParameterDirectionEnum.adParamInput, fileBlob.Length, fileBlob)
            _cmdBwforms.Parameters.Append(sqlParam)

            myRS = _cmdBwforms.Execute(ra)

            Return myRS.Fields(0).Value

        Catch ex As Exception
            LogException(ex, "SaveForms:" + sqlQuery)
            Return -999
        End Try

    End Function

Open in new window

Avatar of Tony Gardner

ASKER

I'm sorry Robr, I don't think I'm smart enough to derive anything that I can actually use from your code.

I will, however, look into the possibility of converting from Stream to Byte.

For the record, I'm not really being successful in doing UPDATEs even with a simple string or integer, which underscores that I'm clearly missing something on a much more basic level. For whatever reason, I have no trouble doing INSERTs.

Thanks Anyway!
ASKER CERTIFIED SOLUTION
Avatar of Robberbaron (robr)
Robberbaron (robr)
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks RobR. I think we're moving in the right direction, but it may be revealing additional defects in my code.

Access describes the Data Type for Player_Picture as "OLE Object". If you view the record in Access, it shows as "Long binary data" (including the ones successfully loaded directly from the Roster form).

From the VS Data Designer point-of-view, Player_Picture has a DataType of "System.Byte()" [which we'll see later on is apparently different from "System.Byte"].

When a picture is loaded from the Roster form, it is process through btnDataRosterPicture_Click which will create a 90x120 thumbnail to save space on the hard drive. This is stored on the local drive using the following code:
 Dim toFile As Stream = File.Open(Application.StartupPath & "\RosterImage.png", FileMode.Create, FileAccess.Write)
                ResizeImage(dlg.OpenFile, toFile, Width, Hight)

Open in new window

(misspelling on variable Hight was intentional)

The ResizeImage sub is from another EE Expert, so I will honestly say that I don't entirely understand what it's doing, but here it is:
    Private Sub ResizeImage(fromStream As Stream, toStream As Stream, maxWidth As Double, maxHight As Double)
        Using imageIn As Image = Image.FromStream(fromStream)
            Dim widthScale As Double = 1
            If imageIn.Width > maxWidth Then widthScale = maxWidth / imageIn.Width
            Dim heightScale As Double = 1
            If imageIn.Height > maxHight Then heightScale = maxHight / imageIn.Height
            If widthScale < 1 OrElse heightScale < 1 Then
                Dim scaleFactor As Double = If(widthScale < heightScale, widthScale, heightScale)
                Dim newWidth As Integer = CInt(imageIn.Width * scaleFactor)
                Dim newHeight As Integer = CInt(imageIn.Height * scaleFactor)
                Using thumbnailBitmap As New Bitmap(newWidth, newHeight)
                    Using thumbnailGraph As Graphics = Graphics.FromImage(thumbnailBitmap)
                        thumbnailGraph.CompositingQuality = CompositingQuality.HighQuality
                        thumbnailGraph.SmoothingMode = SmoothingMode.HighQuality
                        thumbnailGraph.InterpolationMode = InterpolationMode.HighQualityBicubic
                        Dim imageRectangle As New Rectangle(0, 0, newWidth, newHeight)
                        thumbnailGraph.DrawImage(imageIn, imageRectangle)
                        Dim jpegCodec As ImageCodecInfo = ImageCodecInfo.GetImageEncoders().FirstOrDefault(Function(c) c.FormatDescription = "JPEG")
                        If jpegCodec IsNot Nothing Then
                            Dim encoderParameters As New EncoderParameters(1)
                            encoderParameters.Param(0) = New EncoderParameter(Encoder.Quality, 100L)
                            thumbnailBitmap.Save(toStream, jpegCodec, encoderParameters)
                        Else
                            thumbnailBitmap.Save(toStream, ImageFormat.Jpeg)
                        End If
                    End Using
                End Using
            Else
                imageIn.Save(toStream, ImageFormat.Jpeg)
            End If
            If toStream IsNot Nothing Then toStream.Close()
        End Using
    End Sub

Open in new window


Clearly, this Expert favored Stream, and it is working great as long as I load the image from the Roster table.

CODE CHANGE RESULTS:
As per your recommendation, I tried swapping the order of the parameter adds, and sure enough the Roster table now showed that a value was present. That's the good news.

I then tried to view the image in the form, and got an error stating that it wasn't able to read the Stream. So, I decided to try to code the 2nd part. This resulted in the following change to the btnDataRosterPicture_Click sub as follows:
               Dim toFile As Stream = File.Open(Application.StartupPath & "\RosterImage.png", FileMode.Create, FileAccess.Write)
                ResizeImage(dlg.OpenFile, toFile, Width, Hight)
                Dim bFile(toFile.Length) As Byte
                toFile.Read(bFile, 0, toFile.Length)
                Select Case sender.Name
                    Case "btnDataRosterPicture"
                        DataRosterPicture.Load(dlg.FileName)
                    Case "btnMyPlayersPicture"
                        MyPlayersPicture.Load(dlg.FileName)
                        UpdatePlayerPicture(bFile) <= [b][i]VS shows bFile as being an error[/i][/b]
                End Select

Open in new window


The error VS shows is "Value of type 'Byte()' cannot be converted to 'Byte'. As you can imagine, this is a little confusing for a newbie.

By the way, please don't hesitate to let me know if this is too much trouble. It's not an unreasonable compromise to require that player images be loaded from the Roster form!

Kind Regards,
Tony G.
perhaps Dim bFile(objFileStream.Length) As Byte should be
Dim bFile(objFileStream.Length) As Byte()  but dont think so..
Thanks for all your help RobR. The primary question has most certainly be resolved using your suggestions.