<%@ LANGUAGE = VBScript %> <% Option Explicit %> <% Response.Expires= -1 %> ASP DB Basics(Add & Delete Record)

ASP DB Basics
(Add & Delete Record)

See the source code (ASP) for the examples.

1. Connect to database

<% //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 & ".

") %> <% //Define some variables. Dim rsUsers ' As ADODB.Recordset Dim strSql ' As String %>

2. Displaying data before adding a record.

<% //Example of running a SQL query. 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( "

" ) response.write( "

" ) %>

3. Add a record.

<% strSql = "insert into users (username) values ('Me')" response.write( "SQL command: " + strSql + "
" ) Set rsUsers = dbcnUsers.Execute( strSql ) //Returns a RecordSet %>

4. Data records: (after adding a record)

<% //Example of running a SQL query. 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( "

" ) response.write( "

" ) %>

5. Delete the record.

<% strSql = "delete from users where username = 'Me'" response.write( "SQL command: " + strSql + "
" ) Set rsUsers = dbcnUsers.Execute( strSql ) //Returns a RecordSet %>

6. Data Records: (after deleting the record)

<% //Example of running a SQL query. 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( "

" ) response.write( "

" ) %>

7. Close (Recordset & Connection)

<% //Close Recordset rsUsers.Close Set rsUsers = Nothing //Close Connection dbcnUsers.Close Set dbcnUsers = Nothing %>