How to do an MS Access DLookUp() in ASP/VB.NET?

Hello - I'm a MS Access guy crossing over into ASP.NET (v2.0), and I'd like to know the best way to do a simple lookup in a table, using one value to get another, like an Access DLookUp() function.

Specifically, in the Authenticate event of a Login control, I need to go get the account # associated with that users' UserName, stored in an Access table.



Who is Participating?
       Dim prtConn As New OleDb.OleDbConnection("your odbc connection string here")
        Dim prtCmd As OleDb.OleDbCommand
        prtCmd = New OleDbCommand("SELECT count(*) FROM [Customer] WHERE username = @username and password = @password", prtConn)
        prtCmd.Parameters.Add(New OleDbParameter("@username", OleDbType.VarChar, 40))
        prtCmd.Parameters.Add(New OleDbParameter("@password", OleDbType.VarChar, 40))
        prtCmd.Parameters("@username").Value = txtusername.Text
        prtCmd.Parameters("@password").Value = txtpassword.Text
        Dim result As Integer = 0
        result = prtCmd.ExecuteScalar

        If result = 1 Then
            'found username and password in database!
        End If
mlagrangeAuthor Commented:
Here's what I wound up with:

    Protected Sub Login1_LoggedIn(ByVal sender As Object, ByVal e As System.EventArgs) Handles Login1.LoggedIn

        Dim prtConn As New Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Inetpub\wwwroot\LDSCO\App_Data\LdsBack.mdb")


        Dim prtCmd As Data.OleDb.OleDbCommand

        prtCmd = New Data.OleDb.OleDbCommand("SELECT AcctNo FROM [tblUsersHdrs] WHERE UserID = @username", prtConn)
        prtCmd.Parameters.Add(New Data.OleDb.OleDbParameter("@username", Data.OleDb.OleDbType.VarChar, 40))
        prtCmd.Parameters("@username").Value = Login1.UserName

        Dim AcctNo As Long = 0

        AcctNo = prtCmd.ExecuteScalar


        If AcctNo <> 0 Then
            Session("lngAcctNo") = AcctNo
            Session("strAcctNo") = AcctNo.ToString.PadLeft(6, "0")
            '-- error message about user not on file, but in the meantime...
            Session("lngAcctNo") = 0
            Session("strAcctNo") = "000000"
        End If

    End Sub
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.