Re-Introduction of Me and My Project, and a Question regarding Updating Existing Records in Bound Data Tables

In the first half of 2016, I began developing a complex VB.NET solution for managing a local 9 ball pool league. For the most part, EE has helped me to accomplish the coding one small task at a time. This usually involved a larger idea for a feature in the program, and I would generally give myself the assignment of breaking it down into small "doable" pieces, and if I got stuck, I would spend hours reading through various books and other on-line resources. Of course, these are often very generalized, and I found it difficult to apply it to my situation. That's when I reached out to EE for help.

Now, more than six months later, I have decided to continue my journey. Sadly much of what I thought I learned seems to elude me, but I am still pressing on.

Most of the project revolves around a complex series of tables and views which I created using MS Access. For example, there is a table for the Roster (players), another for Teams, another for the Schedule, another for Matches, and so on. These are all very relational, so I also built Views (Queries) that allow me to show the Team Name when in fact only the Team ID is stored in the Roster.

One of the features of the program is that it creates a record in the Match table once the schedule, teams and players are selected. I'm pretty sure it's the right thing to do to create the record when the match is started, but now I'm trying to figure out how to update the table with other metadata when the match is over (points, balls, fouls, innings, time outs, and other things that occur during the match but are not known at the beginning when I first create the record.

So if you've gotten this far, you should have a pretty good idea what I'm trying to do today. What I need is a good example of what the VB.NET code would look like when the 2nd write (update) occurs to the Match table. The record ID (key) is in the format YYMMDD-# where '#' is the Match number (1-5). Note that all the values needed for the 2nd write are found in various labels on the Windows Form, but the form itself is not bound to any data table.

As always, please let me know what additional information you need to help. I am not expecting ready-made code, just a good example of what I've described above.

Thanks,
Tony G.
Tony GardnerSr. Programmer/AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I generally use a Connection and Command object to update my tables:

Using con As New SQLClient(YourConnectionString)
  con.Open
  Using cmd As New SQLCommand
    cmd.Connection = con
    cmd.CommandText = "UPDATE MyTable Set Field1=" & Textbox1.Text & ", Field2=" & Textbox2.Text & " WHERE YourIDField=" & IDTextbox.TExt
    cmd.ExecuteNonQuery
  End Using
End Using

If you're worried about SQL Injection, then add Parameters to the Command object, and set the  value of the Parameters, and use those instead of directly referring to the Textbox objects.
Using con As New SQLClient(YourConnectionString)
  con.Open
  Using cmd As New SQLCommand
    cmd.Connection = con
    cmd.CommandText = "UPDATE MyTable Set Field1=@Field1, Field2@Field2 WHERE YourIDField=@IDField"
    command.Parameters.AddWithValue("@Field1", txTextbox1.Text)
    command.Parameters.AddWithValue("@Field2", txTextbox2.Text)
    command.Parameters.AddWithValue("@IDField", txIDTextbox.Text)
    cmd.ExecuteNonQuery
  End Using
End Using

Also note that you'd have to enclose String and Date value in single quotes.
Tony GardnerSr. Programmer/AnalystAuthor Commented:
Thanks Scott. This looks quite promising! To date, I haven't used this method, but I'm definitely open to learning it, especially because it looks like you can update specific fields (columns) as opposed to what I've found when using the Update method on the TableAdapter.

For clarity sake, I want to include a snippet of the code I currently have which initially creates the Match record. This also lets you see all the fields currently in the table:
    Private Sub CreateMatchRecord()
        ' Match Table Uses the Following Schema:
        '     0: Match_Key (yyMMdd-M#)
        MatchKey = DateTime.Now.ToString("yyMMdd") & "-" & Format(MN, "00")
        '     1: Schedule_Key
        Dim CurrDate As DateTime = DateTime.Now.ToString("d")
        Dim SchedKey As Integer = GetCurrSchedKey(CurrDate)
        If SchedKey = 0 Then Exit Sub
        '     2: Match_No (1-5)
        Dim Match_No As Integer = MN
        '   3/4: Home/Visitor Player ID
        Dim Play_1_ID As Integer = CInt(Me.pdPlay1No.Text)
        Dim Play_2_ID As Integer = CInt(Me.pdPlay2No.Text)
        '   5/6: Home/Visitor Team ID
        Dim Team_1_ID As Integer = CInt(Me.pdTeam1No.Text)
        Dim Team_2_ID As Integer = CInt(Me.pdTeam2No.Text)
        '   7/8: Home/Visitor Player SL
        Dim Play_1_SL As Integer = CInt(Me.pdPlay1SL.Text)
        Dim Play_2_SL As Integer = CInt(Me.pdPlay2SL.Text)
        '  9/10: Player 1/2 DS (Defensive Shots)
        Dim Play_1_DS As Integer = CInt(Me.pdPlay1DS.Text)
        Dim Play_2_DS As Integer = CInt(Me.pdPlay2DS.Text)
        ' 11/12: Player 1/2 MP (Match Points)
        Dim Play_1_MP As Integer = CInt(Me.pdPlay1MP.Text)
        Dim Play_2_MP As Integer = CInt(Me.pdPlay2MP.Text)
        '    13: Total Innings
        Dim TotInning As Integer = CInt(Me.TotalInnings.Text)
        ' 14/15: Start/End Time (Actual)
        Dim Beg_Time As DateTime = DateTime.Now
        Dim End_Time As DateTime = Nothing
        ' Player 1/2 TP (Balls Pocketed)
        Dim Play_1_TP As Integer = CInt(Me.pdPlay1TP.Text)
        Dim Play_2_TP As Integer = CInt(Me.pdPlay2TP.Text)
        ' Player 1/2 TO (Time Outs)
        Dim Play_1_TO As Integer = CInt(Me.lblTimeOutL1.Text) + CInt(Me.lblTimeOutS1.Text)
        Dim Play_2_TO As Integer = CInt(Me.lblTimeOutL2.Text) + CInt(Me.lblTimeOutS2.Text)

        Me.MatchTableAdapter.Insert(MatchKey, SchedKey, Match_No, Play_1_ID, Play_2_ID,
          Team_1_ID, Team_2_ID, Play_1_SL, Play_2_SL, Play_1_DS, Play_2_DS, Play_1_MP, Play_2_MP,
          TotInning, Beg_Time, End_Time, Play_1_TP, Play_2_TP, Play_1_TO, Play_2_TO)
        Call BindingNavigatorSave_Click(Me.DataMatchSave, Nothing)
    End Sub

Open in new window

And this is a very bad example of what I had imagined for the Update code:
    Private Sub IsMatchOver(ByVal sender As Object)
        Dim PlayBC As Integer = CInt(PT(BC, TN - 1).Text)
        Dim PlayNm As String = PT(PN, TN - 1).Text
        Dim MsgOpts As MsgBoxStyle = vbYesNo + vbExclamation + vbDefaultButton2
        Dim MsgText As String = PlayNm & " has won Match " & MN & ". Click Yes to confirm, or No to return."
        If MsgBox(MsgText, MsgOpts, "SNAP - Match Complete") = vbYes Then
            MatchOver = True : Else : Return
            Dim CurrDate As DateTime = DateTime.Now.ToString("d")
            Dim MatchKey As String = DateTime.Now.ToString("yyMMdd") & "-" & Format(MN, "00")
            ' Update Match Points / Running Totals
            '  9/10: Player 1/2 DS (Defensive Shots)
            Dim Play_1_DS As Integer = CInt(Me.pdPlay1DS.Text)
            Dim Play_2_DS As Integer = CInt(Me.pdPlay2DS.Text)
            ' 11/12: Player 1/2 MP (Match Points)
            Dim Play_1_MP As Integer = CInt(Me.pdPlay1MP.Text)
            Dim Play_2_MP As Integer = CInt(Me.pdPlay2MP.Text)
            '    13: Total Innings
            Dim TotInning As Integer = CInt(Me.TotalInnings.Text)
            Dim End_Time As DateTime = DateTime.Now
            ' Player 1/2 TP (Balls Pocketed)
            Dim Play_1_TP As Integer = CInt(Me.pdPlay1TP.Text)
            Dim Play_2_TP As Integer = CInt(Me.pdPlay2TP.Text)
            ' Player 1/2 TO (Time Outs)
            Dim Play_1_TO As Integer = CInt(Me.lblTimeOutL1.Text) + CInt(Me.lblTimeOutS1.Text)
            Dim Play_2_TO As Integer = CInt(Me.lblTimeOutL2.Text) + CInt(Me.lblTimeOutS2.Text)
            Dim MatchRow As DataRow = Me.SNAPDataSet.Match.Rows.Find(MatchKey)
            ' Write final values to Match record and advance to next.
            Me.MatchTableAdapter.Update(MatchRow)
        End If
    End Sub

