[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2006-03-26
Medium Priority
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

Accepted Solution

Darth_helge earned 2000 total points
ID: 16294374
       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

ID: 16318087
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

834 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