Hi all,

I'm new to ASP.net forms authentication, so I'm making a simple login feature to my site, where the user enters their username, password and first name into my database. I can get this to insert correctly.

On the login screen though, when the user enters their username and password, how do I compare what they've submitted with what's stored in the database?

Many thanks,

stokefan

Usually when creating a login, I build a sql query to lookup a user based on the username and password:

Dim Sql As String = "SELECT UserID FROM LoginTable " & _
                    "WHERE UserName = '" & txtUserName.Text & "' "
                    "AND Password = '" & txtPassword.Text & "'"

Dim con As New SqlConnection(connectString)
Dim cmd As New SqlCommand(Sql, con)
Dim reader As New SqlDataReader

con.Open
reader = cmd.ExecuteReader

If Not reader.Read Then
    ' Login was unsuccessful
    ' Show error message in a label
    lblMessage.Text = "Your username or password was incorrect!"
End If
reader.Close
con.Close

: Hi all,
:
: I'm new to ASP.net forms authentication, so I'm making a simple login feature to my site, where the user enters their username, password and first name into my database. I can get this to insert correctly.
:
: On the login screen though, when the user enters their username and password, how do I compare what they've submitted with what's stored in the database?
:
: Many thanks,
:
: stokefan
:

This message was edited by stokefan at 2004-12-21 7:8:34

---

hi, many thanks for your response there.

I wonder if you could explain to me please what the code does? i.e. the SQL statement's '" bits I dont really understand.

Also, would there be any chance I could get the query in Access form instead of sql please?

Many, many thanks.

Much appreciated.

stokefan.

: Usually when creating a login, I build a sql query to lookup a user based on the username and password:
:
:

: Dim Sql As String = "SELECT UserID FROM LoginTable " & _
:                     "WHERE UserName = '" & txtUserName.Text & "' "
:                     "AND Password = '" & txtPassword.Text & "'"
: 
: Dim con As New SqlConnection(connectString)
: Dim cmd As New SqlCommand(Sql, con)
: Dim reader As New SqlDataReader
: 
: con.Open
: reader = cmd.ExecuteReader
: 
: If Not reader.Read Then
:     ' Login was unsuccessful
:     ' Show error message in a label
:     lblMessage.Text = "Your username or password was incorrect!"
: End If
: reader.Close
: con.Close
: 

:
: : Hi all,
: :
: : I'm new to ASP.net forms authentication, so I'm making a simple login feature to my site, where the user enters their username, password and first name into my database. I can get this to insert correctly.
: :
: : On the login screen though, when the user enters their username and password, how do I compare what they've submitted with what's stored in the database?
: :
: : Many thanks,
: :
: : stokefan
: :
:
:

Access form ...

Dim Sql As String = "SELECT UserID FROM LoginTable " & _
                    "WHERE UserName = '" & txtUserName.Text & "' "
                    "AND Password = '" & txtPassword.Text & "'"

Dim con As New OleDBConnection(connectString)
Dim cmd As New OleDBCommand(Sql, con)
Dim reader As New OleDBDataReader

con.Open
reader = cmd.ExecuteReader

If Not reader.Read Then
    ' Login was unsuccessful
    ' Show error message in a label
    lblMessage.Text = "Your username or password was incorrect!"
End If
reader.Close
con.Close

The SQL statement is already in Access form, since SQL is nearly universal enough. However, the SQL query ...

"SELECT UserID FROM LoginTable " & _
"WHERE UserName = '" & txtUserName.Text & "' "
"AND Password = '" & txtPassword.Text & "'"

... reads from an example database table called "LoginTable", which has at least 3 columns:

UserID (AutoNumber)
UserName (Text)
Password (Password)

The SQL statement says to return A record where UserName and Password is equal to something. In our case we are dynamically building the SQL query, based on the values of two TextBox ASP.NET controls called "txtUserName" and "txtPassword". So if someonem on the login form enters "jdoe" in the txtUserName textbox and someone enters "test" in the txtPassword textbox, the SQL query would look like this in the long run:

"SELECT UserID FROM LoginTable " & _
"WHERE UserName = 'jdoe' "
"AND Password = 'test'"

This next part executes the sql query and returns an OleDBDataReader object:

reader = cmd.ExecuteReader

Basically, this returns the results of the SQL query. If there are any results, then the data readers Read() function will return the boolean value of "True". In our code, if the Read() method returns false, then the login information was incorrect.

If Not reader.Read Then
    ' Login was unsuccessful
    ' Show error message in a label
    lblMessage.Text = "Your username or password was incorrect!"
End If

NOTE: In the code I use OleDB objects from the System.Data.OleDb namespace, because that is the only way to work with Access database. In the code you will notice that I used the variable "connectString" in the OleDbConnection objects constructor. I assumed that variable was set elsewhere in code. However, you would probably set that variable priorly in code like so:

Dim connectString As String

connectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=C:\myPath\accessFile.mdb;" & _
                "User ID=" & _
                "Password=" 

If you need to know more about the different connection strings, go to:

http://www.able-consulting.com/ADO_Conn.htm

ADO.NET database programming is an extensive subject, which I suggest picking up a good book on.