When selecting data from a database (SELECT * FROM) only last row is being read

Question

I am making a create an account system, and in this I am using a SELECT * FROM query to read all of the data in the database to see if the inputted username already exists however this is only returning the very last user in the database, so if the inputted user matches any other user excluding the last saved account, it will not recognize that the user already exists therefore creating an account with the same username. Below is my code for the sub routine. can anyone please help me find what I have done wrong

Dim conn As New OleDbConnection Dim myqry As String = Nothing Dim mycmd As New OleDbCommand Dim mydr As OleDbDataReader

Private Sub btn_createAccount_Click(sender As System.Object, e As System.EventArgs) Handles btn_createAccount.Click

    'Connecting to the database
    Try
        With conn
            If .State = ConnectionState.Open Then .Close()
            .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database.accdb"
            .Open()

        End With

    Catch ex As Exception
        MessageBox.Show("Unable to connect", "error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try


    Dim student_Name As String
    Dim student_Username As String
    Dim student_Password As String
    Dim student_Group As String

    student_Name = txt_firstname.Text & " " & txt_surname.Text
    student_Username = LCase(txt_Username.Text)
    student_Password = txt_password.Text
    student_Group = cbo_tutorGroup.SelectedItem


    'This chunk of code is reading the username column in the student account table in my database and doing a read to see if the inputted username is already existent in the table.
    myqry = "SELECT * FROM TblStudents"
    mycmd = New OleDbCommand(myqry, conn)
    mydr = mycmd.ExecuteReader
    While mydr.Read
        Dim user As String = mydr("studentUser").ToString
        If user = student_Username Then
            MsgBox("Username already exists, please choose another")


        Else

            'If the username is not taken, the account credentials will be stored for use when logging in
            Dim sqlQry As String
            sqlQry = "INSERT INTO TblStudents(studentName, tutorGroup, studentUser, studentPass) VALUES('" & student_Name & "','" & student_Group & "','" & student_Username & "','" & student_Password & "')"

            Dim cmd As New OleDbCommand(sqlQry, conn)
            cmd.ExecuteNonQuery()

            MsgBox("Your account has successfully been created")


            Login_Student.Show()
            Me.Close()
            Exit Sub
        End If
    End While

Why not use myqry = "SELECT * FROM TblStudents WHERE StudentUser = '"&Student_userName &"'" to specifically check instead of having to iterate though. (though this is open to SQL injection, a paramaterized query would be better) But if you must... why not While not mydr.eof ... msdn.microsoft.com/en-us/library/ms810288.aspx — xQbert
when you say "only returning the very last user in the database" Are you referring to the variable "user"? If so, I would say it's a given that it only returns the last one because it's overwritten in each iteration of the loop. However, I agree with xQbert that a parametrized query would be better, and if mydr.hasrows returns false, you know the user doesn't already exist — exceptional exception

Marcin Wroblewski Marcin Wroblewski · Accepted Answer · 2015-01-09T14:49:03

I think you are misinterpreting your results. The query may return many rows but if the first one does not match your condition (user = student_Username), then you insert a record and exit the procedure.

Why don't you check if your specific user exists:

SELECT * FROM TblStudents WHERE studentUser = :student_Username

instead of checking each student?

When selecting data from a database (SELECT * FROM) only last row is being read

1 Answers