reading and comparing data values in MySQL database in vb.net-CodePudding

I'm trying to collect input from the user, check it against my database, and see if it exists in the database. If it does the program is supposed to fetch the last name, first name, and fees of the respective individual and output it.

Code:

Imports Mysql.Data.MySqlClient

Public Class Form1
    Dim reader As MySqlDataReader
    Dim command As New MySqlCommand

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim myConnectionString As String

        myConnectionString = "server=localhost;" _
              & "uid=root;" _
              & "pwd=Emma@21*GCTU;" _
              & "database=dummystudents"

        Try
            Dim conn As New MySql.Data.MySqlClient.MySqlConnection(myConnectionString)
            conn.Open()
            Dim sql As String = "SELECT idstudents FROM students WHERE idstudents = @TextBox1.Text "
            command = New MySqlCommand(sql, conn)
            reader = command.ExecuteReader()

            If reader.Read() Then
                TextBox2.Text = reader(1)
                TextBox3.Text = reader(2)
                TextBox4.Text = reader(3)
            End If

        Catch ex As MySql.Data.MySqlClient.MySqlException
            MessageBox.Show(ex.Message)
        End Try
    End Sub
End Class

CodePudding user response：

Here's your SQL code:

SELECT idstudents FROM students WHERE idstudents = @TextBox1.Text

What's the point of pulling idstudents out when that's the value you're putting in? Worse, though, that's ALL you're pulling out, then you do this:

TextBox2.Text = reader(1)
TextBox3.Text = reader(2)
TextBox4.Text = reader(3)

which would require you to pull back at least four columns.

The modification mentioned in the comments may well get your code to execute but it's not the right way to go. It looks like you tried to use a parameter but failed. Do that but do it right, i.e.

Dim sql As String = "SELECT idstudents, otherColumnsHere FROM students WHERE idstudents = @idstudents"

Using connection As New MySqlConnection(myConnectionString),
      command As New MySqlCommand(sql, connection)
    command.Parameters.Add("@idstudents", MySqlDbType.Int32).Value = CInt(TextBox1.Text)
    conn.Open()

    Using reader = command.ExecuteReader()
        If reader.Read() Then
            TextBox2.Text = reader(1)
            TextBox3.Text = reader(2)
            TextBox4.Text = reader(3)
        End If
    End Using
End Using