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 3 of 3

Thread: Access database

  1. #1
    New to the CF scene
    Join Date
    Jun 2002
    Location
    Roermond, Netherlands
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Access database

    Can someone plz tell me what the easiest way of accessing an Access database is with ASP, preferably with an example. I've tried the MSDN ASP guide and other sources, but none of the things I tried works, probably because they're made up too complicated for me to understand (I just started using ASP, but I want to make all of the code myself to keep it managable)

  • #2
    Regular Coder
    Join Date
    Jun 2002
    Posts
    344
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It all depends what you want to do with the access database....

    for example, if you have a table with all the names of people in it, you can output them to the page like this:
    Code:
    <%@Language=VBScript%>
    <%
    Set Conn = Server.CreateObject("ADODB.Connection")
    Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data Source=full_path_to_your_db.mdb"
    Conn.Open
    Set rsCount = Conn.Execute("SELECT COUNT(*) FROM TABLE_NAME")
    If rsCount(0) > 0 Then 'There are actually users in the DB
    Set rs = Conn.Execute("SELECT * FROM TABLE_NAME")
    rs.MoveFirst
    for x = 1 to rsCount(0)
    response.write(rs("FIELD_NAME") & "<br>")
    rs.MoveNext
    next
    set rs = Nothing
    Else
    Response.Write("There are no users in the database.")
    End If
    Set rsCount = Nothing
    Conn.Close
    Set Conn = Nothing
    %>

    That'll get you started...

    ~Quack

  • #3
    Regular Coder
    Join Date
    Jun 2002
    Location
    Plano, Texas
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Good Example Given Above To Get Started. The following is a sample of code I use that may be of some help. It works well with my Access 2000 Database web pages.

    Code:
    This Code Is In My Include File:
    
    
    '====================================================================
    ' Database connection string '
    Sub OpenDB	
    			Set connCP = server.createobject("adodb.connection")
    			dbName="/Accounting/Maintenance/Database/CP_Data.mdb"
    			connectCP ="provider=microsoft.jet.oledb.4.0;data source="
    			connectCP=connectCP & server.mappath(dbName)& ";"
    			connCP.open connectCP
    End Sub
    '====================================================================
    'Clean Up
    Sub CloseDB
    				Set rsCP = Nothing
    				Set connectCP = Nothing
    				Set SQLstmtCP = Nothing
    				connCP.close
    				Set connCP = Nothing
    				Set dbName = Nothing
    End Sub
    '====================================================================
    
    
    
    
    This Call Is In A "NEW / EDIT /  DELETE Record Form" Submit page:
    
    '====================================================================
    'Capture Data From Vendor Edit / New Form
    DIM ChargeID
    DIM Ingredient
    DIM UnitOfMeasure7Inch
    DIM UnitOfMeasure12Inch
    DIM UnitCost7Inch
    DIM UnitCost12Inch
    DIM DateLastChange		
    		RecordAction = request.form("RecordAction")
    		ChargeID = request.form("ChargeID")
    		Ingredient  = ucase(replace(request.form("Ingredient"),"'","''"))
    		UnitOfMeasure7Inch = request.form("UnitOfMeasure7Inch")
    		UnitOfMeasure12Inch = request.form("UnitOfMeasure12Inch")
    		UnitCost7Inch = request.form("UnitCost7Inch")
    		UnitCost12Inch = request.form("UnitCost12Inch")
    		DateLastChange = request.form("DateLastChange")				
    '====================================================================
    			Call OpenDB
    '====================================================================
    If RecordAction = "AddNew" Then 'Add New Record
    		SQLstmtCP = "INSERT INTO ChargeTable (Ingredient,UnitOfMeasure7Inch,UnitOfMeasure12Inch,UnitCost7Inch,UnitCost12Inch,DateLastChange)"
    		SQLstmtCP = SQLstmtCP & " VALUES ("
    		SQLstmtCP = SQLstmtCP & "'" & Ingredient & "',"
    		SQLstmtCP = SQLstmtCP & "'" & UnitOfMeasure7Inch & "',"
    		SQLstmtCP = SQLstmtCP & "'" & UnitOfMeasure12Inch & "',"		
    		SQLstmtCP = SQLstmtCP & "'" & UnitCost7Inch & "',"
    		SQLstmtCP = SQLstmtCP & "'" & UnitCost12Inch & "',"
    		SQLstmtCP = SQLstmtCP & "'" & DateLastChange & "'"
    		SQLstmtCP = SQLstmtCP & ")"
    		Set rsCP = connCP.execute(SQLstmtCP)
    
    ElseIf RecordAction = "EditRecord" Then 'Update Edited Record
    
    		SQLstmtCP = "UPDATE ChargeTable"
    		SQLstmtCP = SQLstmtCP & " SET Ingredient='" & Ingredient  &"',"
    		SQLstmtCP = SQLstmtCP & "UnitOfMeasure7Inch='" & UnitOfMeasure7Inch &"',"
    		SQLstmtCP = SQLstmtCP & "UnitOfMeasure12Inch='" & UnitOfMeasure12Inch &"',"		
    		SQLstmtCP = SQLstmtCP & "UnitCost7Inch='" & UnitCost7Inch &"',"
    		SQLstmtCP = SQLstmtCP & "UnitCost12Inch='" & UnitCost12Inch &"',"
    		SQLstmtCP = SQLstmtCP & "DateLastChange='" & DateLastChange &"'"
      		SQLstmtCP = SQLstmtCP & " WHERE ChargeID=" & ChargeID
      		Set rsCP = connCP.execute(SQLstmtCP)
    
    ElseIf request.querystring("RecordAction")  = "DelRecord" Then 'Delete Record
    		ChargeID = request.querystring("ChargeID")
    		SQLstmtCP = "DELETE * from ChargeTable WHERE ChargeID=" & ChargeID
    		Set rsCP = connCP.execute(SQLstmtCP)
    
    End If
    		Call CheckErrors
    		Call CloseDB
    		Response.Redirect "Charges.asp"
    '====================================================================


  •  

    Posting Permissions

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