VB.NET/MS ACCESS Display data from query

[User 11833437]

I have a textbox that I need to run this query when the value changes for the textbox.

Private Sub tbStudentID2_TextChanged(sender As Object, e As EventArgs) Handles tbStudentID2.TextChanged
mySql = "SELECT DateDiff('d', [DateofRank], [currentDate]) FROM StudentData"
ConnDB()
myCommand = New OleDbCommand(mySql, myConnection)
myCommand.ExecuteNonQuery()
myRead = myCommand.ExecuteReader
Do While myRead.Read = True
tbDays.Text = myRead(0).ToString()
Loop
End Sub

86 is display no matter which record I choose. I think it is just retrieving the first record in the in the table. I want to display the number of days since a student has tested by subtracting DateofRank from currentDate. I am open to other ideas on how to achieve this.

[Richard Deeming]

It's selecting all records from the table. You then iterate through the results, overwriting the textbox with the next value, until you get to the last record in the table. You need a WHERE clause to select just the record you're interested in. And you need to make sure you use parameters to avoid a SQL Injection[^] vulnerability. There's also no need to use ExecuteReader to read a single value from a single row; use ExecuteScalar instead. Try something like this:

Private Sub tbStudentID2_TextChanged(sender As Object, e As EventArgs) Handles tbStudentID2.TextChanged
Dim studentID As Integer
If Not Integer.TryParse(tbStudentID2.Text, studentID) Then
' Not a valid ID
txtDays.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()
        txtDays.Text = If(Convert.IsDBNull(days), String.Empty, Convert.ToString(days))
    End Using
End If

End Sub

Int32.TryParse Method (System) | Microsoft Docs[^] OleDbCommand.ExecuteScalar Method (System.Data.OleDb) | Microsoft Docs[^]

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[User 11833437]

That did the trick. Thank you very much.

[User 11833437]

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