www.shashionline.in

SQL Server 2005

This is a compiled connection strings reference list on how to connect to SQL Server 2005.

SQL Native Client ODBC Driver

Standard security

Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase; Uid=myUsername;Pwd=myPassword;

Are you using SQL Server 2005 Express? Don’t miss the server name syntax ServernameSQLEXPRESS where you substitute Servername with the name of the computer where the SQL Server 2005 Express installation resides.

Trusted Connection

Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase; Trusted_Connection=yes;

Equivalent key-value pair: “Integrated Security=SSPI” equals “Trusted_Connection=yes”

Connecting to an SQL Server instance

The syntax of specifying the server instance in the value of the server key is the same for all connection strings for SQL Server.

Driver={SQL Native Client};Server=myServerNametheInstanceName;Database=myDataBase; Trusted_Connection=yes;

Prompt for username and password

This one is a bit tricky. First you need to set the connection object’s Prompt property to adPromptAlways. Then use the connection string to connect to the database.

oConn.Properties(”Prompt”) = adPromptAlways
Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;

Enabling MARS (multiple active result sets)

Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase; Trusted_Connection=yes;MARS_Connection=yes;

Equivalent key-value pair: “MultipleActiveResultSets=true” equals “MARS_Connection=yes”

Use ADO.NET 2.0 for MARS functionality. MARS is not supported in ADO.NET 1.0 nor ADO.NET 1.1.

Encrypt data sent over network

Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase; Trusted_Connection=yes;Encrypt=yes;

Attach a database file on connect to a local SQL Server Express instance

Driver={SQL Native Client};Server=.SQLExpress;AttachDbFilename=c:mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;

Why is the Database parameter needed? If the named database have already been attached, SQL Server does not reattach it. It uses the attached database as the default for the connection.

Attach a database file, located in the data directory, on connect to a local SQL Server Express instance

Driver={SQL Native Client};Server=.SQLExpress; AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;

Why is the Database parameter needed? If the named database have already been attached, SQL Server does not reattach it. It uses the attached database as the default for the connection.

Database mirroring

If you connect with ADO.NET or the SQL Native Client to a database that is being mirrored, your application can take advantage of the drivers ability to automatically redirect connections when a database mirroring failover occurs. You must specify the initial principal server and database in the connection string and the failover partner server.

Data Source=myServerAddress;Failover Partner=myMirrorServer;Initial Catalog=myDataBase;Integrated Security=True;

There is ofcourse many other ways to write the connection string using database mirroring, this is just one example pointing out the failover functionality. You can combine this with the other connection strings options available.

SQL Native Client OLE DB Provider

Standard security

Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Uid=myUsername; Pwd=myPassword;

Are you using SQL Server 2005 Express? Don’t miss the server name syntax ServernameSQLEXPRESS where you substitute Servername with the name of the computer where the SQL Server 2005 Express installation resides.

Trusted connection

Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;

Equivalent key-value pair: “Integrated Security=SSPI” equals “Trusted_Connection=yes”

Connecting to an SQL Server instance

The syntax of specifying the server instance in the value of the server key is the same for all connection strings for SQL Server.

Provider=SQLNCLI;Server=myServerNametheInstanceName;Database=myDataBase; Trusted_Connection=yes;

Prompt for username and password

This one is a bit tricky. First you need to set the connection object’s Prompt property to adPromptAlways. Then use the connection string to connect to the database.

oConn.Properties(”Prompt”) = adPromptAlways

oConn.Open “Provider=SQLNCLI;Server=myServerAddress;DataBase=myDataBase;

Enabling MARS (multiple active result sets)

Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase; Trusted_Connection=yes;MarsConn=yes;

Equivalent key-value pair: “MultipleActiveResultSets=true” equals “MARS_Connection=yes”

Use ADO.NET 2.0 for MARS functionality. MARS is not supported in ADO.NET 1.0 nor ADO.NET 1.1.

Encrypt data sent over network

Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase; Trusted_Connection=yes;Encrypt=yes;

Attach a database file on connect to a local SQL Server Express instance

Provider=SQLNCLI;Server=.SQLExpress;AttachDbFilename=c:mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;

Why is the Database parameter needed? If the named database have already been attached, SQL Server does not reattach it. It uses the attached database as the default for the connection.

Attach a database file, located in the data directory, on connect to a local SQL Server Express instance

Provider=SQLNCLI;Server=.SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;

Why is the Database parameter needed? If the named database have already been attached, SQL Server does not reattach it. It uses the attached database as the default for the connection.

Database mirroring

If you connect with ADO.NET or the SQL Native Client to a database that is being mirrored, your application can take advantage of the drivers ability to automatically redirect connections when a database mirroring failover occurs. You must specify the initial principal server and database in the connection string and the failover partner server.

Data Source=myServerAddress;Failover Partner=myMirrorServer;Initial Catalog=myDataBase;Integrated Security=True;

There is ofcourse many other ways to write the connection string using database mirroring, this is just one example pointing out the failover functionality. You can combine this with the other connection strings options available.

SqlConnection (.NET)

Standard Security

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

Use serverNameinstanceName as Data Source to connect to a specific SQL Server instance.

Are you using SQL Server 2005 Express? Don’t miss the server name syntax ServernameSQLEXPRESS where you substitute Servername with the name of the computer where the SQL Server 2005 Express installation resides.

Standard Security alternative syntax

This connection string produce the same result as the previous one. The reason to include it is to point out that some connection string keywords have many equivalents.

Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=False;

Trusted Connection

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

Trusted Connection alternative syntax

This connection string produce the same result as the previous one. The reason to include it is to point out that some connection string keywords have many equivalents.

Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

Connecting to an SQL Server instance

The syntax of specifying the server instance in the value of the server key is the same for all connection strings for SQL Server.

