Looking to have Login do a DB lookup. 1

[qwert231]

(Warning, Newbie to .NET, so this may be a dumb sounding question.)
I created the basic login page, but at this point in time I am using the inherent(built-in/default) password setup I get from my books which has the username/password listed in the web.config file. I would like/have been (in ASP) using a database user/pass lookup. Any good examples of how to do this and still use the security that comes with .Net?

 

[qwert231]

K, I've looked some more (it took a lot of digging to find anybody who even mentioned this), and this is what I came up with. I originally thought that you would do a database lookup in the web config since that is where you can set credentials on simple Forms based authentication. But then I saw/realized that you can do the database comparison at the login page itself. (Duh) However, the person who mentioned this, didn't give an example. So, I am trying to put together the simplest most efficient way to get the UserID/Name/Password from my database and compare that with what was submitted. Here is what I have so far. I am pretty sure that gives me a connection. It should be a single row/record. How can I get individual fields from this record? (ie. In ASP I could do

recordSet("FieldName")

)

Dim strReq As String
Dim objDataSet As New DataSet()
Dim objConnection As OleDbConnection
Dim objAdapter As OleDbDataAdapter
strReq = "SELECT [ID], username, password FROM Customers WHERE username LIKE '" & UserName.Text &"'"
objConnection = New OledbConnection(Application("dbSQL"))
objAdapter = New OledbDataAdapter(strReq, objConnection)
objAdapter.Fill(objDataSet, "Customers")

 

[Zarcom]

Well you have your dataset filled so now you just need to look at the record that was returned.

The way you set up your sql statment there may not be anything in the dataset ie. if the username is not listed. So first check to see if there are any records at all. Then check the password field.

if objDataSet.Tables(0).Rows.Count <> 0 Then
if txtPassword.Text = objDataset.Tables(0).Rows(0).Item(1) Then
'success
else
'error
End if

One thing to note if you make strongly typed datasets then you can use the table/column names rather than the zero index based method.

To do this you need to fill the dataset schema or create and save a full dataset. One way to do this is with the designer in VS. Just an option.

HTH That'l do donkey, that'l do
Mark

 

[qwert231]

I found it easier to use the DataReader function. It can work quite like a recordset, which, for this small ammount of data, seems to be the quickest route (typing wise). Take a look at what I've got:

Sub LoginBtn_Click(Sender As Object, E As EventArgs)
    If Page.IsValid Then
        Dim dbUser, dbPass, dbId AS String
        Dim strReq As String = "SELECT ID, username, password FROM Customers WHERE username LIKE '" & UserName.Text &"'"
        Dim objConn As New OledbConnection(Application("dbSQL"))
        Dim objComm As New OleDbCommand(strReq, objConn)
        Dim objReader AS OledbDataReader

        try
         objConn.Open()
          objReader = objComm.ExecuteReader()
           Do While objReader.Read() = True
            dbUser = objReader("username") '*
            dbPass = objReader("password").Trim
            dbId = objReader("ID")
           Loop
          objReader.Close()
         objConn.Close()
        catch excep as Exception
         eMsg.text = excep.ToString()
        end try

        If UserPass.Text = dbPass Then
            Session("userName") = UserName.Text '*
            Session("id") = dbId
            Session.TimeOut = "240"
            FormsAuthentication.RedirectFromLoginPage(UserName.Text, true)
        Else
            Msg.Text = "Invalid Credentials: Please try again."
        End If
    End If
End Sub

I'll probably take out the lines I have with an * because they won't be needed, so I'll take the username field out of the query since I am already testing for it. I do the trim to the password because the production software that primarily accesses the database always adds trailing spaces to everything. I am going to be accessing this database in the entire application, therefore I left the connection string in the global.asax. Thanks.

 

[Zarcom]

That solution works perfectly. You said as far as typing wise it is faster but a datareader is faster execution wise as well. It is meant to provide a forward only fast connection to the database. Meaning it reads data very well.

Just a quick note I saw that you were using the try-catch method of error handling. In case of an error it is always beneficial to close the connection so you don't have open db connections all over. Try something like this

Try
'code goes here
Catch excep as Exception
eMsg.text = excep.ToString()
Finally
'close connection
End Try

This way no matter what the connection will be closed.
That'l do donkey, that'l do
Mark

 

[qwert231]

Thanks for the try info. Will do... done. Also, thanks for that note on speed. Still trying to learn what is quickest, but DUH, that makes sense...

 

[Zarcom]

np thanks for the star That'l do donkey, that'l do
Mark