locked
Creating login with Stored Procedure vb.net 2010 and sql server 2012 RRS feed

  • Question

  • Hello there,

    I would like to have some help here if it is possible if not that is understandable.

    I'm creating a login to my project and I want to set the users' roles, the username and password to be required when access the system, so on the main screen I can see the users' name and the roles assign to the users.

    The issue is that with the code I have, every time I try to login, it says the Username does not exist, it doesn’t find it on the database I created, and I’m not sure if what exactly is wrong on the code and if you can give me an advice I will really appreciate it.

    This is the store procedure I created:
    Create proc splogin(
    @access varchar(20),
    @username varchar(20),
    @password nvarchar(20)
    )
    as
    select * from tblUsers
    where username=@username and password=@password and access=@access
    go
    This is the method I created on vb.net:	
    Public Sub verifyinfo()
            Try
                username = txtUsername.Text
                password = txtpassword.Text
                access = CBRolEmp.Text 'Combobox with Administration persmissons
    
               
                If txtUsername.Text <> "" And txtpassword.Text <> "" And CBRolEmp.Text <> "" Then
    
                    sql = "exec splogin '" + username + "', '" + password + "', '" + access + "'"
                    conection()' connection to the database
                    com = New SqlClient.SqlCommand(sql, conexion)
                    dr = com.ExecuteReader
                    If dr.Read = True Then
                        If username = dr("username") And password = dr("password") And access = dr("access") Then
                            
                            frm_MainScreen.Show()
                            Me.Hide()
                          
                        Else
                            MsgBox("Incorrect password" & vbCrLf & "Access deny", MsgBoxStyle.Critical)
                        End If
                    Else
                        MsgBox("Username does not exist", MsgBoxStyle.Critical)
                    End If
                End If
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End Sub

    Thank you,

    Ivan.


    Ivannovish






    • Edited by Ivannovish Friday, June 16, 2017 8:29 PM
    Friday, June 16, 2017 1:39 PM

Answers

  • Hi lvannovish,

    According to your procedure, please note the Access , Username, Password order, then modify your query

    Create proc splogin(
    @access varchar(20),
    @username varchar(20),
    @password nvarchar(20)
    )
    as
    select * from tblUsers
    where username=@username and password=@password and access=@access
    go

    exec splogin 'admin','Cherry','test'

    Firstly you need to enter Access field, and username field, then password field.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, June 28, 2017 7:52 AM

All replies

  • dr =com.ExecuteReader

    The above line on the com object put a breakpoint on the line, do a QuickWatch on the com object and look at the generated T-SQL in the object. You can copy/past the T-SQL in SSMS T_SQL execution and see what the results are.

    Friday, June 16, 2017 2:01 PM
  • If it is only needed to know if the user has access, there is no  need to get all data back. 

    If it gets the data, he/she is authenticated otherwise not. 

    You can use the Executescalar instead of all that code you need with the datareader. 

    http://www.vb-tips.com/ExecuteScalarText.ASPX


    Success
    Cor

    Friday, June 16, 2017 2:13 PM
  • Thank you, I will try that once I get home from work at the end of the day.

    Regards,


    Ivannovish

    Friday, June 16, 2017 2:14 PM
  • I did what you said but honestly im not sure what is did, no info is display and i have info in the db.


    Ivannovish

    Tuesday, June 20, 2017 4:03 AM
  • Hi lvannovish,

    According to your procedure, please note the Access , Username, Password order, then modify your query

    Create proc splogin(
    @access varchar(20),
    @username varchar(20),
    @password nvarchar(20)
    )
    as
    select * from tblUsers
    where username=@username and password=@password and access=@access
    go

    exec splogin 'admin','Cherry','test'

    Firstly you need to enter Access field, and username field, then password field.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, June 28, 2017 7:52 AM
  • Hello Cherry,

    Thank you for your help, I really appreciate it, I did what you said and it worked, I'm sure that was the problem, however, when I tried to access my system with the login I created I got the same error but I trim the following textboxes and that worked, I'm sure there was also a blank space that my system was not reading and that was also an issue.

     username = txtUsername.Text.Trim
     password = txtpassword.Text.Trim
     access = CBRolEmp.Text.Trim

    Best wishes,

    lvannovish


    Ivannovish

    • Proposed as answer by Ivanoskie Thursday, August 24, 2017 7:59 PM
    Thursday, June 29, 2017 4:23 PM