Server=myServerNametheInstanceName;Database=myDataBase;Trusted_Connection=True;

Trusted Connection from a CE device

Often a Windows CE device is not authenticated and logged in to a domain. To use SSPI or trusted connection / authentication from a CE device, use this connection string.

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;User ID=myDomainmyUsername;Password=myPassword;

Connect via an IP address

Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

DBMSSOCN=TCP/IP. This is how to use TCP/IP instead of Named Pipes. At the end of the Data Source is the port to use. 1433 is the default port for SQL Server.

Enabling MARS (multiple active result sets)

Server=myServerAddress;Database=myDataBase;Trusted_Connection=True; MultipleActiveResultSets=true;

Use ADO.NET 2.0 for MARS functionality. MARS is not supported in ADO.NET 1.0 nor ADO.NET 1.1.

Attach a database file on connect to a local SQL Server Express instance

Server=.SQLExpress;AttachDbFilename=c:mydbfile.mdf;Database=dbname; Trusted_Connection=Yes;

Why is the Database parameter needed? If the named database have already been attached, SQL Server does not reattach it. It uses the attached database as the default for the connection.

Attach a database file, located in the data directory, on connect to a local SQL Server Express instance

Server=.SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;

Why is the Database parameter needed? If the named database have already been attached, SQL Server does not reattach it. It uses the attached database as the default for the connection.

Using an User Instance on a local SQL Server Express instance

The User Instance functionality creates a new SQL Server instance on the fly during connect. This works only on a local SQL Server 2005 instance and only when connecting using windows authentication over local named pipes. The purpose is to be able to create a full rights SQL Server instance to a user with limited administrative rights on the computer.

Data Source=.SQLExpress;Integrated Security=true; AttachDbFilename=|DataDirectory|mydb.mdf;User Instance=true;

To use the User Instance functionality you need to enable it on the SQL Server. This is done by executing the following command: sp_configure ‘user instances enabled’, ‘1′. To disable the functionality execute sp_configure ‘user instances enabled’, ‘0′.

Database mirroring

If you connect with ADO.NET or the SQL Native Client to a database that is being mirrored, your application can take advantage of the drivers ability to automatically redirect connections when a database mirroring failover occurs. You must specify the initial principal server and database in the connection string and the failover partner server.

Data Source=myServerAddress;Failover Partner=myMirrorServer;Initial Catalog=myDataBase;Integrated Security=True;

There is ofcourse many other ways to write the connection string using database mirroring, this is just one example pointing out the failover functionality. You can combine this with the other connection strings options available.

Asynchronous processing

A connection to SQL Server 2005 that allows for the issuing of async requests through ADO.NET objects.

Server=myServerAddress;Database=myDataBase;Integrated Security=True;Asynchronous Processing=True;

SQLXMLOLEDB

Using SQL Server Native Client provider

Provider=SQLXMLOLEDB.4.0;Data Provider=SQLNCLI;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

SQL Server 2005 specials

Context Connection

Connecting to “self” from within your CLR stored prodedure/function. The context connection lets you execute Transact-SQL statements in the same context (connection) that your code was invoked in the first place.

C#
using(SqlConnection connection = new SqlConnection(”context connection=true”))
{
connection.Open();
// Use the connection
}

VB.Net
Using connection as new SqlConnection(”context connection=true”)
connection.Open()
‘ Use the connection
End Using

The Dictionary object

In order to learn how Dictionary object works we will create a small script which will translate number 1 to 10 from English to Spanish.

translate.asp

<% SET MyDictionary=CreateObject(”Scripting.Dictionary”) MyDictionary.Add “one”,”uno” MyDictionary.Add “two”,”dos” MyDictionary.Add “three”,”tres” MyDictionary.Add “four”,”cuatro” MyDictionary.Add “five”,”cinco” MyDictionary.Add “six”,”seis” MyDictionary.Add “seven”,”siete” MyDictionary.Add “eight”,”ocho” MyDictionary.Add “nine”,”nueve” MyDictionary.Add “ten”,”diez” EnglishNumber=”four” SpanishNumber=MyDictionary.Item (EnglishNumber) Response.Write(SpanishNumber) %>

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

How the script works

• Fist we have define a Dictionary named “Mydictionary” (line 2)
• We have add to the dictionary the data corresponding to the different number in English and Spanish (lines 4 to 13).
  When adding pairs of English and Spanish numbers to the Dictionary object, the number writen in English is a Key, and the number writen in Spanish a Item.
• In line 15 we have defined a variable named EnglishNumber and we have provided a value for this variable (in red).
• In line 16 we have defined a new variable (SpanishNumber) and we have get its value from the dictionary by indicating we want to get the Item corresponding to a specific Key (EnglishNumber).
• In line 17 the translated number is send to our visitor. The response will be “cuatro”.

We may change the values in our dictionary by using this kind of code:

• MyDictionary.Key (”one”)=”1″
  In our original script the key “one” will be substitute by a new key value (”1″). The item “uno” will not be changed.
• MyDictionary.Item (”two”)=”2″
  In our original script the item corresponding to key “two” will be substitute by a new item value (”2″). The key “two” will not be changed.

We may display the number of element pairs in the dictyonary by using this code:

• MyDictionary.Count

If we want to check whether a key exists in our dictionary before responding to our visitor we will use this kind of comparisoncode

if MyDictionary.Exists (”ten”)=True then Response.Write(”this key is included in the dictionary”) lse Response.Write(”Error: no such a key in the dictionary”) end if

Example: Translation of a number from English to Spanish

This example uses most of the elements explained above.

• If there is no information posted to the script (line 6), the script will send to the visitor the form in the Sendform() subrouting (lines 34-40).
• When a request to translate a number is get the script will check whether the number corresponds to a key in the dictionary (line 25). If the response is afirmative the corresponding item is send to the visitor. In case the key does not exists, a “No translation available” response is send to the visitor (line 29).

