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
Dim toFile As Stream = File.Open(Application.StartupPath & "\RosterImage.png", FileMode.Create, FileAccess.Write)
ResizeImage(dlg.OpenFile, toFile, Width, Hight)
Select Case sender.Name
Catch ex As Exception
MessageBox.Show("Cannot read file from disk. Original error: " & ex.Message)
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)
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))
Catch ex As OleDbException
Call BindingNavigatorSave_Click(Me.DataRosterSave, Nothing)
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
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?