0

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
Improve this question
4
  • 2
    ...WHERE idstudents =" & textbox1.text ? Does your message box show with any errors? Commented May 17, 2022 at 10:36
  • @Nathan_Sav after inserting this ...WHERE idstudents =" & textbox1.text TextBox2.Text = reader(1) was flagged with this error code "System.IndexOutOfRangeException: 'Index was outside the bounds of the array." Commented May 17, 2022 at 10:59
  • I am not sure what problem you encountering, but in your Select Query, you only Select IDstudents. so if later you try to read your data. you would only read IDstudents. in the select statement only add the fields you need to select. Commented May 17, 2022 at 11:33
  • No project should have TextBoxes with those generic names. ALWAYS change the names of your controls to something meaningful, e.g. a TextBox to store a given name should be named givenNameTextBox or the like. Commented May 17, 2022 at 12:14

1 Answer 1

Reset to default
0

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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.