translation.asp

<html> <title>Page under construction</title> <body><%¼br> if request.form=”" then Sendform() else SET MyDictionary=CreateObject(”Scripting.Dictionary”) MyDictionary.Add “one”,”uno” MyDictionary.Add “two”,”dos” MyDictionary.Add “three”,”tres” MyDictionary.Add “four”,”cuatro” MyDictionary.Add “five”,”cinco” MyDictionary.Add “six”,”seis” MyDictionary.Add “seven”,”siete” MyDictionary.Add “eight”,”ocho” MyDictionary.Add “nine”,”nueve” MyDictionary.Add “ten”,”diez” EnglishNumber=request.form(”EnglishNumber”) Response.Write(”English number: ” & EnglishNumber) if MyDictionary.Exists (EnglishNumber)=True then SpanishNumber=MyDictionary.Item (EnglishNumber) Response.Write(”<BR>Spanish number: ” & SpanishNumber) else Response.Write(”<BR>Spanish number: ” & “No translation available”) end if end if %> <% Sub Sendform() %> <form action=translation.asp method=post> Write a number in English<BR> <input type=text size=30 name=EnglishNumber><BR> <input type=submit Value=”Enter to my Secret Page”> </form> <% End Sub %> </body> </html>

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

Example: Password protected information

In this example keys and items are used as usernames and passwords. It is very similar to the one above.
<br>

secretpage.asp

<%¼br> if request.form=”" then Sendform() else SET MyDictionary=CreateObject(”Scripting.Dictionary”) MyDictionary.Add “John”,”123″ MyDictionary.Add “Peter”,”456″ MyDictionary.Add “Anna”,”789″ Username=request.form(”Username”) Password=request.form(”password”) if MyDictionary.Exists (Username)=True AND Password=MyDictionary.Item (Username) then SecretInfo()¼br> else Response.Write(”Error: incorrect userame or password”) end if end if %><% Sub Sendform() %> <form action=secretpage.asp method=post> Username: <input type=text size=30 name=Username><BR> Password: <input type=password size=30 name=Password><BR> <input type=submit Value=”Submit”> </form> <% End Sub %> <% Sub SecretInfo() %> <html> <head> <title>My Secret Page</title> </head> <body bgcolor=FFFFFF> <center> <h1>This is my secret info</h1> Hello !<BR> Do you want to be my friend? </center> </body> </html> <% End Sub %>

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

Displaying the Current Date and Time

The date and time described in this section are those that are on the server.

Date

To display the current date by itself in a Web page, type:

<% =date %>

at the point where you want it to appear. When you view the page in your browser, you should see something like this:Thu, Jan 23, 1997

Note: Even though “=date” is a short script, it’s actually made up of two parts. The “date” part tells the server, “Get me the date.” The equal sign (=) tells the server to display the date in the Web page. If you typed just:

<% date %>

the server would get the current date from your system, but that’s all. It wouldn’t display it. There are times when it makes sense to use an ASP function without the equal sign.

Time

To display the current time by itself, type:

<% =time %>

where you want it to appear. When you view the page, you should see something like this:4:19:46 PM

Now (Date and Time)

To display the current date and time, type:

<% =now %>

where you want them to appear. When you view the page, you should see something like this:1/23/97 4:19:46 PM

Changing the Way Date and Time are Displayed

You can also use Active Server Pages (ASP) functions to customize the way the current date and time are displayed on your Web page. To do this, use the now function together with the following formatting functions.

Month and Monthname

To display the number of the current month in a Web page, type:

<% =month(now) %>

where you want it to appear. When you view the page in your browser, you’ll see a 1 if the current month is January, 2 if it’s February, and so on.To display the name of the current month, type:

<% =monthname(month(now)) %>

where you want it to appear.

Day

To display the day of the current month, type:

<% =day(now) %>

where you want it to appear. When you view the page, you’ll see a number between 1 and 31.

Year

To display the current year, type:

<% =year(now) %>

where you want it to appear.

Example

Suppose you wanted to display today’s date as day/month/year instead of month/day/year. To do so, you would use the day, month, and year ASP functions together, by typing:

<% =day(now) %>/<% =month(now) %>/<% =year(now) %>

When you viewed the page, you would see something like this:23/1/1997

Later we’ll see how you can change this so only the last two digits of the year are displayed, like this:

23/1/97

Weekday and Weekdayname

To display the day of the week as a number from 1 to 7 in a Web page, type:

<% =weekday(now) %>

where you want it to appear. When you view the page in Internet Explorer, you’ll see a 1 if today is Sunday, 2 if it’s Monday, and so on.To display the day of the week by name, type:

<% =weekdayname(weekday(now)) %>

where you want it to appear.

Hour, Minute, and Second

To display just the hour part of the current time, type:

<% =hour(now) %>

where you want it to appear. The hour function is based on a 24-hour clock. When you view the page, you’ll see a number between 0 and 23.To display just the minutes part of the current time, type:

<% =minute(now) %>

where you want it to appear. When you view the page, you’ll see a number between 0 and 59.To display just the seconds part of the current time, type:

<% =second(now) %>

where you want it to appear. When you view the page, you’ll see a number between 0 and 59.

Example

Try typing this into a Web page:

The time is <% =time %>.

That means it's <% =minute(now) %>  minutes past <% =hour(now) %> o'clock.

When you view the page in Internet Explorer, you should see something like this:The time is 1:36:05 PM. That means it’s 36 minutes past 13 o’clock.

Remember, the hour function is based on a 24-hour clock. Later we’ll see how to convert from the 24-hour clock to a 12-hour clock.

Timevalue

