This is how I fixed with help from a post on an another site. I do have one question though. If I comment out the first db connection - ConnDB() I get an error. Why does it need two connection to work? It makes a connection in the using command. I had setup a data table from a class I wrote to make a connection and set it up so I declare Private Access As New DBControl at the top and I can add parameters and do my additions and edits that way. I didn't know how to make that work with the existing connection to the Access DBControl so I just did another connection. Not the best but I am a beginner and don't know how to do things with more efficiency yet.
Private Sub tbStudentID2_TextChanged(sender As Object, e As EventArgs) Handles tbStudentID2.TextChanged
mySql = "SELECT DateDiff('d', [DateofRank], [currentDate]) FROM StudentData"
'"SELECT DateDiff('d', [DateofRank], [currentDate]) FROM StudentData WHERE id={id}"
ConnDB()
'myCommand = New OleDbCommand(mySql, myConnection)
'myCommand.ExecuteNonQuery()
Dim studentID As Integer
If Not Integer.TryParse(tbStudentID2.Text, studentID) Then
' Not a valid ID
tbDays.Text = String.Empty
Else
Using command As New OleDbCommand("SELECT DateDiff('d', \[DateofRank\], \[currentDate\]) FROM StudentData WHERE StudentID = @StudentID", myConnection)
command.Parameters.AddWithValue("@StudentID", studentID)
ConnDB()
Dim days As Object = command.ExecuteScalar()
tbDays.Text = If(Convert.IsDBNull(days), String.Empty, Convert.ToString(days))
End Using
' ADD PARAMETERS - ORDER MATTERS !!!
Access.AddParam("@TIG", tbDays.Text)
Access.AddParam("@SID", tbStudentID2.Text)
' RUN COMMAND
Access.ExecQuery("UPDATE StudentData " &
"SET TimeinGrade=@TIG " &
"WHERE StudentID=@SID")
' REPORT & ABORT ON ERRORS
If NoErrors(True) = False Then Exit Sub
End If
End Sub
