/****************************************************************/ /* Document : "Classical" ASP examples */ /* Doc. Version : 2 */ /* File : classical_asp.txt */ /* Purpose : some usefull examples for the Oracle or */ /* SQL Server DBA. This textfiles describes */ /* classical ASP ( not ASP.NET ) */ /* Date : 28-02-2002 */ /* Compiled by : Albert */ /****************************************************************/ ================ 1. Introduction: ================ An ASP file normally contains HTML tags, just like an HTML file. However, an ASP file can also contain server scripts, surrounded by the delimiters <% and %>. The main thing to understand is that "Server scripts" in .asp files are executed on the server, and not in the client's browser. These scripts can contain any expressions, statements, procedures, or operators valid for the scripting language you prefer to use. Thus, the Web Server creates html content dynamically. You may use different scripting languages in ASP files. However, the default scripting language is VBScript. Most commonly used languages are VBScript and Javascript. This is how it works. The client request an .asp page. The Server processes any script in this page and returns an dynamically constructed html reponse to the client. Client Server ------------------------------ ------- request test.asp | <% if Hour(Now)< 12 then %>| | |---------------------> | Good Morning. | |Web | | <% else %> | |browser| test.htm returned | Good Day. | | | <------------------ | <% end if %> | ------ | ------------------------------ | | | | if its before 12.00 o'clock | V -----<------ Good Morning Server side script is contained in either <% %> delimiters or in a Other simple example: <%@ LANGUAGE=VBScript %> <% For i = 3 To 7 %> > Hello World!
<% Next %> ASP also uses some "intrinsic" objects, build right into the ASP architecture. You can refer to their properties and methods. These objects are: Response, Request, Session, Application and Server. Very important objects are the "Response" and "Request" objects. Let's look at a few simple examples right now: - example 1: ------------ The Write method of the ASP Response Object is used to send content to the browser. For example, the following statement sends the text "Hello World" into the body of the document. <% response.write("Hello World!") %> - example 2: ------------ - example 3: ------------ To set JavaScript as the default scripting language for a particular page you must insert a language specification at the top of the page: <%@ language="javascript"%> <% Response.Write("Hello World!") %> - example 4: ------------ <% response.write("

You can use HTML tags to format the text!

") %> <% response.write("

This text is styled with the style attribute!

") %> ============= 2. Variables: ============= A variable is used to store information. If the variable is declared outside a procedure it can be changed by any script in the ASP file. If the variable is declared inside a procedure, it is created and destroyed every time the procedure is executed. 2.1 Simple examples: -------------------- - Declare a variable: --------------------- <% dim name name="Donald Duck" response.write("My name is: " & name) %> - Declare an array: ------------------- <% Dim famname(5),i famname(0) = "Jan Egil" famname(1) = "Tove" famname(2) = "Hege" famname(3) = "Stale" famname(4) = "Kai Jim" famname(5) = "Borge" For i = 0 to 5 response.write(famname(i) & "
") Next %> - Example: loop through html headers: ------------------------------------------ <% dim i for i=1 to 6 response.write("Header " & i & "") next %> - Example: time based greeting using VBscript: ---------------------------------------------- <% dim h h=hour(now()) response.write("

" & now()) response.write(" (Norwegian Time)

") If h<12 then response.write("Good Morning!") else response.write("Good day!") end if %> - Example: time based greeting using Javascript: ------------------------------------------------ <%@ language="javascript" %> <% var d=new Date() var h=d.getHours() Respons.Write("

") Response.Write(d + " (Norwegian Time)") Response.Write("

") if (h<12) { Response.Write("Good Morning!") } else { Response.Write("Good day!") } %> 2.2 Procedures in an asp file: ------------------------------ - Call a procedure using VBscript: ---------------------------------- <% sub vbproc(num1,num2) response.write(num1*num2) end sub %>

You can call a procedure like this:

Result: <%call vbproc(3,4)%>

Or, like this:

Result: <%vbproc 3,4%>

- Call a procedure using Javascript: ------------------------------------ <%@ language="javascript" %> <% function jsproc(num1,num2) { Response.Write(num1*num2) } %>

Result: <%jsproc(3,4)%>

- How to use both VBscript and Javascript: ------------------------------------------ <% sub vbproc(num1,num2) Response.Write(num1*num2) end sub %>

Result: <%call vbproc(3,4)%>

Result: <%call jsproc(3,4)%>