You probably won’t ever use the timevalue function. It takes the different ways you can write the time, such as “2:24PM” and “14:24,” and returns them in this format: “2:24:00 PM.” This can be useful if you’re using a function that needs to be given the time in that exact format.

Example

Earlier in this section we saw how you can use the hour, minute, and second functions to break up the time into hours, minutes, and seconds. With the timevalue function, you can put them back together. Type this into a Web page:

When it's 23 minutes and 5 seconds past 4 o'clock in the afternoon,

that means it's <% =timevalue("16:23:05") %>.

This is the same as <% =timevalue("4:23:05PM") %> or

<% =timevalue("16:23:05PM") %>.

Make sure you type “16:23:05PM” and not “16:23:05 PM.” The “05″ and the “PM.” should be run together, not separated by a space. When you view the page in Internet Explorer, you should see:When it’s 23 minutes and 5 seconds past 4 o’clock in the afternoon, that means it’s 4:23:05 PM. This is the same as 4:23:05 PM or 4:23:05 PM.

Displaying Text

len

The len function tells you how many characters are in a word or sequence of words.

(The name “len” is an abbreviation of “length.”) All characters are counted, including the space character. For example, to find the length of the sentence “The cat is on the mat,” type this into a Web page:

There are <% =len("The cat is on the mat.") %> characters

in  "The cat is on the mat."

When you view the page in Internet Explorer, you should see this:There are 22 characters in “The cat is on the mat.”

left

You can use the left function to look at the first few characters of a word or sequence of words. For example, to find the first character of “Frankenstein,” type this into a Web page:

"Frankenstein" begins with the letter <% =left("Frankenstein", 1) %>.

When you view the page, you should see this:”Frankenstein” begins with the letter F.

right

To look at the last few characters of a word or sequence of words, use the right function. For example, to find the last three letters of “Wednesday,” type this into a Web page:

The last three letters of "Wednesday" are: <% =right("Wednesday", 3) %>.

When you view this page, you should see this:The last three letters of “Wednesday” are: day.

Example

What if you wanted to take a few letters from the middle of something? How would you specify exactly where in the middle you wanted to be? For example, how would you take out just the “apple” part of the word “pineapples”?You could start with the fifth character from the left and then stop at the second character from the right. Or you could do it the following way.

Try typing this into a Web page:

<% =right("pineapples", 6) %> <% =left(right("pineapples", 6), 5) %>

This line takes the last six letters of the word “pineapples,” which make up the word “apples.” Then it takes the first five letters of the word “apples,” which make up the word “apple.”When you view this page in Internet Explorer, you should see this:

apples apple




Then try typing this into a Web page:

<% =left("pineapples", 9) %> <% =right(left("pineapples", 9), 5) %>

This line takes the first nine letters of the word “pineapples,” which make up the word “pineapple.” Then it takes the last five letters of the word “pineapple,” which make up the word “apple.”When you view this page, you should see this:

pineapple apple

Cool Things You Can Do with Date, Time, and Text

Here are some examples of interesting things you can do with date, time, and text functions.

Link of the Day

What if you wanted to have a link that pointed to a different page every day of the week? Here’s how you can do that. First, choose the pages (HTML files) on your Web site that you want your link to point to. Name them “Sunday.htm,” “Monday.htm,” and so on. (If you don’t have seven different HTML files, you can copy some of the files or make aliases on your Macintosh to them. The important thing is that there has to be one file or alias for every day of the week.)To make the link, type

<a href= <% =weekdayname(weekday(now)) %>.htm>Link of the Day</a>

where you want it to appear. When you click this link in Internet Explorer, it will take you to today’s page.

Another Way to Display Today’s Date

Earlier we saw how to change the date display from month/day/year to day/month/year like this:23/1/1997. We can also change the date display so only the last two digits of the year are included. To do this, type

<% =day(now) %>/<% =month(now) %>/<% =Right((year(now)), 2) %>

Now when you view the page, you should see something like this:23/1/97

Another Way to Display the Time

In an earlier example, we wrote a server-side script to display the current time in words, such as: “The time is 36 minutes and 5 seconds past 13 o’clock.” This script used the ASP hour function, which returns just the hour part of the current time, based on a 24-hour clock.In this example, we’ll see how to change 24-hour clock times such as “13 o’clock” to 12-hour clock times (”1 o’clock PM”). To do this, we’ll need to make the server-side script that uses the hour function a little more complicated. Instead of

<% =hour(now) %> o'clock

we’ll need to write a script that looks at the hour and does one of the following:

• If the hour is 0 (zero), the script displays “midnight.”
• If the hour is 12, the script displays “noon.”
• If the hour is between 1 and 11, the script doesn’t change it, but it displays “AM” after “o’clock.”
• If the hour is between 13 and 23, the script subtracts 12 (to make it a number between 1 and 11) and displays “PM” after “o’clock.”

The script is shown below. It isn’t written quite the way a programmer would write it, but it works, and it’s fairly easy to understand, since it follows the items in the bulleted list above exactly.

The hour is  <% if hour(now) = 0 then %>    midnight.

<% end if     if hour(now) = 12 then %>   noon. <% end if

if (hour(now) >= 1) and (hour(now) <= 11) then %>

<% =hour(now) %> o'clock AM. <% end if

if (hour(now) >= 13) and (hour(now) <= 23) then %>

<% =hour(now) - 12 %> o'clock PM. <% end if %>

If you type (or better yet, cut-and-paste) this script in a Web page, when you view the page, you should see something like this:The hour is 4 o’clock PM.

Cookies method is very similar to Session method: the basic difference is that with Cookies method the information is save in the clients computer and not in the server, so it is more suitable for sites with a lot of visitors. This method implies sending information to the client and requesting it whenever the information is needed. Additionally, we will learn how to delete the information save in the clients computer when it is not necessary anymore.When the visitor gets to our asp file we may save information related with him in his computer. The order will be like this one:

