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

Posted on 2006-03-26
Last Modified: 2008-02-01
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.



Question by:mlagrange
    LVL 5

    Accepted Solution

           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

    Author Comment

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Suggested Solutions

    Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
    Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now