Tony Gardner
asked on
Need Help with OleDb UPDATE Command (not updating table)
Hello 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:
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:
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?
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
The call to BindingNavigatorSave_Click
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
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?
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!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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:
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:
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:
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.
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
Dim toFile As Stream = File.Open(Application.StartupPath & "\RosterImage.png", FileMode.Create, FileAccess.Write)
ResizeImage(dlg.OpenFile, toFile, Width, Hight)
(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
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
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
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..
Dim bFile(objFileStream.Length
ASKER
Thanks for all your help RobR. The primary question has most certainly be resolved using your suggestions.
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...
Open in new window