<% response.Cookies (”whatever”)=”information” %>

When this line is executed, the visitor will have the information in his computer, and whenever we need that information, we may request it using this code:

<% =request.Cookies (”whatever”) %>
or
<% variable1=request.Cookies (”whatever”) %>

Let’s try an example using Cookies method: let’s consider we have visitors checking our site several times and we want to let them know how many times they have accessed to our computer.

cookiesexample.asp

<% If Request.Cookies (”NumberVisits”)=”" Then %> <% Response.Cookies (”NumberVisits”)=1 %> This is your first visit to this page. Welcome. <% Else %> <% VarNumberVisits=Request.Cookies (”NumberVisits”) VarNumberVisits=VarNumberVisits+1 Response.Cookies(”NumberVisits”)=VarNumberVisits %> Welcome back to this page. You have visited this page <% =VarNumberVisits %> times. <BR>Check my great links <BR>….. <BR>…..<% End If %>

Cookies method may be used to show visitors specific information we have requested throw a form, as for example a list of links related to a specific theme, information to allow access to the content of a page or to personalize the page (background color, frames or not frames…), information to fill a form automatically, etc.

In this tutorial we will create a regular HTML page with a small javascript code, and we will use this javascript code to include in the page new information from a “.asp” file.

First, let´s check this two pages:
<br>

javascript.html
<html> <title>My page</title> <body><script language=”javascript” src=”javascript.asp“></script></body> </html>	1 2 3 4 5 6 7 8

javascript.asp
document.write (”hello“)	1

