Login code

[Derice]

I am using the login control in ASP.NET. When I run the program an error msg "Incorrect syntax near the keyword 'User' " appeared in the debugging mode. I have no idea what is the problem. Anyone know what is wrong with my code?

Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs) Handles Login1.Authenticate
        If (ValidateLogin(Login1.UserName, Login1.Password)) Then
             e.Authenticated = true
        else
            e.Authenticated = false
        End If
    End Sub

    Private Function ValidateLogin(ByVal username As String, ByVal password As String) As Boolean
        Dim boolReturn As Boolean = False
        Dim conStr As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("testingConnectionString").ConnectionString)
        Dim cmdStr As String = "SELECT username, password FROM User"
        Dim cmd As SqlCommand = New SqlCommand(cmdStr, conStr)
        Dim dr As SqlDataReader
        conStr.Open()
        dr = cmd.ExecuteReader() 'ERROR IS INDICATED HERE
        While dr.Read()
            If (username = dr("username").ToString()) And (password = dr("password")) Then
                boolReturn = True
                dr.Close()
                conStr.Close()
                Return boolReturn
            End If
        End While
        dr.Close()
        conStr.Close()
        Return boolReturn
    End Function

[Derice]

After i changed the command string to this (after i admend a Login table in database) Dim cmdStr As String = "SELECT username, password FROM Login" No error occurred, but the code is still nt working. Subsequently i discovered a new error, it says the 'reader is closed'.

[srikanthkadem]

hi,
user is sql keyword..so if u want use user as table name then in query you need to mention like [user].in your code dr.close() comes at 2 places .so it is trieng to close the dr which is already close ...romeve the second dr.close or use it in else part.ur problem will be solved..let me know if u have still queries..

[Derice]

I am wondering if the if statement is not executed will the DataReader close after the sub exit?
It is necessary to close DataReader?
I am new to mssql, asp.net as well as daniweb.
I received msg from the admin saying my thread was removed...am I posting in the right place?
Thanks

[srikanthkadem]

it is better to close the objects ...but not mandatory as clr will take care of it..if ur concern is to close dr then in else part write that line dr.close()......

[ema005]

User is a keyword in sql database make use of another variable or put squre bracket around it like this [User]. Change the Code as follows:

Private Function ValidateLogin(ByVal username As String, ByVal password As String) As Boolean
        Dim boolReturn As Boolean = False
        Dim conStr As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("testingConnectionString").ConnectionString)
        Dim cmdStr As String = "SELECT username, password FROM [User]"
        Dim cmd As SqlCommand = New SqlCommand(cmdStr, conStr)
        Dim dr As SqlDataReader
        conStr.Open()
        dr = cmd.ExecuteReader() 
        While dr.Read()
            If (username = dr("username").ToString()) And (password = dr("password")) Then
                boolReturn = True
                 Return boolReturn
            End If
        End While
        dr.Close()
        conStr.Close()
        Return boolReturn
    End FunctionProtected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs) Handles Login1.Authenticate
        If (ValidateLogin(Login1.UserName, Login1.Password)) Then
             e.Authenticated = true
        else
            e.Authenticated = false
        End If
    End Sub