Open in new window


I'm pouring through my Wrox book to see if I can get a better understanding how to do the above using connection strings, and will get back to you shortly with an update.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
A good reference for Connection strings is here:

https://www.connectionstrings.com/

An example would be: "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Path to your database"

Here's what I'd do:
Using con As New OLDEBConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Path to your database")
  con.Open
  Using cmd As New OLEDBCommand
    cmd.Connection = con
        Dim PlayBC As Integer = CInt(PT(BC, TN - 1).Text)
        Dim PlayNm As String = PT(PN, TN - 1).Text
        Dim MsgOpts As MsgBoxStyle = vbYesNo + vbExclamation + vbDefaultButton2
        Dim MsgText As String = PlayNm & " has won Match " & MN & ". Click Yes to confirm, or No to return."
        If MsgBox(MsgText, MsgOpts, "SNAP - Match Complete") = vbYes Then
            MatchOver = True : Else : Return
            Dim CurrDate As DateTime = DateTime.Now.ToString("d")
            Dim MatchKey As String = DateTime.Now.ToString("yyMMdd") & "-" & Format(MN, "00")
            ' Update Match Points / Running Totals
            '  9/10: Player 1/2 DS (Defensive Shots)
            Dim Play_1_DS As Integer = CInt(Me.pdPlay1DS.Text)
            Dim Play_2_DS As Integer = CInt(Me.pdPlay2DS.Text)
            ' 11/12: Player 1/2 MP (Match Points)
            Dim Play_1_MP As Integer = CInt(Me.pdPlay1MP.Text)
            Dim Play_2_MP As Integer = CInt(Me.pdPlay2MP.Text)
            '    13: Total Innings
            Dim TotInning As Integer = CInt(Me.TotalInnings.Text)
            Dim End_Time As DateTime = DateTime.Now
            ' Player 1/2 TP (Balls Pocketed)
            Dim Play_1_TP As Integer = CInt(Me.pdPlay1TP.Text)
            Dim Play_2_TP As Integer = CInt(Me.pdPlay2TP.Text)
            ' Player 1/2 TO (Time Outs)
            Dim Play_1_TO As Integer = CInt(Me.lblTimeOutL1.Text) + CInt(Me.lblTimeOutS1.Text)
            Dim Play_2_TO As Integer = CInt(Me.lblTimeOutL2.Text) + CInt(Me.lblTimeOutS2.Text)
            Dim MatchRow As DataRow = Me.SNAPDataSet.Match.Rows.Find(MatchKey)
            ' Write final values to Match record and advance to next.
           cmd.commandtext = "UPDATE MatchTable SET Field1=" & Play_1_DS & ", Field2= & " Play_2_DS & " etc etc WHERE MatchTable.ID=" & YourID Field
           cmd.ExecuteNonQuery
    End Using
End Using
           
        End If

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Tony GardnerSr. Programmer/AnalystAuthor Commented:
My apologies for the inactivity. I've been wrestling quite a bit with Visual Studio wreaking havoc on my project.

it's probably partially my fault, but I didn't see any other recourse. Let me explain...

