Web Analytics Made Easy -
StatCounter Access database - CodingForum

Announcement

Collapse
No announcement yet.

Access database

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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
    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

    Comment


    • #3
      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"
      '====================================================================
      House Of Proctor Genealogy

      Comment

      Working...
      X