update. Help me, am I doing it wrong?-CodePudding

Here, is a code that updates the data in the fees column that corresponds to the Student ID entered by the student. There are no errors in the code, but the program doesn't seem to update the value in the database.

  Private Sub dbupdate()
        'Create a connection object.
        myConnectionString = "server=localhost;" _
              & "uid=root;" _
              & "pwd=password;" _
              & "database=dummystudents"

        Try
            'open the connection
            Dim conn As New MySql.Data.MySqlClient.MySqlConnection(myConnectionString)
            conn.Open()

            'create a command object.
            sql = "UPDATE students SET fees = '" & txtBalance.Text & "'WHERE idstudents = '" & txtStudentID.Text
            command = New MySqlCommand(sql, conn)
            command.ExecuteNonQuery()
            'Close connection
            conn.Close()

        Catch ex As Exception

        End Try


    End Sub

CodePudding user response：

Are you using a debugger? I mean really using a debugger? Put a breakpoint after you build the sql and inspect the actual sql. Does it look correct?

UPDATE students SET fees = 'txtBalanceText'WHERE idstudents = 'txtStudentIDText

You may notice it's missing a closing single quote after student id. Also, I wonder what types fees and idstudents are in your database. If they are numeric, which they should be, then you don't use single quotes at all. If they are text then you do need the quotes. Fix the quotes. You can add another single quote like this

sql = "UPDATE students SET fees = '" & txtBalance.Text & "' WHERE idstudents = '" & txtStudentID.Text & "'"

The above code will just fix the missing quote.

However,

Here is how you can improve your code. For one, use Using blocks to create and dispose the connection and command. These objects may have unmanaged memory behind the scenes and you should always either call obj.Dispose() or let the Using block handle it automatically. Also, this sample adds your parameters which effectively avoids the aforementioned sql injection risk

Dim myConnectionString = "server=localhost;" &
    "uid=root;" &
    "pwd=password;" &
    "database=dummystudents"
Using conn As New MySqlConnection(myConnectionString)
    conn.Open()
    Dim sql = "UPDATE students SET fees = @fees WHERE idstudents = @studentID"
    Using com As New MySqlCommand(sql, conn)
        com.Parameters.AddWithValue("@fees", txtBalance.Text)
        com.Parameters.AddWithValue("@studentID", txtStudentID.Text)
        com.ExecuteNonQuery()
    End Using
End Using

Depending on the field types, adding the parameters will either include or exclude the single quotes so you don't need to worry about that anymore - an added bonus.