Can someone check over my VB.net code? Issue explained below

Question

Okay I have created a log in system on VB.net using a database on access. The problem I am having is that some of the username and password combinations work perfectly, but some of them, although put in correctly, don't work at all. This is the code I have written...

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

    ' Check if username or password is empty
    If textpassword.Text = "" Or textusername.Text = "" Then
        MessageBox.Show("Please complete the required fields..", "Authentication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    Else
        ' Both fields were supplied
        ' Check if user exist in database
        ' Connect to DB
        Dim conn As New System.Data.OleDb.OleDbConnection()
        conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\database1.accdb"

        'conn.Open()
        'MsgBox("Susscess")

        Dim sql As String = "SELECT * FROM Accounts WHERE username='" & textusername.Text & "' AND password = '" & textpassword.Text & "'"
        Dim sqlCom As New System.Data.OleDb.OleDbCommand(sql)

        'Open Database Connection
        sqlCom.Connection = conn
        conn.Open()

        Dim sqlRead As System.Data.OleDb.OleDbDataReader = sqlCom.ExecuteReader()

        If sqlRead.Read() Then
            MemberPage.Show()
            Me.Hide()

        Else
            ' If user enter wrong username and password combination
            ' Throw an error message
            MessageBox.Show("Username and Password do not match..", "Authentication Failure", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

            'Clear all fields
            textpassword.Text = ""
            textusername.Text = ""

            'Focus on Username field
            textusername.Focus()
        End If 
    End If 
End Sub

Best to avoid concatenated strings for sql statements or you can risk sql injection issues. Also worth including Using structure to better manage object disposal. Should your '.Read' method rather be checking .'HasRows' or something similar? — Andrew Mortimer
– Andrew Mortimer, Commented Jul 15, 2015 at 9:49
Can you clarify what happens when they "don't work at all"? — SuperBiasedMan
– SuperBiasedMan, Commented Jul 15, 2015 at 9:50

Nagaraj S · Accepted Answer · 2015-07-15 10:05:18Z

3

Do not Concatenate string.Its wide open for SQL injection .Its better to use Parameterized query

Dim sql As String = "SELECT * FROM Accounts WHERE username=? AND password = ?"
Dim sqlCom As New System.Data.OleDb.OleDbCommand(sql)
sqlCom.Parameters.AddWithValue("?", textusername.Text);
sqlCom.Parameters.AddWithValue("?", textpassword.Text);

Also you can use HasRows property

If sqlRead.HasRows Then
      While sqlRead.Read() 
       MemberPage.Show()
       Me.Hide()
      End While
 Else
     MessageBox.Show("Username and Password do not match..", "Authentication Failure", MessageBoxButtons.OK, MessageBoxIcon.Exclamation) 'Clear all fields
        textpassword.Text = ""
        textusername.Text = "" 
      'Focus on Username field
        textusername.Focus()
 End If

answered Jul 15, 2015 at 10:05

Nagaraj S

13.5k6 gold badges35 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

hdkhardik · Accepted Answer · 2015-07-15 10:00:53Z

It seems that some mistake is happening at the condition you checking after filling the dataReader. i.e

If sqlRead.Read() Then

try the if condition by following code

If Not sqlRead Is Nothing  Then

if it doesnt work then..

I would suggest you to do it using DataAdapter and check whether it returns rows. if the row count is greater than 1 , you must show the MemberPage

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


    If textpassword.Text = "" Or textusername.Text = "" Then
        MessageBox.Show("Please complete the required fields..", "Authentication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    Else

        Dim conn As New System.Data.OleDb.OleDbConnection()
        conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\database1.accdb"


        Dim sql As String = "SELECT * FROM Accounts WHERE username='" & textusername.Text & "' AND password = '" & textpassword.Text & "'"
        Dim sqlCom As New System.Data.OleDb.OleDbCommand(sql)

        Dim ds As DataSet

        sqlCom.Connection = conn
        conn.Open()

        'Dim sqlRead As System.Data.OleDb.OleDbDataReader = sqlCom.ExecuteReader()

        Dim da as New OleDbDataAdapter(sqlCom)

        da.Fill(ds)

        If ds.Tables(0).Rows.Count > 1  Then
            MemberPage.Show()
            Me.Hide()

        Else

            MessageBox.Show("Username and Password do not match..", "Authentication Failure", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)


            textpassword.Text = ""
            textusername.Text = ""


            textusername.Focus()
        End If 
    End If 
End Sub

Collectives™ on Stack Overflow

Can someone check over my VB.net code? Issue explained below

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related