dcsimg
Results 1 to 16 of 16

Thread: [RESOLVED] Login Form - Access failed

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Sep 2018
    Posts
    83

    Resolved [RESOLVED] Login Form - Access failed

    Dear Gurus,
    I'm developing an application in VB.net with an SQL Express 2017 db behind. Previously it was connected to an Access DB. Now I'm re-engineering it to work with SQL.

    I'm currently revisiting the login page. I entered in the same value I had in Access in SQL table as well, but everytime I try to login it gives me "Access failed" message. Here below you can find the code (with also statements from previous version commented).

    Code:
    'Imports System.Data.OleDb
    Imports System.Data.SqlClient
    Imports System.Data
    
    Public Class Form1
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim uname As String = ""
            Dim pword As String
            Dim username As String = ""
            Dim pass As String
            If TextBox1.Text IsNot "" And TextBox2.Text IsNot "" Then
                uname = TextBox1.Text
                pword = TextBox2.Text
                Dim query As String = "SELECT Password FROM Tbl_Utenti WHERE Userlogin = '" & uname & "';"
                'Dim dbsource As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = " & Application.StartupPath & "\DBUtenti.accdb"
                ' Dim dbsource As String = "Data Source=.\SQLEXPRESS;Initial Catalog=DBUtenti;Integrated Security=True;Pooling=False"
                Dim dbsource As String = "Server=cpx-xv8vhm7v1x1\sqlexpress;Database=DBUtenti;Integrated Security=True"
                Dim conn = New SqlConnection(dbsource)
                Dim cmd As New SqlCommand(query, conn)
                conn.Open()
                Try
                    pass = ""
                    pass = cmd.ExecuteScalar().ToString
                Catch ex As Exception
                    MsgBox("Username non valida",, "Accesso applicazione")
                End Try
                If pword = pass Then
                    MsgBox("Accesso Valido",, "Accesso applicazione")
                    Form2.Show()
                    Dim visible1 As Object = Form2.Visible
    
                    If visible1 Then
                        Me.Hide()
                    End If
                Else
                    MsgBox("Accesso fallito",, "Accesso applicazione")
                    TextBox1.Clear()
                    TextBox2.Clear()
                End If
            Else
                MsgBox("Inserisci i dati per accedere", MsgBoxStyle.Critical, "Accesso applicazione")
            End If
        End Sub
    Please consider that both the fields on the SQL table are of type nvarchar(50).

    Could you please let me know where I'm mistaking?

    Thanks,
    A.

  2. #2
    Frenzied Member schoemr's Avatar
    Join Date
    Apr 2016
    Location
    South Africa
    Posts
    1,118

    Re: Login Form - Access failed

    Hi, If I may ask are you using Visual studio?

    You are trying to login with a username and password that is already exist in database?
    Don't miss the whole point of the dance...

    https://www.youtube.com/watch?v=qHnIJeE3LAI

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Sep 2018
    Posts
    83

    Re: Login Form - Access failed

    Dear schoemr,
    of course you can ask.
    I'm indeed using Visual Studio and I'm indeed trying to login with the unique record I have in the DB. Please see attachment.

    Thanks,
    A.
    Attached Files Attached Files

  4. #4
    Frenzied Member schoemr's Avatar
    Join Date
    Apr 2016
    Location
    South Africa
    Posts
    1,118

    Re: Login Form - Access failed

    I am not sure about your connection - but if you are sure that your method is indeed open a connection, then you have to check if that username and associated password exist in the database. You can use a DataReader.

    If there is such a username and matching password then the DataReader will return a row. If a row is found then proceed....If no row is found then login fails.

    Here is an example:

    Code:
     Dim dr As SqlDataReader
                Dim cmd As SqlCommand
                Dim sql As String
    
    
                cmd = New SqlCommand
                cmd.CommandType = CommandType.Text
                cmd.Connection = connection
    
                sql = "SELECT * FROM tblUsers WHERE Username='" & TextBox1.Text & "' AND Password='" & TextBox2.Text & "'"
    
    
                cmd.CommandText = sql
    
                dr = cmd.ExecuteReader
    
    
                If dr.HasRows Then
                    dr.Read()
    
    MsgBox("Login Success")
    Don't miss the whole point of the dance...

    https://www.youtube.com/watch?v=qHnIJeE3LAI

  5. #5
    Frenzied Member schoemr's Avatar
    Join Date
    Apr 2016
    Location
    South Africa
    Posts
    1,118

    Re: Login Form - Access failed

    in VS there are better (easier) ways to connect to database. These older guys on vbforums is always fighting with me because I don't do some things in the old fashioned way But there is benefit to learn old ways too...
    Don't miss the whole point of the dance...

    https://www.youtube.com/watch?v=qHnIJeE3LAI

  6. #6
    Addicted Member Goggy's Avatar
    Join Date
    Oct 2017
    Posts
    158

    Re: Login Form - Access failed

    your not accidentally mistaken Column Username with Userlogin?
    Utterly useless, but always willing to help

    As a finishing touch god created the dutch

  7. #7
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    34,056

    Re: Login Form - Access failed

    I prefer a somewhat different approach to this, but this one should be working fine...and it's not. So, what have you done to look at this? The first thing I would do would be to put a breakpoint on this line:

    If pword = pass Then

    and have a look at pword and pass. You may see a variety of different things when you do this. Most likely, pword will be empty, but if it is not, that would be pretty interesting. In fact, the two might look to be the same, or differ in some trivial and obvious way. If they look identical, then it is likely that some invisible character is added to one of them, whereas if there is some trivially obvious difference, then there is also a trivial solution.

    Of course, the most likely result is that pword is empty, and that will take further study. It could be that the argument supplied is incorrect in some way, and you can further study that by copying the SQL from your query variable and use that in SQL Server Management Studio to play around with it and try to figure out why it is returning nothing. Since the SQL looks fine, if the problem turns out to be in the SQL string, you'll likely see it when you look at it.
    My usual boring signature: Nothing

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Sep 2018
    Posts
    83

    Re: Login Form - Access failed

    Dear schoemr,
    thanks for your suggestion.

    I modified the code as per your indication, but again the login is failing. I'm sure this has something to do with Data type and/or with spaces or something like it..any suggestion?

    Here the new code:

    Code:
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim pass As SqlDataReader
            Dim cmd As SqlCommand
            Dim query As String
    
            If TextBox1.Text IsNot "" And TextBox2.Text IsNot "" Then
    
    
                Dim dbsource As String = "Data Source=.\SQLEXPRESS;Initial Catalog=DBUtenti;Integrated Security=True;Pooling=False"
                Dim conn = New SqlConnection(dbsource)
    
                cmd = New SqlCommand
                cmd.CommandType = CommandType.Text
                cmd.Connection = conn
                conn.Open()
    
                query = "SELECT * FROM Tbl_Utenti WHERE Username='" & TextBox1.Text & "' AND Password='" & TextBox2.Text & "'"
    
                cmd.CommandText = query
                pass = cmd.ExecuteReader
    
                If pass.HasRows Then
                    pass.Read()
    
                    MsgBox("Accesso Valido",, "Accesso applicazione")
    
                    Form2.Show()
                    Dim visible1 As Object = Form2.Visible
    
                    If visible1 Then
                        Me.Hide()
                    End If
    
                Else
                    MsgBox("Accesso fallito",, "Accesso applicazione")
                    TextBox1.Clear()
                    TextBox2.Clear()
                End If
            Else
                MsgBox("Inserisci i dati per accedere", MsgBoxStyle.Critical, "Accesso applicazione")
            End If
    
        End Sub
    Thanks,
    A.

  9. #9
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    34,056

    Re: Login Form - Access failed

    You don't need the HasRows line. Read will return True if there was a record and False if there was not, so you could just write:

    If pass.Read Then

    As that will do the same thing as HasRows while skipping one extra step.
    My usual boring signature: Nothing

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Sep 2018
    Posts
    83

    Re: Login Form - Access failed

    Dear Shaggy Hiker,
    please see my post above. I'm pretty sure both in my original version and in the new suggested one there is problem linked with data type or spaces. Please note that in my original version, when I put a breakppoint at the line you indicated, both the variables where valued properly at "210582".

    Is there any control that I could apply to truncate spaces or something like it?

    Thanks for your suggestion,
    A.

  11. #11

    Thread Starter
    Lively Member
    Join Date
    Sep 2018
    Posts
    83

    Re: Login Form - Access failed

    Dear Shaggy Hiker,
    I've tried it but it is still failing. In my tests, in fact once it worked. In that case the SQL statement was selecting the password looking for the username inserted in the textbox, but actually it was not checking whether there was correspondence in the password inserted and the one which is in the db. So, it is definitely the password field in the db that is creating the issue.

    Do you have any clue on how I can overcome this?

    Thanks,
    A.

  12. #12
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    34,056

    Re: Login Form - Access failed

    Actually, after looking over the new solution, it can be improved considerably. ExecuteReader is a total waste, in this case. ExecuteScalar is faster and lighter weight, so you might as well use it. After all, the query will return nothing if the credentials don't match, and will return one record if the credentials do match. Therefore, you can write the query like this:

    query = "SELECT COUNT(*) FROM Tbl_Utenti WHERE Username='" & TextBox1.Text & "' AND Password='" & TextBox2.Text & "'"

    Now, if you use ExecuteScalar, you will get back a number. If that number is 0, then there was no match. If the number is 1, then all is well, if the number is greater than 1, then there's a problem with the database, but you can likely ignore that. Thus, the code becomes:
    Code:
    query = "SELECT COUNT(*) FROM Tbl_Utenti WHERE Username='" & TextBox1.Text & "' AND Password='" & TextBox2.Text & "'"
    cmd.CommandText = query
    
    If CInt(cmd.ExecuteNonQuery) >0 Then
      'A match was found.
    Else
     'A match was not found
    End If
    This will have better performance than using the datareader, though you won't see the difference. Much less data is being moved around with the query.

    The second point to make is that you really should be using parameters. If this is for a class, you can get away with concatenating user input directly into the query, but it would be better not to. Doing that leaves your code open to SQL injection attacks whereby a malicious person can destroy your database. Therefore, the code would be better written like this:

    Code:
    query = "SELECT COUNT(*) FROM Tbl_Utenti WHERE Username= @UName AND Password=@PWord "
    cmd.Parameters.AddWithQuery(@UName,TextBox1.Text)
    cmd.Parameters.AddWithQuery(@PWord,Textbox2.Text)
    cmd.CommandText = query
    
    If CInt(cmd.ExecuteNonQuery) >0 Then
      'A match was found.
    Else
     'A match was not found
    End If
    My usual boring signature: Nothing

  13. #13
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    34,056

    Re: Login Form - Access failed

    I wrote that while you were posting, so I'm not quite clear. Is it still not working with the code you posted in #8?

    Using parameters will solve a few problems, as the input will be properly sanitized/formatted. However, if there are hidden characters on the input, those would still cause trouble. Using TextBox1.Text.Trim might help, as that will get rid of whitespace on the ends, but if the data in the database is where the hidden characters are located, then that won't help at all.
    My usual boring signature: Nothing

  14. #14

    Thread Starter
    Lively Member
    Join Date
    Sep 2018
    Posts
    83

    Re: Login Form - Access failed

    Dear Shaggy Hiker,
    I'm still getting crazy.

    So, I modified the code in the last way you suggested - with parameters - but it gives me error on the two lines of parameters definition. So I went back to your previous version which I report below and this is still failing.

    Code:
    If TextBox1.Text IsNot "" And TextBox2.Text IsNot "" Then
    
    
                Dim dbsource As String = "Data Source=.\SQLEXPRESS;Initial Catalog=DBUtenti;Integrated Security=True;Pooling=False"
                Dim conn = New SqlConnection(dbsource)
    
                cmd = New SqlCommand
                cmd.CommandType = CommandType.Text
                cmd.Connection = conn
                conn.Open()
    
                uname = TextBox1.Text.Trim
                pass = TextBox2.Text.Trim
                query = "SELECT COUNT(*) FROM Tbl_Utenti WHERE Userlogin='" & uname & "' AND Password='" & pass & "';"
                cmd.CommandText = query
    
    
                If CInt(cmd.ExecuteNonQuery) > 0 Then
                    MsgBox("Accesso Valido",, "Accesso applicazione")
    
                    Form2.Show()
                    Dim visible1 As Object = Form2.Visible
    
                    If visible1 Then
                        Me.Hide()
                    End If
    
                Else
                    MsgBox("Accesso fallito",, "Accesso applicazione")
                    TextBox1.Clear()
                    TextBox2.Clear()
                End If
            Else
                MsgBox("Inserisci i dati per accedere", MsgBoxStyle.Critical, "Accesso applicazione")
            End If
    Please note that I execute the query on the SQL DB it is working perfectly - see attached.
    This leaves me the doubt that the connection is not working properly.

    Please help!

    A.Query.docx

  15. #15

    Thread Starter
    Lively Member
    Join Date
    Sep 2018
    Posts
    83

    Re: Login Form - Access failed

    Dear Shaggy Hiker,
    just to add something on top that could help, I added the following two lines of code:

    Code:
     Dim ex As Integer = cmd.ExecuteNonQuery()
                MsgBox(ex)
    The message box is retrieving always the value of -1 for any kind of value I put in the two textboxes...

    A.

  16. #16

    Thread Starter
    Lively Member
    Join Date
    Sep 2018
    Posts
    83

    Re: Login Form - Access failed

    Dear Shaggy Hiker,
    just modified the statement into

    Code:
    cmd.executescalar()
    and it worked properly in all the cases.

    Thanks for your support,
    A.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width