After further research from my Wrox book, I decided that I wanted to add an OleDbConnection so I could have the system provide the CommandText, and a few other benefits. Even though it was textbook (literally) perfect, it still wouldn't compile and VS continually insisted on creating copies of SNAPDataSetDesigner.vb which it called SNAPDataSetDesigner1.vb. I can accept VS doing this if it deems necessary, but leaving behind the original file causes well over 7,000 errors because everything is duplicated! Grrr...

The epic battle between me and VS subsided just enough for me to learn that 'Match' is a reserved word in Jet 4.0, so I fired up Access and changed it to 'Matches'. Of course, that let loose the VS dragon's fury once again, and after several more hours of code-slaying, I am now down to a single error (actually, it's a Message) shown by the Designer which continues to vex me:
DataMember property 'Match' cannot be found on the DataSource
. Here's the code:

    Private Sub IsMatchOver(ByVal sender As Object)
        ' Update Match Points / Running Totals
        ' Write final values to Match record and advance to next.
        Dim command_builder As New OleDbCommandBuilder(OleDbDataAdapterMatches)
        Dim UpdCmd As String = command_builder.GetUpdateCommand.CommandText
        MsgBox(UpdCmd)
        Dim PlayNm As String = PT(PN, TN - 1).Text
        Dim MsgOpts As MsgBoxStyle = vbYesNo + vbExclamation + vbDefaultButton2
        Dim MsgText As String = PlayNm & " has won Match " & MN & ". Click Yes to confirm, or No to return."
        If MsgBox(MsgText, MsgOpts, "SNAP - Match Complete") = vbYes Then MatchOver = True Else Exit Sub
        '
        Using con As New OleDbConnection(My.Settings.SNAPConnectionString)
            con.Open()
            Using cmd As New OleDbCommand
                cmd.Connection = con
                Dim End_Time As DateTime = DateTime.Now
                Dim CurrDate As DateTime = DateTime.Now.ToString("d")
                Dim MatchKey As String = DateTime.Now.ToString("yyMMdd") & "-" & Format(MN, "00")
                '  Player 1/2 DS (Defensive Shots)
                Dim Play_1_DS As Integer = CInt(Me.pdPlay1DS.Text)
                Dim Play_2_DS As Integer = CInt(Me.pdPlay2DS.Text)
                ' Player 1/2 MP (Match Points)
                Dim Play_1_MP As Integer = CInt(Me.pdPlay1MP.Text)
                Dim Play_2_MP As Integer = CInt(Me.pdPlay2MP.Text)
                ' Total Innings
                Dim TotInning As Integer = CInt(Me.TotalInnings.Text)
                ' Player 1/2 TP (Balls Pocketed)
                Dim Play_1_TP As Integer = CInt(Me.pdPlay1TP.Text)
                Dim Play_2_TP As Integer = CInt(Me.pdPlay2TP.Text)
                ' Player 1/2 TO (Time Outs)
                Dim Play_1_TO As Integer = CInt(Me.lblTimeOutL1.Text) + CInt(Me.lblTimeOutS1.Text)
                Dim Play_2_TO As Integer = CInt(Me.lblTimeOutL2.Text) + CInt(Me.lblTimeOutS2.Text)
                Dim MatchRow As DataRow = Me.SNAPDataSet.Matches.Rows.Find(MatchKey)
                cmd.CommandText = "UPDATE Matches SET End_Time = " & End_Time & " WHERE Matches.Match_Key = " & MatchKey
                cmd.ExecuteNonQuery()
            End Using
        End Using
    End Sub

Open in new window


Fighting The Good Fight,
Tony G.
Tony GardnerSr. Programmer/AnalystAuthor Commented:
This issue has finally been resolved. I do, however, still have a few lingering questions.

I believe the main problem with my original code is that the OleDb parameters need to be added in the same order as the SQL command. For the UPDATE command, since WHERE goes last, the parameters for the fields used within that clause need to also be specified last. In my case, I'm not updating the key, but I do use it within the WHERE clause, so the Match_Key parameter needed to be added after all the other fields (in order). In my Google travels, I did see quite a few examples where the actual field names were not specified since apparently VB.NET only cares about the order.