When calling a VBScript or a JavaScript procedure from an ASP file written in VBScript, you can use the "call" keyword followed by the procedure name. If a procedure requires parameters, the parameter list must be enclosed in parentheses when using the "call" keyword. If you omit the "call" keyword, the parameter list must not be enclosed in parentheses. If the procedure has no parameters, the parentheses are optional. When calling a JavaScript or a VBScript procedure from an ASP file written in JavaScript, always use parentheses after the procedure name. ============================ 3. ASP Forms and user input: ============================ The Request object may be used to retrieve user information from forms. Suppose we have the following FORM:
First Name:
Last Name:

Then we want to capture the values passed through this form. User input can be retrieved in two ways: With Request.QueryString or Request.Form. 3.1 Request.QueryString: ------------------------ The Request.QueryString command is used to collect values in a form with method="get". Information sent from a form with the GET method is visible to everyone (it will be displayed in the browser's address bar) and has limits on the amount of information to send. If a user typed "Bill" and "Gates" in the form example above, the URL sent to the server would look like this: http://www.w3schools.com/simpleform.asp?fname=Bill&lname=Gates Assume that the ASP file "simpleform.asp" contains the following script: Welcome <% response.write(request.querystring("fname")) response.write(" " & request.querystring("lname")) %> The browser will display the following in the body of the document: Welcome Bill Gates 3.2 Request.Form: ----------------- The Request.Form command is used to collect values in a form with method="post". Information sent from a form with the POST method is invisible to others and has no limits on the amount of information to send. If a user typed "Bill" and "Gates" in the form example above, the URL sent to the server would look like this: http://www.w3schools.com/simpleform.asp Assume that the ASP file "simpleform.asp" contains the following script: Welcome <% response.write(request.form("fname")) response.write(" " & request.form("lname")) %> The browser will display the following in the body of the document: Welcome Bill Gates 3.3 Form Validation: -------------------- User input should be validated on the browser whenever possible (by client scripts). Browser validation is faster and you reduce the server load. You should consider using server validation if the user input will be inserted into a database. A good way to validate a form on the server is to post the form to itself, instead of jumping to a different page. The user will then get the error messages on the same page as the form. This makes it easier to discover the error. 3.4 Some more examples: ----------------------- - Example: A Form with method is get: How to interact with the user, with the Request.QueryString command.
Your name:
<% dim fname fname=Request.QueryString("fname") If fname<>"" Then Response.Write("Hello " & fname & "!
") Response.Write("How are you today?") End If %> - Example: A Form with method is post: How to interact with the user, with the Request.Form command.
Your name:
<% dim fname fname=Request.Form("fname") If fname<>"" Then Response.Write("Hello " & fname & "!
") Response.Write("How are you today?") End If %> - Example: A Form with radio buttons: How to interact with the user, through radio buttons, with the Request.Form command. <% dim cars cars=Request.Form("cars") %>

Please select your favorite car:

value="Volvo">Volvo
value="Saab">Saab
value="BMW">BMW

<% if cars<>"" then Response.Write("

Your favorite car is: " & cars & "

") end if %> 3.5 The Request and Response object methods: -------------------------------------------- Request: -------- Request.querystring Request.Form Request.cookies Request.clientcertificate Request.servervariables The first two we have seen above, in retrieving userinput. As an example of the last one: Request.Servervariables("SERVER_NAME") Response: --------- Response.Clear Response.End Response.cookies Response.Flush Response.Redirect Response.write =============== 4. ASP Cookies: =============== What is a Cookie? A cookie is often used to identify a user. A cookie is a small file that the server embeds on the user's computer. Each time the same computer requests for a page with a browser, it will send the cookie too. With ASP, you can both create and retrieve cookie values. A cookie can be used to maintain state. State is the ability to retain user information in a Web application. How to Create a Cookie: ----------------------- The "Response.Cookies" command is used to create cookies. Note: The Response.Cookies command must appear BEFORE the tag. In the example below, we will create a cookie named "firstname" and assign the value "Alex" to it: <% Response.Cookies("firstname")="Alex" %> It is also possible to assign properties to a cookie, like setting a date when the cookie should expire: <% Response.Cookies("firstname")="Alex" Response.Cookies("firstname").Expires=#May 10,2002# %> How to Retrieve a Cookie Value: ------------------------------- The "Request.Cookies" command is used to retrieve a cookie value. In the example below, we retrieve the value of the cookie named "firstname" and display it on a page: <% fname=Request.Cookies("firstname") response.write("Firstname=" & fname) %> =================================== 5. Session and Application objects: =================================== 5.1 The Session Object: ----------------------- The Session object is used to store information about, or change settings for a user session. Variables stored in the Session object hold information about one single user, and are available to all pages in one application. When you are working with an application, you open it, do some changes and then you close it. This is much like a Session. The computer knows who you are. It knows when you start the application and when you end. But on the internet there is one problem: the web server does not know who you are and what you do because the HTTP address doesn't maintain state. ASP solves this problem by creating a unique cookie for each user. The cookie is sent to the client and it contains information that identifies the user. This interface is called the Session object. The Session object is used to store information about, or change settings for a user session. Variables stored in the Session object hold information about one single user, and are available to all pages in one application. Common information stored in session variables are name, id, and preferences. The server creates a new Session object for each new user, and destroys the Session object when the session expires. When does a Session Start? A session starts when: - A new user requests an ASP file, and the Global.asa file includes a Session_OnStart procedure - A value is stored in a Session variable - A user requests an ASP file, and the Global.asa file uses the tag to instantiate an object with session scope When does a Session End? A session ends if a user has not requested or refreshed a page in the application for a specified period. By default, this is 20 minutes. If you want to set a timeout interval that is shorter or longer than the default, you can set the Timeout property. The example below sets a timeout interval of 5 minutes: <% Session.Timeout=5 %> To end a session immediately, you may use the Abandon method: <% Session.Abandon %> Note: The main problem with sessions is WHEN they should end. We do not know if the user's last request was the final one or not. So we do not know how long we should keep the session "alive". Waiting too long uses up resources on the server. But if the session is deleted too fast you risk that the user is coming back and the server has deleted all the information, so the user has to start all over again. Finding the right timeout interval can be difficult. Tip: If you are using session variables, store SMALL amounts of data in them. Store and Retrieve Session Variables: ------------------------------------- The most important thing about the Session object is that you can store variables in it. The example below will set the Session variable username to "Donald Duck" and the Session variable age to "50": <% Session("username")="Donald Duck" Session("age")=50 %> When the value is stored in a session variable it can be reached from ANY page in the ASP application: Welcome <%Response.Write(Session("username"))%> The line above returns: "Welcome Donald Duck". You can also store user preferences in the Session object, and then access that preference to choose what page to return to the user. The example below specifies a text-only version of the page if the user has a low screen resolution: <%If Session("screenres")="low" Then%> This is the text version of the page <%Else%> This is the multimedia version of the page <%End If%> Remove Session Variables: ------------------------- The Contents collection contains all session variables. It is possible to remove a session variable with the Remove method. The example below removes the session variable "sale" if the value of the session variable "age" is lower than 18: <% If Session.Contents("age")<18 then Session.Contents.Remove("sale") End If %> To remove all variables in a session, use the RemoveAll method: <% Session.Contents.RemoveAll() %> Loop Through the Contents Collection: ------------------------------------- The Contents collection contains all session variables. You can loop through the Contents collection, to see what's stored in it: <% Session("username")="Donald Duck" Session("age")=50 dim i For Each i in Session.Contents Response.Write(i & "
") Next %> Result: username age If you do not know the number of items in the Contents collection, you can use the Count property: <% dim i dim j j=Session.Contents.Count Response.Write("Session variables: " & j) For i=1 to j Response.Write(Session.Contents(i) & "
") Next %> Result: Session variables: 2 Donald Duck 50 Loop Through the StaticObjects Collection: ------------------------------------------ You can loop through the StaticObjects collection, to see the values of all objects stored in the Session object: <% dim i For Each i in Session.StaticObjects Response.Write(i & "
") Next %> 5.2 The Application Object: --------------------------- You can use the application object to share information among all users of a Web application. An application on the Web may be a group of ASP files. The ASP files work together to perform some purpose. The Application object in ASP is used to tie these files together. The Application object is used to store and access variables from any page, just like the Session object. The difference is that ALL users share one Application object, while with Sessions there is one Session object for EACH user. The Application object should hold information that will be used by many pages in the application (like database connection information). This means that you can access the information from any page. It also means that you can change the information in one place and the changes will automatically be reflected on all pages. Example: If you want to display information to all users of your Web application, you can create an administrative page that enables a specified person to enter information for all users. This page writes the information to the application object, so you can use the information on other Active Server pages. <% Application("TodaysLecture")=Request.form("lecture") %> <% Application("location")=Request.form("location") %> Now use this: Don't miss today's lecture in room <%=Application("location") %>, titled <%=Application("TodaysLecture") %> ====================== 6. #include directive: ====================== You can insert the content of one ASP file into another ASP file before the server executes it, with the #include directive. The #include directive is used to create functions, headers, footers, or elements that will be reused on multiple pages. How to Use the #include Directive Here is a file called "mypage.asp":

Words of Wisdom:

The time is:

Here is the "wisdom.inc" file: "One should never increase, beyond what is necessary, the number of entities required to explain anything." Here is the "time.inc" file: <% Response.Write(Time) %>

Words of Wisdom:

"One should never increase, beyond what is necessary, the number of entities required to explain anything."

The time is:

11:33:42 AM

To include a file in an ASP page, place the #include directive inside comment tags: or The virtual keyword: Use the virtual keyword to indicate a path beginning with a virtual directory. If a file named "header.inc" resides in a virtual directory named /html, the following line would insert the contents of "header.inc": The file keyword: Use the file keyword to indicate a relative path. A relative path begins with the directory that contains the including file. If you have a file in the html directory, and the file "header.inc" resides in html\headers, the following line would insert "header.inc" in your file: =================== 7. DATABASE ACCESS: =================== 7.1 Access through ADO: ----------------------- Microsoft® ActiveX® Data Objects are a set of Automation objects that consume the OLE DB API and allow applications to consume data from OLE DB data sources. This includes data stored in many different formats, not only SQL databases. The ActiveX Data Object (ADO) API can be used from applications written in any automation-enabled language, such as Microsoft Visual Basic®, Microsoft Visual C++®, Microsoft Visual J++®, and Microsoft Visual FoxPro®. ADO applications access data through OLE DB providers. Microsoft SQL Server™ 2000 includes a native Microsoft OLE DB Provider for SQL Server used by ADO applications to access the data in SQL Server. In SQL Server version 6.5 and earlier, ADO applications had to use the OLE DB Provider for ODBC layered over the Microsoft SQL Server ODBC driver. Although ADO applications can still use the OLE DB Provider for ODBC with the SQL Server ODBC driver, it is more efficient to only use the OLE DB Provider for SQL Server. ADO is a COM API recommended as the primary API for accessing data from general business applications, such as human resources, accounting, and marketing applications. ADO encapsulates the OLE DB API in a simplified object model that reduces application development and maintenance costs. The SQL Server OLE DB provider is the preferred provider to use in ADO applications that access SQL Server. ADO, similar to OLE DB, can access data from many sources, not just SQL databases. In SQL Server 2000, ADO supports XML document processing in addition to relational result set processing - Example 1: Show records from a SQL Server database ==================================================== <% set cn=Server.CreateObject("ADODB.Connection") cn.Provider="sqloledb" ProvStr = "Server=w2ksql;Database=Pubs;UID=sa;PWD=;" cn.Open ProvStr set rs = Server.CreateObject("ADODB.recordset") rs.Open "SELECT Companyname, Contactname FROM xyz", cn %> <%do until rs.EOF%> <%for each x in rs.Fields%> <%next rs.MoveNext%> <%loop rs.close cn.close %>
<%Response.Write(x.value)%>
- Example 2: Show records from a SQL Server database with a form: ================================================================= Suppose default.asp shows a form: ---------------------------------

Look up information from SQLServer database

Enter the id from the xyz table:

ID:

Suppose simpleform.asp contains the following: ---------------------------------------------- <% set cn=Server.CreateObject("ADODB.Connection") cn.Provider="sqloledb" ProvStr = "Server=w2ksql;Database=Pubs;UID=sa;PWD=;" cn.Open ProvStr dim x x=Request.Form("id") set rs = Server.CreateObject("ADODB.recordset") rs.Open "SELECT id, name FROM xyz where id=" & x , cn %> <%do until rs.EOF%> <%for each x in rs.Fields%> <%next rs.MoveNext%> <%loop rs.close cn.close %>
<%Response.Write(x.value)%>
- Example 3: Entry of an order in SQL Server database stored procedure: ======================================================================== Suppose default.asp shows a form: ---------------------------------

Order invoer

Enter the Order_id from the orders table:

order_id :
cust_name:
product :
quantity :
Suppose simpleform.asp contains the following: ---------------------------------------------- <% set cn=Server.CreateObject("ADODB.Connection") cn.Provider="sqloledb" ProvStr = "Server=w2ksql;Database=test;UID=sa;PWD=;" cn.Open ProvStr sqlcmd = "exec orderinvoer " dim x x=Request.Form("order_id") dim y y=Request.Form("cust_name") dim z z=Request.Form("product") dim w w=Request.Form("quantity") ' Create the remove command and set its properties Set cmd = Server.CreateObject("ADODB.Command") cmd.CommandText = sqlcmd & x &","& y &","& z &","& w cmd.ActiveConnection = cn ' Execute the command on the Active Connection cmd.Execute %> <% FOR EACH product IN Request.Form("order_id") Response.Write "Order ingevoerd #" & x & "
"& y & "
"& z & "
"& w & "
" NEXT %>
- Example 4: executing Stored Procedures ======================================== This example shows the execution of the sp_who SQL Server system stored procedure: Dim cn As New ADODB.Connection Dim cmd As New ADODB.Command Dim rs As New ADODB.Recordset cn.Provider = "sqloledb" cn.Properties("Data Source").Value = "MyServerName" cn.Properties("Initial Catalog").Value = "northwind" cn.Properties("Integrated Security").Value = "SSPI" cn.Open Cmd.ActiveConnection = cn Cmd.CommandText = "sp_who" Cmd.CommandType = adCmdStoredProc Set rs = Cmd.Execute Debug.Print rs(0) rs.Close - Example 5: Getting xml from SQL Server database ================================================= <% response.ContentType = "text/xml" set conn=Server.CreateObject("ADODB.Connection") conn.provider="sqloledb" ProvStr ="Server=w2ksql;Database=northwind;UID=sa;PWD=;" conn.Open ProvStr sql="select customerid, companyname from customers" set rs=Conn.Execute(sql) rs.MoveFirst() response.write("") response.write("") while (not rs.EOF) response.write("") response.write("" & rs("customerid") & "") response.write("" & rs("companyname") & "") response.write("") rs.MoveNext() wend rs.close() conn.close() response.write("") %> - Example 6: Show records from access database ============================================== Records in a table: ------------------- <% set conn=Server.CreateObject("ADODB.Connection") conn.Provider="Microsoft.Jet.OLEDB.4.0" conn.Open(Server.Mappath("northwind.mdb")) set rs = Server.CreateObject("ADODB.recordset") rs.Open "SELECT Companyname, Contactname FROM Customers", conn %> <%do until rs.EOF%> <%for each x in rs.Fields%> <%next rs.MoveNext%> <%loop rs.close conn.close %>
<%Response.Write(x.value)%>
Records in a where clause: -------------------------- <% set conn=Server.CreateObject("ADODB.Connection") conn.Provider="Microsoft.Jet.OLEDB.4.0" conn.Open(Server.Mappath("northwind.mdb")) set rs = Server.CreateObject("ADODB.recordset") sql="SELECT Companyname, Contactname FROM Customers WHERE CompanyName LIKE 'A%'" rs.Open sql, conn %> <%for each x in rs.Fields response.write("") next%> <%do until rs.EOF%> <%for each x in rs.Fields%> <%next rs.MoveNext%> <%loop rs.close conn.close %>
" & x.name & "
<%Response.Write(x.value)%>
7.2 Access through VB with SQL-DMO: ----------------------------------- Parts of declarations and code: -------------- Dim MySqlServer As SQLOLE.SQLServer Set MySqlServer = CreateObject("SQLOLE.SQLServer") MySqlServer.Connect ServerName:=txtServer.TEXT, - Login:=txtLogon.TEXT, - Password:=txtPassword.TEXT -------------- 'Use the SQLServer object to connect to a specific server Public goSQLServer As SQLDMO.SQLServer Public gShowServerEvents As Boolean -------------- Set oSQLServer = New SQLDMO.SQLServer oSQLServer.LoginTimeout = -1 '-1 is the ODBC default (60) seconds 'Connect to the Server If chkAuthentication Then With oSQLServer 'Use NT Authentication .LoginSecure = True 'Do not reconnect automatically .AutoReConnect = False 'Now connect .Connect txtServer.Text End With Else With oSQLServer 'Use SQL Server Authentication .LoginSecure = False 'Do not reconnect automatically .AutoReConnect = False 'Use SQL Security .Connect txtServer.Text, sUsername, sPasswd End With End If