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

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'.

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..

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

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()......

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
This article has been dead for over six months. Start a new discussion instead.