There was another big hang-up that I had and it really didn't have anything to do with the UPDATE command, but in the end was one of my biggest challenges. Essentially, when the Matches record is first created, I do an INSERT, and this has pretty much worked fine from the beginning. However, at the top of the subroutine I wanted to do a check to see if that particular Matches ID was in the data table, so I tried a few methods (the LINQ query being my favorite), but in most cases, it was very much hit-or-miss. For some reason, the code wanted to execute the CreateMatch subroutine twice, and the second time around, my query would not find the Matches record created a few milliseconds earlier. I threw everything I could at it, and eventually decided to create a Public boolean variable MatchCreated so I could refer to and update that to know whether it existed or not. I'll keep that for now until I get a little smarter with more accepted methods.

Here's the code for the INSERT:
    Private Sub CreateMatchesRecord()
        If MatchCreated Then Exit Sub
        Dim command_builder As New OleDbCommandBuilder(OleDbDataAdapterMatches)
        Dim CmdTxt As String = command_builder.GetInsertCommand.CommandText
        Using con As New OleDbConnection(OleDbConnection.ConnectionString)
            con.Open()
            Using cmd As New OleDbCommand
                cmd.Connection = con
                Dim MatchKey As String = DateTime.Now.ToString("yyMMdd") & MN.ToString("D2")
                Dim CurrDate As DateTime = DateTime.Now.ToString("d")
                Dim SchedKey As Int32 = GetCurrSchedKey(CurrDate)
                'Dim FindMatch As DataRow = Me.SNAPDataSet.Matches.FindByMatch_Key(MatchKey)
                Dim MatchFound As Boolean = CheckMatchKey(MatchKey)
                If SchedKey = 0 AndAlso MatchFound Then Exit Sub
                ' If SchedKey = 0 AndAlso FindMatch IsNot Nothing Then Exit Sub
                ' TODO: Check also which teams and players are on found match record.
                If SchedKey = 0 AndAlso Not MatchFound Then
                    Dim MsgOpts As MsgBoxStyle = vbYesNo + vbQuestion + vbDefaultButton2
                    Dim MsgText As String = "No APA events are Scheduled for today. Create a practice match?"
                    If MsgBox(MsgText, MsgOpts, "SNAP - Practice Match") = vbYes Then
                        SchedKey = CInt(DateTime.Now.ToString("HHmm"))
                    Else
                        Exit Sub
                    End If
                End If
                Dim Match_No As Int32 = MN
                Dim Play_1_ID As Int32 = CInt(PT(PN, 0).Text), Play_2_ID As Int32 = CInt(PT(PN, 1).Text)
                Dim Team_1_ID As Int32 = CInt(PT(NO, 0).Text), Team_2_ID As Int32 = CInt(PT(NO, 1).Text)
                Dim Play_1_SL As Int32 = CInt(PT(SK, 0).Text), Play_2_SL As Int16 = CInt(PT(SK, 1).Text)
                Dim Play_1_DS As Int16 = 0, Play_2_DS As Int16 = 0
                Dim Play_1_MP As Int16 = 0, Play_2_MP As Int16 = 0
                Dim TotInning As Int16 = 0
                Dim Beg_Time As Double = DateTime.Now.ToOADate
                Dim End_Time As Double = DateTime.Now.ToOADate
                Dim Play_1_TP As Int32 = 0, Play_2_TP As Int32 = 0
                Dim Play_1_TO As Int32 = 0, Play_2_TO As Int32 = 0
                cmd.CommandText = CmdTxt
                'cmd.CommandText = "INSERT INTO Matches (Match_Key, Schedule_Key, Match_No, " _
                '  & "Player_1_ID, Player_2_ID, Team_1_ID, Team_2_ID, Player_1_SL, Player_2_SL, " _
                '  & "Player_1_DS, Player_2_DS, Player_1_MP, Player_2_MP, Player_1_TP, Player_2_TP, " _
                '  & "Player_1_TO, Player_2_TO, Total_Innings, Start_Time, End_Time) " _
                '  & "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
                cmd.Parameters.Add(New OleDbParameter("Match_Key", MatchKey))
                cmd.Parameters.Add(New OleDbParameter("Schedule_Key", SchedKey))
                cmd.Parameters.Add(New OleDbParameter("Match_No", Match_No))
                cmd.Parameters.Add(New OleDbParameter("Player_1_ID", Play_1_ID))
                cmd.Parameters.Add(New OleDbParameter("Player_2_ID", Play_2_ID))
                cmd.Parameters.Add(New OleDbParameter("Team_1_ID", Team_1_ID))
                cmd.Parameters.Add(New OleDbParameter("Team_2_ID", Team_2_ID))
                cmd.Parameters.Add(New OleDbParameter("Player_1_SL", Play_1_SL))
                cmd.Parameters.Add(New OleDbParameter("Player_2_SL", Play_2_SL))
                cmd.Parameters.Add(New OleDbParameter("Player_1_DS", Play_1_DS))
                cmd.Parameters.Add(New OleDbParameter("Player_2_DS", Play_2_DS))
                cmd.Parameters.Add(New OleDbParameter("Player_1_MP", Play_1_MP))
                cmd.Parameters.Add(New OleDbParameter("Player_2_MP", Play_2_MP))
                cmd.Parameters.Add(New OleDbParameter("Player_1_TP", Play_1_TP))
                cmd.Parameters.Add(New OleDbParameter("Player_2_TP", Play_2_TP))
                cmd.Parameters.Add(New OleDbParameter("Player_1_TO", Play_1_TO))
                cmd.Parameters.Add(New OleDbParameter("Player_2_TO", Play_2_TO))
                cmd.Parameters.Add(New OleDbParameter("Total_Innings", TotInning))
                cmd.Parameters.Add(New OleDbParameter("Start_Time", Beg_Time))
                cmd.Parameters.Add(New OleDbParameter("End_Time", End_Time))
                Dim rowsAffected As Integer = 0
                Try
                    rowsAffected = cmd.ExecuteNonQuery()
                Catch ex As OleDbException
                    MessageBox.Show(ex.Message)
                Finally
                    con.Close()
                    Me.SNAPDataSet.Matches.AcceptChanges()
                    MatchesTableAdapter.Update(Me.SNAPDataSet.Matches)
                    If rowsAffected = 0 Then
                        MsgBox("Matches record not created!")
                    Else
                        MatchCreated = True
                    End If
                End Try
            End Using
        End Using
    End Sub

