%@ LANGUAGE = VBScript %>
<% Option Explicit %>
<% Response.Expires= -1 %>
DB Basics (Add & Delete Record using Procedures) (ASP)
<%
Dim dbcnUsers ' As ADODB.Connection
//-------------------------------------
sub connectToDb
//-------------------------------------
//Example of connecting to a Database.
//Dim dbcnUsers ' As ADODB.Connection
Dim strDbPathname ' As String
Dim strConnection ' As String
strDbPathname = Server.MapPath("MyDbAsp.mdb")
strConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strDbPathname
Set dbcnUsers = Server.CreateObject("ADODB.Connection")
dbcnUsers.Open(strConnection)
response.write("Connected to database: " & strDbPathname & ".
")
end sub
//-------------------------------------
sub displayRecords
//-------------------------------------
//Example of running a SQL query.
Dim rsUsers ' As ADODB.Recordset
Dim strSql ' As String
strSql = "select * from users " _
& "order by username"
Set rsUsers = dbcnUsers.Execute( strSql ) //Returns a RecordSet
//Example of publishing the results of the query.
response.write( "SQL Query executed: " & strSql & "
" )
response.write( "" )
response.write( "List of Users
" )
response.write( "
" )
Do While Not rsUsers.EOF
response.write( "- " & rsUsers("username") & "
" )
rsUsers.MoveNext
Loop
response.write( "
" )
response.write( "" )
rsUsers.Close
Set rsUsers = Nothing
end sub
//-------------------------------------
sub insertRecords
//-------------------------------------
Dim rsUsers ' As ADODB.Recordset
Dim strSql ' As String
strSql = "insert into users (username) values ('Me')"
response.write( "SQL command: " + strSql + "
" )
Set rsUsers = dbcnUsers.Execute( strSql ) //Returns a RecordSet
end sub
//-------------------------------------
sub deleteRecords
//-------------------------------------
Dim rsUsers ' As ADODB.Recordset
Dim strSql ' As String
strSql = "delete from users where username = 'Me'"
response.write( "SQL command: " + strSql + "
" )
Set rsUsers = dbcnUsers.Execute( strSql ) //Returns a RecordSet
end sub
//-------------------------------------
sub closeConnection
//-------------------------------------
dbcnUsers.Close
Set dbcnUsers = Nothing
end sub
%>
ASP DB Basics
(Add & Delete Record using Procedures)
See the source code (ASP) for the examples.
1. Connect to database.
<%
call connectToDb //Connect to DB.
%>
2. Displaying data before adding a record.
<%
call displayRecords
%>
3. Add a record.
<%
call insertRecords
%>
4. Data records: (after adding a record)
<%
call displayRecords
%>
5. Delete the record.
<%
call deleteRecords
%>
6. Data Records: (after deleting the record)
<%
call displayRecords
%>
7. Close (Recordset & Connection)
<%
call closeConnection
%>