<%
PW = Request.Form("pass") ' password from the loginform
UN = Request.Form("user") ' username from the loginform

SQL = "SELECT * FROM <user_table> WHERE username = '"& UN &"' AND passwd ='"& PW &"'"
Set RS = Conn.Execute(SQL)


If Not RS.EOF Then ' If the username and password exists in the database

Session("valid") = True '  Keep the valid value in session for later use
Session("uid") = RS("<userid>") ' Keep the member userid in session for later use

Response.Redirect"some_valid_members_page.asp?uid=" & Session("uid")

Else  'If the username and password NOT exists in the database, send them back to your loginpage

Response.Redirect"some_page_where_you_have_the_login_form.asp 

RS.Close
Conn.Close
Set RS = Nothing
Set Conn = Nothing
End If
%>

Dim rs, conn, sql, passwd, uname

passwd = Trim(Request.Form("password"))
uname = Trim(Request.Form("username"))

Set conn = Server.CreateObject("ADODB.Connection")
conn.Provider = "this is your connection string. Look one up at http://connectionstrings.com"

Set rs = Server.CreateObject("ADODB.Recordset")

'Select the password from the database where the supplied username exists.
sql = "SELECT userpassword FROM users WHERE username='" & uname & "'"

'Open the connection called "conn"
conn.Open()

'Open a recordset that retrieves the query with connection "conn"
rs.Open sql, conn

if Not rs.EOF then
  'If you haven't reached the end of the recordset, there must be a record!
  if StrComp(rs("userpassword"), passwd, 0) = 0 then
    'The 0 stands for case-sensitive. 1 is case-insensitive.
    'If this command equals zero, then it passed validation.
    'Give them a session to store that they logged in. This way you can check
    'at a later time if they logged in.
    Session("logged") = "True"
    'Send the user to the good pages!
    response.redirect("loggedin.asp")
  else
    'Failed to login, incorrect password.
    'Try not to let them know if they have the right username.
    'Just tell them it all failed.
    response.write("incorrect username or password.")
  end if
else
  'No records, meaning there are no users with that username.
  response.write("incorrect username or password.")
end if

rs.Close()
set rs = nothing

conn.Close()
set conn = nothing
'If you do not close the connection, they will continuously rack up, which will slow down, if not halt your program/website. Always close. Disposing of the variable (setting it to nothing) frees up space for the next user. Not required, but definitely good techniques.