Open in new window

I would still very much like to know if anything I did in the Finally section was at all necessary.

And here's the code for the UPDATE:
    Private Sub IsMatchOver(ByVal sender As Object)
        ' Update Match Points / Running Totals
        ' Write final values to Match record and advance to next.
        Dim command_builder As New OleDbCommandBuilder(OleDbDataAdapterMatches)
        Dim CmdTxt As String = command_builder.GetUpdateCommand.CommandText
        Dim PlayNm As String = PT(PM, TN - 1).Text
        Dim MsgOpts As MsgBoxStyle = vbYesNo + vbExclamation + vbDefaultButton2
        Dim MsgText As String = SwapFirstLastName(PlayNm) & " has won Match " & MN & "!" _
            & vbCrLf & "Click Yes to confirm, or No to return if adjustments are needed."
        If MsgBox(MsgText, MsgOpts, "SNAP - Match Complete") = vbYes Then MatchOver = True Else Exit Sub
        Dim MatchKey As String = DateTime.Now.ToString("yyMMdd") & MN.ToString("D2")
        'Dim FindMatch As Boolean = CheckMatchKey(MatchKey)
        'Dim FindMatch As DataRow = Me.SNAPDataSet.Matches.FindByMatch_Key(MatchKey)
        'If FindMatch Is Nothing Then Exit Sub ' Match ID Not found!
        If Not MatchCreated Then Exit Sub
        '
        Using con As New OleDbConnection(OleDbConnection.ConnectionString)
            con.Open()
            Using cmd As New OleDbCommand
                cmd.Connection = con
                Dim Play_1_DS As Integer = CInt(Me.pdPlay1DS.Text)
                Dim Play_2_DS As Integer = CInt(Me.pdPlay2DS.Text)
                Dim Play_1_MP As Integer = CInt(Me.pdPlay1MP.Text)
                Dim Play_2_MP As Integer = CInt(Me.pdPlay2MP.Text)
                Dim Play_1_TP As Integer = CInt(Me.pdPlay1TP.Text)
                Dim Play_2_TP As Integer = CInt(Me.pdPlay2TP.Text)
                Dim Play_1_TO As Integer = CInt(Me.lblTimeOutL1.Text) + CInt(Me.lblTimeOutS1.Text)
                Dim Play_2_TO As Integer = CInt(Me.lblTimeOutL2.Text) + CInt(Me.lblTimeOutS2.Text)
                Dim TotInning As Integer = CInt(Me.TotalInnings.Text)
                Dim End_Time As Double = DateTime.Now.ToOADate
                '
                cmd.CommandText = "UPDATE Matches SET Player_1_DS = ?, Player_2_DS = ?, " _
                  & "Player_1_MP = ?, Player_2_MP = ?, Player_1_TP = ?, Player_2_TP = ?, " _
                  & "Player_1_TO = ?, Player_2_TO = ?, Total_Innings = ?, End_Time = ? " _
                  & "WHERE Match_Key = ?"
                cmd.Parameters.Add(New OleDbParameter("Player_1_DS", Play_1_DS))
                cmd.Parameters.Add(New OleDbParameter("Player_2_DS", Play_2_DS))
                cmd.Parameters.Add(New OleDbParameter("Player_1_MP", Play_1_MP))
                cmd.Parameters.Add(New OleDbParameter("Player_2_MP", Play_2_MP))
                cmd.Parameters.Add(New OleDbParameter("Player_1_TP", Play_1_TP))
                cmd.Parameters.Add(New OleDbParameter("Player_2_TP", Play_2_TP))
                cmd.Parameters.Add(New OleDbParameter("Player_1_TO", Play_1_TO))
                cmd.Parameters.Add(New OleDbParameter("Player_2_TO", Play_2_TO))
                cmd.Parameters.Add(New OleDbParameter("Total_Innings", TotInning))
                cmd.Parameters.Add(New OleDbParameter("End_Time", End_Time))
                cmd.Parameters.Add(New OleDbParameter("Match_Key", MatchKey))
                Dim rowsAffected As Integer = 0
                Try
                    rowsAffected = cmd.ExecuteNonQuery()
                Catch ex As OleDbException
                    MessageBox.Show(ex.Message)
                Finally
                    con.Close()
                    'Me.SNAPDataSet.Matches.AcceptChanges()
                    'MatchesTableAdapter.Update(Me.SNAPDataSet.Matches)
                    If rowsAffected = 0 Then MsgBox("Matches table not updated!")
                End Try
            End Using
        End Using
    End Sub

Open in new window


Lastly the code for the CheckMatchKey function which also serves to manage the public MatchCreated variable:
    Public Shared Function CheckMatchKey(MatchID As String) As Boolean
        ' TODO: Figure out why this doesn't find the Match moments after it is created.
        Dim ChkMt =
            (
                From Matches In frmSNAP.SNAPDataSet.Matches
                Where Matches.Match_Key = MatchID
                Select Matches
            )
        If ChkMt.Count > 0 Then
            frmSNAP.MatchCreated = True
            Return True
        Else
            frmSNAP.MatchCreated = False
            Return False
        End If
    End Function

Open in new window


The only remaining comment I wish to add is that I have learned a tremendous amount through this ordeal, and greatly appreciate the assistance offered by Scott.
Tony GardnerSr. Programmer/AnalystAuthor Commented:
This was a tough one, but definitely a valuable lesson which will be essential for the success of my project.

Thanks for hanging in there with me, Scott.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.