In the first file (javascript.html) we have include a javascript code in red, and within the code the file name from which we will get information to complete our page (src: source). So that we are asking for information to complete our page (javascript.html) to a different page (javascript.asp). This time we have only include the file name, but we may use the complete url even from a different site (for example: http://www.shashionline.in/javascript.asp).

In the second file (javascript.asp) we have include the information necessary to write in the document the word “hello”. This time we have use “.asp” extensión for the second page even though other extensions are possible.

The resulting page in our example will be like this one:

javascript.html (resulting page)

hello

So we already know we are able to include a text generated in one page within a different one. As the information we are including can be generate within a “.asp” file, we can add information dinamically by using Active Server Pages.

Let´s try two examples:

A very rudimentary banner rotator system
A simple text hit counter

In case you are using “.asp” files whithin a site and the origen of the information we want to include in the page is originated in the same site, using any of those system is not very convenient: the number of conections to the server will increase, and there may be an important delay. In case you want to include information obtained from an asp script in several pages but you do not want to copy the asp script in all of them, you may use the Include instruction, so that the script is only in one page, and by changing it you will change the results in several pages.

A very rudimentary banner rotator system

Our page is “mypage.html” and we are requesting information to complete it from “http://www.shashionline.in/adrotator.asp” which may be located in the same or in a different site. The information provided by the second file will determinate the ad to be display in our page.

mypage.html
<html> <title>My page</title> <body><script language=”javascript” src=”http://www.shashionline.in/adrotator.asp“></script></body> </html>	1 2 3 4 5 6 7 8

adrotator.asp

<%br> if session(”ad”)=”" then <br> session(”ad”)=0 end if if session(”ad”)=5 then <br> session(”ad”)=1 <br> else <br> session(”ad”)=session(”ad”)+1 <br> end if <br> %> <% Select Case session(”ad”) %> <% case 1 %> document.write (”<A HREF=http://www.1shashionline.in”>) document.write (”<IMG SCR=1.gif>”) document.write (”</A>”) <% case 2 %> document.write (”<A HREF=http://www.2shashionline.in”>) document.write (”<IMG SCR=2.gif>”) document.write (”</A>”) <% case 3 %> document.write (”<A HREF=http://www.3shashionline.in”>) document.write (”<IMG SCR=3.gif>”) document.write (”</A>”) <% case 4 %> document.write (”<A HREF=http://www.4shashionline.in”>) document.write (”<IMG SCR=4.gif>”) document.write (”</A>”) <% case 5 %> document.write (”<A HREF=http://www.4shashionline.in”>) document.write (”<IMG SCR=4.gif>”) document.write (”</A>”) <% End select %>

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

• Lines 2-3. Each time a new visitor gets to “http://www.shashionline.in/adrotator.asp” a new session will be open, and by using the session method, we will define session(”ad”) value to 0. In case the visitor is not new, the value for session(”ad”) will exits, and that value will be keep.
• Lines 6-10. The value for Session(”ad”) is increased by one unless that value is 5 (which is the maximum value allowed in this script)
• Lines 13-34. By using Select_case the page will return the information necessary to display the appropiate ad at “mypage.html”. The first time a visitor accesses our page the ad display will be the first one, the second time ad number two and so on. After 5 visits, the process will start again. We may easily increase the number of ads just by adding more obtions within Select_case and setting the maximum number from 5 to the new maximum in line 6.

NOTE: when writing the javascript code you must be very carefull: do not include brakets within the brakets in the javascript code
p.e.: Correct: document.write (”<A HREF=http://www.4shashionline.in”>)
Incorrect: document.write (”<A HREF=“http://www.4shashionline.in““>)
You may include in the response as many lines like the ones above or any other javascript code.A simple text hit counter

Our page is “mypage.html” and we are requesting information to complete it from “http://www.shashionline.in/hitcounter.asp” which may be located in the same or in a different site. The information provided by the second file will allow to get a text with the number of hits in our page.
<br>

mypage.html
<html> <title>My page</title> <body><script language=”javascript” src=”http://www.shashionline.in/hitcounter.asp“></script></body> </html>	1 2 3 4 5 6 7 8

hitcounter.asp
<%br> Wfile=”c:mydircgi-binhitcounter.txt“Set fs = CreateObject(”Scripting.FileSystemObject”) Set a = fs.OpenTextFile(Wfile) hits = Clng(a.ReadLine) hits = hits + 1 a.closeSet a = fs.CreateTextFile(Wfile,True) a.WriteLine(hits) a.Close %> document. write (”Number of hits since 2002/01/01: <% =hits %>”)	1 2 3 4 5 6 7 8 9 10 11 12 13 14

• Lines 2. We will need a text file with a number as its unique content. The path to that file will be openned in variable “Wfile”. Creating this page within cgi-bin directory is a good way to prevent visitor from accesing our counter (they will get an error when trying to access a “.txt
  file within cgi-bin directory).
• Lines 4-8. We will open our file, read the content to a variable (”hits”), and in line 7 the variable will be increased by one.
• Lines 10-12 . We will create a new file with the same name (overwriting the previous file) with the content in “hits”.
• Line 14. The response page will be a javascript code containing the number of hits.

Introduction
Working with Arrays
Filtering values from a array
Creating a table from data in a string
Simple keyword search

Introduction

Instead of having our information (variables or numbers) in variables like
Mydata1, Mydata2, Mydata3 etc, by using arrays our information will be in an
unique variable. Let´s check an example:

array.asp
<html> <title>My Array</title> <body> <% DIM MyData(2,2) MyData (0,0) = “1″ MyData (0,1) = “2″ MyData (0,2) = “3″ MyData (1,0) = “4″ MyData (1,1) = “5″ MyData (1,2) = “6″ MyData (2,0) = “7″ MyData (2,1) = “8″ MyData (2,2) = “9″ Response.write (MyData (1,2)) %> </body> </html>	1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29	Line 6: In this example we have defined by using “DIM” an array named Mydata and we have defined the size of the array. We may consider the table bellow as the source of information for our array.<br> MyData 0 1 2 0 1 2 3 1 4 5 6 2 7 8 9 Lines 7-15. After defining the array we have assigned values to the array. Line 16. In the response page we will send the value assigned to MyData(1,2) in the array. The first number will be the row and the second the column, so that in our case the response page will show the value “6″

Top
Very often, we will defined an array from data obtained from a table with only one column. Let´s check an example:

array2.asp
<html> <title>My Array</title> <body><% DIM MyData(9) MyData (0) = “0″ MyData (1) = “1″ MyData (2) = “2″ MyData (3) = “3″ MyData (4) = “4″ MyData (5) = “5″ MyData (6) = “6″ MyData (7) = “7″ MyData (8) = “8″ MyData (9) = “9″ Response.write (MyData (5)) %></body> </html>	1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30	Original table for the array in the script MyData 0 1 1 4 2 7 3 3 4 4 5 5 6 6 7 7 8 8 9 9 In the response page we will send the value assigned to MyData(5) in the array. The response page will return 5.<br>

It is also possible to define an array with more dimensions as for example MyData(5,5,5,5).
Top

Working with Arrays

In the examples above we have defined all the values within the script one by one, but this assignation may be done in a different way, as it is described in the example bellow:
<br>

array3a.asp

Resulting page

<pre> <% MyArray=Array(Zero,one,two,three,four,five,six,seven,eight,nine) %>Thearray(0): <% =Thearray(0) %> Thearray(1): <% =Thearray(1) %> Thearray(2): <% =Thearray(2) %> Thearray(3): <% =Thearray(3) %> Thearray(4): <% =Thearray(4) %> Thearray(5): <% =Thearray(5) %> Thearray(6): <% =Thearray(6) %> Thearray(7): <% =Thearray(7) %> Thearray(8): <% =Thearray(8) %> Thearray(9): <% =Thearray(9) %></pre>

Thearray(0): Zero Thearray(1): pne Thearray(2): two Thearray(3): three Thearray(4): four Thearray(5): five Thearray(6): six Thearray(7): seven Thearray(8): eight Thearray(9): nine

In this example the array has been create from a string, and each component of the array has been separated by a comma. The Array method will do it for us easily.
We may also want to use a different string with a different delimiter to define the components in our array:

array3b.asp

Resulting page

<pre> <% TheText=”Zero=one=two=three=four=five=six=seven=eight=nine” Thearray=split (TheText,”=”) %>Thearray(0): <% =Thearray(0) %> Thearray(1): <% =Thearray(1) %> Thearray(2): <% =Thearray(2) %> Thearray(3): <% =Thearray(3) %> Thearray(4): <% =Thearray(4) %> Thearray(5): <% =Thearray(5) %> Thearray(6): <% =Thearray(6) %> Thearray(7): <% =Thearray(7) %> Thearray(8): <% =Thearray(8) %> Thearray(9): <% =Thearray(9) %></pre>

Thearray(0): Zero Thearray(1): pne Thearray(2): two Thearray(3): three Thearray(4): four Thearray(5): five Thearray(6): six Thearray(7): seven Thearray(8): eight Thearray(9): nine

In this example we have defined the variable TheText, and whithin this variable we have include strings separated by “=”.

In the next line, we have split the variable TheText into an array of strings (Thearray).
Split command have been used to brake TheText and “=” has been used as a delimiter to separate the substrings.

In the response page we have indicated the individual values of Thearray one by one.

It may happend to have a variable we want to split, but we do not know how many substrings we may get. In that case we may use ubound command to discover how many elements are in our array, and them we may use that value to write them by using a For-next loop (see example below).

array4.asp	Resulting page
<pre> <% TheText=”a,f,w,d,u,t,e,u,f,v,o” Thearray=split (TheText,”=”) %>How many String do I have in TheArray? <% =ubound(Thearray)+1 %><% For n=0 to ubound(Thearray) Response.write (Thearray(n) & “<BR>”) next %></pre>	How many Strings do I have in TheArray? 10a f w d u t e u f v o

Top
Filtering values from a array

In the next example we will filter the information in our array, and we will display only part of it.

array5.asp

<pre> <% dim MyArray(9) MyArray (0) = “Zero” MyArray (1) = “One” MyArray (2) = “Two” MyArray (3) = “Three” MyArray (4) = “Four” MyArray (5) = “Five” MyArray (6) = “Six” MyArray (7) = “Seven” MyArray (8) = “Eight” MyArray (9) = “Nine” %> Find strings containing “t” (case sensitive) <% =join(filter(MyArray,”t”,True,0),”,”) %> Find strings containing “t” <% =join(filter(MyArray,”t”,True,1),”,”) %> Find strings which do not contain “t” (case sensitive) <% =join(filter(MyArray,”t”,False,0),”,”) %> Find strings which do not contain “t” <% =join(filter(MyArray,”t”,False,1),”,”) %></pre>

Find strings containing “t” (case sensitive) Eight Find strings containing “t” Two,Three,Eight Find strings which do not contain “t” (case sensitive) Zero,One,Two,Three,Four,Five,Six,Seven,Nine Find strings which do not contain “t” Zero,One,Four,Five,Six,Seven,Nine

The array and the assignation of values has been done as usually, and in the second part of the script we have used some lines similar to this one:
<% =join(filter(MyArray,”t”,True,0),”,”) %>

In this lines we have filter the values at MyArray and we have join them.

filter(MyArray,”t”,True,0)
This part of the line have search for “t” in MyArray.
True means we have selected the strings containing the search string (in this case “t”).
False will indicate we are selecting the strings which do not content the search string.
0 means our search is case sensitive (a binary comparation)
1 will mean it is not a case sensitive search (a textual comparation)
join(filter(MyArray,”t”,True,0),”,”)
The complete line will join the filtered strings with the delimiter indicated (in this case “,”)

Top
Creating a table from data in a string

In order to undertand this script we will consider we have a table like the one bellow, and that this table was the original source of information we used to create our table:

Peter student Chicago 123 John teacher London 234 Sue Manager Sidney 789

From the table we got this three lines by separeting the values by commas:Peter,student,Chicago,123 John,teacher,London,234 Sue,Manager,Sidney,789 And finaly we conected the three lines by separeting the values with “/”: Peter,student,Chicago,123/ John,teacher,London,234/ Sue,Manager,Sidney,789

The string obtained was saved to a variable named Mydata in the script bellow. The resulting page will show a table like the original. This script is not limited by number of rows or columns (the maximun amount of then is calculate each time we run the script).

Createatable.asp

<% Mydata=”Peter,student,Chicago,123/John,teacher,London,234/Sue,Manager,Sidney,789″ Createtable() %><%¼br> Sub CreateTable()MyRows=split (Mydata,”/”)¼br> RowsNumber=ubound(MyRows)Response.write (”<table border=1>”) For i=0 to RowsNumber DatainRow=split (MyRows(i),”,”)¼br> NumberofDatainRow=ubound(DatainRow) Response.write (”<tr>”) For n=0 to NumberofDatainRow Response.write(”<td>” & DatainRow(n) & “</td>”) Next Response.write (”</tr>”) Next Response.write (”</table>”)End Sub %>

This script may be used for several porpouses: we may generate Mydata by filtering values from an array as shown bellow:

<%
Dim Myclients(3)
Myclients(0)=”Peter Smith,Chicago,Manager,123″
Myclients(1)=”John Smith,New York,Accountant,124″
Myclients(2)=”George Smith,Chicago,Administration,245″
Myclients(3)=”Sam Smith,Dallas,Consultant,567″SearchFor=”Chicago”

Mydata=join(filter(Myclients,SearchFor,True,1),”/”)

Createtable()
%>

This code in combination with Createtable() Subroutine in the previus example will display only the clients from Chicago. The SearchFor variable may be obtained from a form.
<br>
Top
Simple keyword search

In this example, in our first visit a form asking for a keyword will be display. After submitting the keyword Toredirect() Subroutine will be activated.

In this Subroutine we have create two arrays: Myinfo has a description of the URL located at MyURL. In case the keyword is included in the description of the site, the visitor will be redirected to the corresponding URL. Both arrays may be very very long.
<br>

search.asp

<% if request.form=”" them %> <form method=post action=search.asp> <input type=text name=keyword> <input type=Submit value=Search> </form> <%<br> else Toredirect() end if<br> %><% Sub Toredirect()dim Myinfo(4) Myinfo (0) = “Asp tutorial for beginners” Myinfo (1) = “Displaying Date Time and Text” Myinfo (2) = “Using Variables and Forms” Myinfo (3) = “If…Then and For…Next instructions” Myinfo (4) = “Do…Loop and Select…Case instructions”dim MyURL(4) MyURL (0) = “http://www.shashionline.in” MyURL (1) = “http://www.shashionline.in/Datetime.htm” MyURL (2) = “http://www.shashionline.in/Forms.htm” MyURL (3) = “http://www.shashionline.in/if_then-for_next.htm” MyURL (4) = “http://www.shashionline.in/Do_loop-Select_case.htm”Numberofpairs=ubound(Myinfo) For n=0 to Numberofpairs if inStr(Myinfo (n), request.form (”keyword”))>0 then Response.redirect(MyURL(n)) end if NextResponse.write (”The keyword has not been found”) End Sub %>

Question : How are scripts executed?
Answer : ASP provides scripting engines that execute the corresponding scripting languages on the server side. Scripts should be encoded within the “< %…. %”.

Question : Explain the building blocks of Client/Server?
Answer : The client side building block runs the client side of the application. The server side building block runs the server side of the application.

Question : Explain the POST & GET Method or Explain the difference between them.
Answer : POST METHOD:
The POST method generates a FORM collection, which is sent as a HTTP request body. All the values typed in the form will be stored in the FORM collection.
GET METHOD:
The GET method sends information by appending it to the URL (with a question mark) and stored as A Querystring collection. The Querystring collection is passed to the server as name/value pair. The length of the URL should be less than 255 characters.

Question : What are the advantages of using ASP?
Answer : HTML is used only to create static web pages (which displays information) With the use of ASP one can create dynamic web pages,where client can communicate with the web server and vice versa. Thus using ASP interactive webpages can be created.

Question : What is the maximum data can be sent as a querystring?
Answer : Maximum length is 2084 in IE…

Question : what are the tools used for editing in IIS?
Answer : IIS Metabase Explorer is a freeware tool to allow editing of the IIS metabase. Essentially this has a similar featureset to Microsoft’s MetaEdit and Metabase Explorer except that it runs on all versions of Windows from 98 through to 2003. In addition the software includes a trace window feature which allows you to track all activity in the metabase including IIS itself or any other administration tool.

Question : What is connection pooling ?
Answer : A connection pool is a set of database connections that are available for an application to use. Connection Pooling is the concept of using a connection pool for enhanced performance.

Question : How many types of cookies are available in asp?
Answer : There are 2 types of cookies Persistent and Non-persistent.

Question : What is http header?
Answer : HTTP headers expose a great deal of information about your client as well as the server you are working, the application you are designing, as well as the environment you are in (SSL, etc.).The functionality for this is held in “Request.ServerVariables”, so you only need to access that. For example, Request.ServerVariables(”ALL_HTTP”) or Request.ServerVariables(”HTTP_USER_AGENT”). You need to know the specific name of the header that you want. It is extremely simple to display all the HTTP headers in ASP. Here’s a code snippit that will do it for you: < % for each header in Request.ServerVariables response.write header & ” = ” & Request.ServerVariables(header) & ” < br>< br> ” next % > Just delete the spaces near the angle brackets and run it on IIS. You’ll get a list of all the HTTP headers along with the actual value for the header. Make sure to try this with different browsers and on different computers with different operating systems if you can. You’ll immediately see the differences.

Question : What is the difference between Server-side validation and Client-side validation?
Answer : Client side validation is processed the client side before submitting the form. The advantage of using the client side validation is , it reduces the netork trafiic , since the validation is processed in the client machine itself. Eg, email, isnumeric, isdate etc.
Server side validation is processed in the server. Some data cannot be validated in the client side and it has to be validated in the server side. Eg, Date between the two dates in the database.

Question : How long is a SessionID guaranteed to be unique?
Answer : It is unique per client. A client cannot have two sessions with the same SessionID.

Question : What are the advantages of using ASP?
Answer : Minimizes network traffic by limiting the need for the browser and server to talk to each other.

Makes for quicker loading time since HTML pages are only downloaded.

Allows to run programs in languages that are not supported by the browser.

Can provide the client with data that does not reside on the client’s machine.

Provides improved security measures since the script cannot be viewed by the browser.

Question : How to handle Error in ASP?
Answer : Using On Error Resume Next.

Question : What are the event handlers of Session Object?
Answer : Application_OnStart – This event will be fired when the first visitor hits the page.
Application_OnEnd – This event runs when the server is stoppe.

Question : What is the result of using Option Explicit?
Answer : All variables must be dimensioned before use.

Question : Which is the default Scripting Language on the client side?
Answer : JavaScript.

Question : What is a session?
Answer : A user accessing an application is known as a session.

What Are Active Server Pages?

Active Server Pages (ASPs) are Web pages that contain server-side scripts in addition to the usual mixture of text and HTML (Hypertext Markup Language) tags. Server-side scripts are special commands you put in Web pages that are processed before the pages are sent from your Personal Web Server to the Web browser of someone who’s visiting your Web site. . When you type a URL in the Address box or click a link on a Web page, you’re asking a Web server on a computer somewhere to send a file to the Web browser (sometimes called a “client”) on your computer. If that file is a normal HTML file, it looks exactly the same when your Web browser receives it as it did before the Web server sent it. After receiving the file, your Web browser displays its contents as a combination of text, images, and sounds.

In the case of an Active Server Page, the process is similar, except there’s an extra processing step that takes place just before the Web server sends the file. Before the Web server sends the Active Server Page to the Web browser, it runs all server-side scripts contained in the page. Some of these scripts display the current date, time, and other information. Others process information the user has just typed into a form, such as a page in the Web site’s guestbook.

To distinguish them from normal HTML pages, Active Server Pages are given the “.asp” extension.

What Can You Do with Active Server Pages?

There are many things you can do with Active Server Pages.

• You can display date, time, and other information in different ways.
• You can make a survey form and ask people who visit your site to fill it out, send emails, save the information to a file, etc

What Do Active Server Pages Look Like?

The appearance of an Active Server Page depends on who or what is viewing it. To the Web browser that receives it, an Active Server Page looks just like a normal HTML page. If a visitor to your Web site views the source code of an Active Server Page, that’s what they see: a normal HTML page. However, the file located in the server  looks very different. In addition to text and HTML tags, you also see server-side scripts. This is what the Active Server Page looks like to the Web server before it is processed and sent in response to a request.

What Do Server-Side Scripts Look Like?

Server-side scripts look a lot like HTML tags. However, instead of starting and ending with lesser-than ( < ) and greater-than ( > ) brackets, they typically start with <% and end with %>. The <% is called an opening tag, and the %> is called a closing tag. In between these tags are the server-side scripts. You can insert server-side scripts anywhere in your Web page–even inside HTML tags.

Do You Have to Be a Programmer to Understand Server-Side Scripting?

There’s a lot you can do with server-side scripts without learning how to program. For this reason, much of the online Help for Active Server Pages is written for people who are familiar with HTML but aren’t computer programmers.