Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2

Thread: Access db

  1. #1
    New to the CF scene
    Join Date
    Jul 2007
    Location
    NJ
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access db

    I've seen a lot of messages about how to post info from HTML forms to a db. I'm working on a very large db (in MS Access) and would like to know the best ('easiest) way to pull data from that db by using a search box. I don't have much experience in programming, but I do have a class in ASP.net coming in Sept. I see Javascript and VBscript used to post info to the db. Can someone help this novice??

  • #2
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,062
    Thanks
    4
    Thanked 8 Times in 8 Posts
    Most people here will use a SQL Query to grab the records that meet their criteria

    example
    SELECT Address, City, State, Zip FROM MyTable WHERE UserID LIKE 'm%'

    In that query all users who's UserID begins with an m would be returned.
    That is all that is need in the db to get the results. However, to get them to display on an ASP page you need to have a bit more info. You need to tell the page which database to open, and to put the results into either a recordset or an array to display all of the results.

    Now to make it so that this uses a variable in the query you just add the variable to the code and since this variable is going to come from a form with the method=post and the inputs name set to txtSearch we will fill in the variable Using the Request Collection

    So let's assign that value to a variable called sSQL, and have a variable called sConn which handles our database connection (either the DSN or the DSNLess connection), and an object called oRs which will be our recordset object.

    Code:
    <%
    'declare our variables 
    Dim sSearchFor
    Dim sConn
    Dim sSQL
    Dim oRs
    
    'Assign the value of sSearchFor
    sSearchFor = Request.Form("txtSearch") 
    
    'If you want to make it so that the search uses a wildcard you can add the wildcard here or at the time of assignment, or even in the SQL Query
    sSearchFor = sSearchFor & "%"
    
    'assign the DSN or DSNLess connection string for your DB 
    sConn = "my_Connection" 
    
    'assign the query variable
    sSQL = "SELECT Address, City, State, Zip FROM MyTable WHERE UserID LIKE '" & sSearchFor & "'"
    
    'Assign the Recordset object
    Set oRs = Server.CreateObject("ADODB.Recordset")
    
    'open the recordset object using the SQL query and the connection string as the two parameters that you are going to pass
    oRs.Open sSQL, sConn
    
    'Look to see if there are any matches by checking for EndOfFile
    If Not oRs.EOF Then
    
        'Let's loop through all the results
        Do Until oRs.EOF
    
            'Write the results to the page
            Response.Write oRs("Address") & "<br>"
            Response.Write oRs("City") & ", " & oRs("State") & " " & oRs("Zip") & "<br>"
            Response.Write "<hr>"
    
            'Move to the next Record 
            oRs.MoveNext
        Loop
    
    Else
         'There was no match 
         Response.Write "No results match your search"
    End If
    
    'Close the recordset and dispose of the object to save server resources
    oRs.Close
    Set oRs = Nothing
    %>

    that is the most basic way of displaying database info onto a web page using ASP classic. ASP.NET offers a bunch of easier ways to do this.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •