Helpful Information

Category: ASP

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)

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:

<%@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

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.

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"
'====================================================================