ASP Summary

ASP Summary

This tutorial has taught you how to add server-side scripts to your web site, to make your web site more dynamic and interactive.

You have learned how to dynamically edit, change or add any content of a web page, respond to data submitted from HTML forms, access any data or databases and return the results to a browser, customize a web page to make it more useful for individual users.

For more information on ASP, please look at our ASP examples.

---

Now You Know ASP, What's Next?

The next step is to learn SQL and ADO.

SQL

SQL is a standard computer language for accessing and manipulating database systems.

SQL statements are used to retrieve and update data in a database. SQL works with database programs like MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase, and other database systems.

If you want to learn more about SQL, please visit our SQL tutorial.

ADO

ADO is a programming interface to access data in a database from a web site.

ADO uses SQL to query data in a database.

If you want to learn more about ADO, please visit our ADO tutorial.

ASP Coding Starting

An ASP file can contain text, HTML tags and scripts. Scripts in an ASP file are executed on the server

---

What you should already know

Before you continue you should have some basic understanding of the following:

• HTML / XHTML
• A scripting language like JavaScript or VBScript

If you want to study these subjects first, find the tutorials on our Home page.

---

What is ASP?

• ASP stands for Active Server Pages
• ASP is a program that runs inside IIS
• IIS stands for Internet Information Services
• IIS comes as a free component with Windows 2000
• IIS is also a part of the Windows NT 4.0 Option Pack
• The Option Pack can be downloaded from Microsoft
• PWS is a smaller - but fully functional - version of IIS
• PWS can be found on your Windows 95/98 CD

ASP Compatibility

• ASP is a Microsoft Technology
• To run IIS you must have Windows NT 4.0 or later
• To run PWS you must have Windows 95 or later
• ChiliASP is a technology that runs ASP without Windows OS
• InstantASP is another technology that runs ASP without Windows

What is an ASP File?

• An ASP file is just the same as an HTML file
• An ASP file can contain text, HTML, XML, and scripts
• Scripts in an ASP file are executed on the server
• An ASP file has the file extension ".asp"

How Does ASP Differ from HTML?

• When a browser requests an HTML file, the server returns the file
• When a browser requests an ASP file, IIS passes the request to the ASP engine. The ASP engine reads the ASP file, line by line, and executes the scripts in the file. Finally, the ASP file is returned to the browser as plain HTML

---

What can ASP do for you?

• Dynamically edit, change or add any content of a Web page
• Respond to user queries or data submitted from HTML forms
• Access any data or databases and return the results to a browser
• Customize a Web page to make it more useful for individual users
• The advantages of using ASP instead of CGI and Perl, are those of simplicity and speed
• Provide security since your ASP code can not be viewed from the browser
• Clever ASP programming can minimize the network traffic

Important: Because the scripts are executed on the server, the browser that displays the ASP file does not need to support scripting at all!

HTML Forms

HTML Forms are used to select different kinds of user input.

---

Examples

Text fields
This example demonstrates how to create text fields on an HTML page. A user can write text in a text field.

Password fields
This example demonstrates how to create a password field on an HTML page.

(You can find more examples at the bottom of this page)

---

Forms

A form is an area that can contain form elements.Form elements are elements that allow the user to enter information (like text fields, textarea fields, drop-down menus, radio buttons, checkboxes, etc.) in a form.A form is defined with the <form> tag.

<form> <input><input> </form>

---

Input

The most used form tag is the <input> tag. The type of input is specified with the type attribute. The most commonly used input types are explained below.

Text Fields

Text fields are used when you want the user to type letters, numbers, etc. in a form.

<form> First name: <input type="text" name="firstname"> <br> Last name: <input type="text" name="lastname"> </form>

How it looks in a browser:
First name:

Last name:

Note that the form itself is not visible. Also note that in most browsers, the width of the text field is 20 characters by default.

Radio Buttons

Radio Buttons are used when you want the user to select one of a limited number of choices.

<form> <input type="radio" name="sex" value="male"> Male <br> <input type="radio" name="sex" value="female"> Female </form>

How it looks in a browser:

Male


Female

Note that only one option can be chosen.

Checkboxes
Checkboxes are used when you want the user to select one or more options of a limited number of choices.

<form> I have a bike: <input type="checkbox" name="vehicle" value="Bike"> <br /> I have a car: <input type="checkbox" name="vehicle" value="Car"> <br /> I have an airplane: <input type="checkbox" name="vehicle" value="Airplane"> </form>

How it looks in a browser:

I have a bike:

I have a car:

I have an airplane:

---

The Form's Action Attribute and the Submit Button

When the user clicks on the "Submit" button, the content of the form is sent to another file. The form's action attribute defines the name of the file to send the content to. The file defined in the action attribute usually does something with the received input.

<form name="input" action="html_form_action.asp" method="get"> Username: <input type="text" name="user"> <input type="submit" value="Submit"> </form>

How it looks in a browser:
Username:

If you type some characters in the text field above, and click the "Submit" button, you will send your input to a page called "html_form_action.asp". That page will show you the received input.

---

More Examples

Checkboxes
This example demonstrates how to create check-boxes on an HTML page. A user can select or unselect a checkbox.

Radio buttons

This example demonstrates how to create radio-buttons on an HTML page.

Simple drop down box

This example demonstrates how to create a simple drop-down box on an HTML page. A drop-down box is a selectable list.

Another drop down box

This example demonstrates how to create a simple drop-down box with a pre-selected value.

Textarea

This example demonstrates how to create a text-area (a multi-line text input control). A user can write text in the text-area. In a text-area you can write an unlimited number of characters.

Create a button

This example demonstrates how to create a button. On the button you can define your own text.

Fieldset around data

This example demonstrates how to draw a border with a caption around your data.

Form Examples

Form with input fields and a submit button

This example demonstrates how to add a form to a page. The form contains two input fields and a submit button.

Form with checkboxes

This form contains two checkboxes, and a submit button.

Form with radio buttons

This form contains two radio buttons, and a submit button.

Send e-mail from a form

This example demonstrates how to send e-mail from a form.

---

Form Tags

Tag	Description
<form>	Defines a form for user input
<input>	Defines an input field
<textarea>	Defines a text-area (a multi-line text input control)
<label>	Defines a label to a control
<fieldset>	Defines a fieldset
<legend>	Defines a caption for a fieldset
<select>	Defines a selectable list (a drop-down box)
<optgroup>	Defines an option group
<option>	Defines an option in the drop-down box
<button>	Defines a push button
<isindex>	Deprecated. Use <input> instead

HTML Tags

HTML documents are text files made up of HTML elements.
HTML elements are defined using HTML tags.

---

HTML Tags

• HTML tags are used to mark-up HTML elements
• HTML tags are surrounded by the two characters < and >
• The surrounding characters are called angle brackets
• HTML tags normally come in pairs like <b> and </b>
• The first tag in a pair is the start tag, the second tag is the end tag
• The text between the start and end tags is the element content
• HTML tags are not case sensitive, <b> means the same as <B>

---

HTML Elements

Remember the HTML example from the previous page:

<html> <head> <title>Title of page</title> </head> <body> This is my first homepage. <b>This text is bold</b> </body> </html>

This is an HTML element:

<b>This text is bold</b>

The HTML element starts with a start tag: <b>

The content of the HTML element is: This text is bold
The HTML element ends with an end tag: </b>

The purpose of the <b> tag is to define an HTML element that should be displayed as bold.
This is also an HTML element:

<body> This is my first homepage. <b>This text is bold</b> </body>

This HTML element starts with the start tag <body>, and ends with the end tag </body>.The purpose of the <body> tag is to define the HTML element that contains the body of the HTML document.

---

Why do We Use Lowercase Tags?

We have just said that HTML tags are not case sensitive: <B> means the same as <b>. If you surf the Web, you will notice that plenty of web sites use uppercase HTML tags in their source code. We always use lowercase tags. Why?

If you want to follow the latest web standards, you should always use lowercase tags. The World Wide Web Consortium (W3C) recommends lowercase tags in their HTML 4 recommendation, and XHTML (the next generation HTML) demands lowercase tags.

---

Tag Attributes

Tags can have attributes. Attributes provide additional information to an HTML element.
The following tag defines an HTML table: <table>. With an added border attribute, you can tell the browser that the table should have no borders: <table border="0">Attributes always come in name/value pairs like this: name="value".
Attributes are always specified in the start tag of an HTML element.Attributes and attribute values are also case-insensitive. However, the World Wide Web Consortium (W3C) recommends lowercase attributes/attribute values in their HTML 4 recommendation, and XHTML demands lowercase attributes/attribute values.

---

Always Quote Attribute Values

Attribute values should always be enclosed in quotes. Double style quotes are the most common, but single style quotes are also allowed.In some rare situations, like when the attribute value itself contains quotes, it is necessary to use single quotes:

name='John "ShotGun" Nelson'

Start Web Design

What is an HTML File?

• HTML stands for Hyper Text Markup Language
• An HTML file is a text file containing small markup tags
• The markup tags tell the Web browser how to display the page
• An HTML file must have an htm or html file extension
• An HTML file can be created using a simple text editor

---

Do You Want to Try It?

If you are running Windows, start Notepad.

If you are on a Mac, start SimpleText.

In OSX start TextEdit and change the following preferences: Open the the "Format" menu and select "Plain text" instead of "Rich text". Then open the "Preferences" window under the "Text Edit" menu and select "Ignore rich text commands in HTML files". Your HTML code will probably not work if you do not change the preferences above!

Type in the following text:

<html> <head> <title>Title of page</title> </head> <body> This is my first homepage. <b>This text is bold</b> </body> </html>

Save the file as "mypage.htm".

Start your Internet browser. Select "Open" (or "Open Page") in the File menu of your browser. A dialog box will appear. Select "Browse" (or "Choose File") and locate the HTML file you just created - "mypage.htm" - select it and click "Open". Now you should see an address in the dialog box, for example "C:\MyDocuments\mypage.htm". Click OK, and the browser will display the page.

---

Example Explained

The first tag in your HTML document is . This tag tells your browser that this is the start of an HTML document. The last tag in your document is . This tag tells your browser that this is the end of the HTML document.

The text between the tag and the tag is header information. Header information is not displayed in the browser window.

The text between the

The text between the tags is the text that will be displayed in your browser.

The text between the and tags will be displayed in a bold font.

---

HTM or HTML Extension?

When you save an HTML file, you can use either the .htm or the .html extension. We have used .htm in our examples. It might be a bad habit inherited from the past when some of the commonly used software only allowed three letter extensions.

With newer software we think it will be perfectly safe to use .html.

---

Note on HTML Editors:

You can easily edit HTML files using a WYSIWYG (what you see is what you get) editor like FrontPage or Dreamweaver, instead of writing your markup tags in a plain text file.

However, if you want to be a skillful Web developer, we strongly recommend that you use a plain text editor to learn your primer HTML.

---

Frequently Asked Questions

Q: After I have edited an HTML file, I cannot view the result in my browser. Why?
A: Make sure that you have saved the file with a proper name and extension like "c:\mypage.htm". Also make sure that you use the same name when you open the file in your browser.

Q: I have edited an HTML file, but the changes don't show in the browser. Why?
A: A browser caches pages so it doesn't have to read the same page twice. When you have modified a page, the browser doesn't know that. Use the browser's refresh/reload button to force the browser to reload the page.

Q: What browser should I use?
A: You can do all the training with all of the well-known browsers, like Internet Explorer, Firefox, Netscape, or Opera. However, some of the examples in our advanced classes require the latest versions of the browsers.

Q: Does my computer have to run Windows? What about a Mac?
A: You can do all your training on a non-Windows computer like a Mac.

SQL Server Tips

1) How to start SQLServer in Single User Mode

At times you might want to start SQL server in a single user mode to perform some maintenance work on the server or to change server configurations or to recover a damaged database. You can accomplish this in any of the three ways given below :-

a) From Command Prompt :-

» sqlservr -m

b) From Startup Options :-

» Go to SQL Server Properties by right-clicking on the Server name in the Enterprise manager.
» Under the 'General' tab, click on 'Startup Parameters'.
» Enter a value of -m in the Parameter.

c) From Registry :-

» Go to HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer\Parameters.
» Add new string value.
» Specify the 'Name' as SQLArg(n) & 'Data' as -m.
Where n is the argument number in the list of arguments.

Caution: Be careful while editing the Registry. Incorrectly setting up Registry values can cause unpredictable behavior.

2) How to start SQL Server in Minimal Configuration Mode

Sometimes a bad configuration value can prevent SQL Server from starting. Then it won't even let you connect to SQL Server using Enterprise Manager and correct the configuration values. The only option is to start SQL Server in a minimum configuration mode and then correct the configuration values and restart the SQL Server in normal mode. Here's how you can start the SQL Server in a minimal configuration mode :-

a) From Command Prompt :-

» sqlservr -f

b) From Startup Options :-

» Go to SQL Server Properties by right-clicking on the Server name in the Enterprise manager.
» Under the 'General' tab, click on 'Startup Parameters'.
» Enter a value of -f in the Parameter.

c) From Registry :-

» Go to HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer\Parameters.
» Add new string value.
» Specify the 'Name' as SQLArg(n) & 'Data' as -f.
Where n is the argument number in the list of arguments.

Caution: Be careful while editing the Registry. Incorrectly setting up Registry values can cause unpredictable behavior.

3) How to get Output parameter value from Dynamic execution of a Query

Sometimes you have to execute a Query dynamically using Exec(@Sql). This method works fine as long as you don't want any output values from the @Sql query. But there's another method (sp_Executesql) that allows you to execute queries dynamically as well as get their output values.

The syntax :-

EXECUTE sp_executesql
 @SQLString,
 @ParmDefinition,
 @parm=@parmIN,
@parm1OUT=@parmRET1 OUTPUT,@parm2OUT=@parmRET2 OUTPUT

Example :-

DECLARE @IntVariable INT
DECLARE @SQLString nVARCHAR(500)
DECLARE @ParmDefinition nVARCHAR(500)
DECLARE @Lastlname nVARCHAR(30)

SET @SQLString = 'SELECT @LastlnameOUT = max(lname) FROM
pubs.dbo.employee WHERE job_lvl = @level'

SET @ParmDefinition = '@level tinyint, @LastlnameOUT
varchar(30) OUTPUT'
SET @IntVariable = 35
EXECUTE sp_executesql @SQLString, @ParmDefinition, @level
= @IntVariable, @LastlnameOUT=@Lastlname OUTPUT
SELECT @Lastlname

Note: sp_Executesql is a Extended Stored Procedure.

4) How to move Database/Transaction Log files

You can move Database & Transaction log files to a different location in 2 ways.

a) sp_detach_db & then sp_attach_db

» Make sure that no user is using the database.
» Exec sp_detach_db northwind
» Move the Data & log files to a different location
» EXEC sp_attach_db 'Northwind', 'c:\mssql7\northwnd.mdf', 'c:\mssql7\northwnd.ldf'

b) BACKUP and RESTORE using WITH MOVE

» Backup :-
Backup Database Northwind To Disk = 'C:\mssql7\backup\nwind.bak'
Go

» Restore :-
USE Master
Go

RESTORE Database northwind from DISK = 'c:\mssql7\backup\nwind.bak'
WITH MOVE 'Northwind' TO 'c:\mssql7\Northwnd.mdf',
MOVE 'Northwind_log' TO 'c:\mssql7\Northwnd.ldf'
Go

c) Can be used only for moving Tempdb files.

» Use ALTER Database statement to specify a different Path for the filename.

ALTER DATABASE Tempdb MODIFY FILE (NAME = Tempdev, FILENAME = 'c:\mssql7\tempdb.mdf')

ALTER DATABASE Tempdb MODIFY FILE (NAME = Templog, FILENAME = 'c:\mssql7\templog.ldf')

» Restart SQL Server and delete the old files.

5) How to Rename a Database

a) Using sp_rename

» Make sure that no user is using the database.
» Make the database in the single user mode. You can do this by using sp_dboption.

sp_dboption 'Pubs', 'single user', true

» sp_rename Pubs, Library, Database

Note : For renaming the database, you can also use sp_renamedb.

sp_renamedb Pubs, Library

» Bring back the database in multiuser mode

sp_dboption 'Library', 'single user', false

b) Using Detach & Attach

sp_detach_db @dbname = N'Pubs'
sp_attach_db @dbname = N'Library',
@filename1 = N'd:\programfiles\MSSQL7\data\Pubs_Data.MDF',
@filename2 = N'd:\program files\MSSQL7\data\Pubs_log.ldf'

Deleting table rows using Javascript

<html>
<head>
<script type="text/javascript">
function deleteRow(i){

document.getElementById('myTable').deleteRow(i)

}
</script>
</head>

<body>
<table id="myTable" border="1">
<tr>
<td>Row 1</td>
<td><input type="button" value="Delete" onclick="deleteRow(this.parentNode.parentNode.rowIndex)"></td>
</tr>
<tr>
<td>Row 2</td>
<td><input type="button" value="Delete" onclick="deleteRow(this.parentNode.parentNode.rowIndex)"></td>
</tr>
<tr>
<td>Row 3</td>
<td><input type="button" value="Delete" onclick="deleteRow(this.parentNode.parentNode.rowIndex)"></td>
</tr>
</table>
</body>
</html>

Adding table rows

<html>
<head>
<script type="text/javascript">
function insRow(){
var x=document.getElementById('myTable').insertRow(2)
var y=x.insertCell(0)
var z=x.insertCell(1)
y.innerHTML="NEW CELL1"
z.innerHTML="NEW CELL2"
}
</script>
</head>
<body>
<table id="myTable" border="1">
<tr>
<td>d</td>
<td>d</td>
</tr>
<tr>
<td>d</td>
<td>d</td>
</tr>
<tr>
<td>Row3 cell1</td>
<td>Row3 cell2</td>
</tr>
<tr>
<td>Row4 cell1</td>
<td>Row4 cell2</td>
</tr>
<tr>
<td>Row5 cell1</td>
<td>Row5 cell2</td>
</tr>
</table>
<form>
<input type="button" onclick="insRow()" value="Insert row">
</form>
</body>
</html>

ALL SQL Connection Strings

Sybase.Data.AseClient

The ASE .NET Data Provider is an add-on component to the .NET 1.1 Framework that allows you to access a Sybase Adaptive Server Enterprise (ASE) database.
Using C#
using Sybase.Data.AseClient;
AseConnection oAseConn = new AseConnection();
oAseConn.ConnectionString = "Data Source=(local);" +
"Initial Catalog=myDatabaseName;" +
"User ID=myUsername;" +
"Password=myPassword"
oAseConn.Open();


Using VB.NET

Imports System.Data.AseClient
...
Dim oAseConn As AseConnection = New AseConnection()
oAseConn.ConnectionString = "Data Source=(local);" & _
"Initial Catalog=myDatabaseName;" & _
"User ID=myUsername;" & _
"Password=myPassword"
oAseConn.Open()

For more information, see: ASE User's Guide

* MySQLDirect .NET Data Provider
CoreLab.MySql

The MySQLDirect .NET Data Provider is an add-on component to the
.NET Framework that allows you to access the MySQL database using
native MySQL network protocol or MySQL client, without going through
OLE DB or ODBC.

Using C#

using CoreLab.MySql;

MySqlConnection oMySqlConn = new MySqlConnection();
oMySqlConn.ConnectionString = "User ID=myUsername;" +
"Password=myPassword;" +
"Host=localhost;" +
"Port=3306;" +
"Database=myDatabaseName;" +
"Direct=true;" +
"Protocol=TCP;" +
"Compress=false;" +
"Pooling=true;" +
"Min Pool Size=0;" +
"Max Pool Size=100;" +
"Connection Lifetime=0";
oMySqlConn.Open();

Using VB.NET

Imports CoreLab.MySql

Dim oMySqlConn As MySqlConnection = New MySqlConnection()
oMySqlConn.ConnectionString = "User ID=myUsername;" & _
"Password=myPassword;" & _
"Host=localhost;" & _
"Port=3306;" & _
"Database=myDatabaseName;" & _
"Direct=true;" & _
"Protocol=TCP;" & _
"Compress=false;" & _
"Pooling=true;" & _
"Min Pool Size=0;" & _
"Max Pool Size=100;" & _
"Connection Lifetime=0"
oMySqlConn.Open()

For more information, see: CoreLab's MySqlDirect .NET Data Provider

* ODBC .NET Data Provider
System.Data.ODBC

The Open Database Connectivity (ODBC) .NET Data Provider is an add-on component to the .NET Framework. It provides access to native ODBC drivers the same way the OLE DB .NET Data Provider provides access to native OLE DB providers.

Note: This technology is included in version 1.1 of the .NET Framework. You need only download this, if you are running version 1.0.

For SQL Server ODBC Driver

' VB.NET
Imports System.Data.Odbc
...
Dim oODBCConnection As OdbcConnection
Dim sConnString As String = _
"Driver=;" & _
"Server=MySQLServerName;" & _
"Database=MyDatabaseName;" & _
"Uid=MyUsername;" & _
"Pwd=MyPassword"
oODBCConnection = New Odbc.OdbcConnection(sConnString)
oODBCConnection.Open()

For Oracle ODBC Driver

' VB.NET
Imports System.Data.Odbc
...
Dim oODBCConnection As OdbcConnection
Dim sConnString As String = _
"Driver={Microsoft ODBC for Oracle};" & _
"Server=OracleServer.world;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
oODBCConnection = New Odbc.OdbcConnection(sConnString)
oODBCConnection.Open()

For Access (JET) ODBC Driver

' VB.NET
Imports System.Data.Odbc
...
Dim oODBCConnection As OdbcConnection
Dim sConnString As String = _
"Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=c:somepathmydb.mdb;" & _
"Uid=Admin;" & _
"Pwd="
oODBCConnection = New Odbc.OdbcConnection(sConnString)
oODBCConnection.Open()

For Sybase System 11 ODBC Driver

' VB.NET
Imports System.Data.Odbc
...
Dim oODBCConnection As OdbcConnection
Dim sConnString As String = _
"Driver=;" & _
"SRVR=mySybaseServerName;" & _
"DB=myDatabaseName;" & _
"UID=myUsername;" & _
"PWD=myPassword"
oODBCConnection = New OdbcConnection(sConnString)
oODBCConnection.Open()

For all other ODBC Drivers

' VB.NET
Imports System.Data.Odbc
...
Dim oODBCConnection As OdbcConnection
Dim sConnString As String = "Dsn=myDsn;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
oODBCConnection = New Odbc.OdbcConnection(sConnString)
oODBCConnection.Open()

For more information, see: OdbcConnection Class and .NET Data Providers

To view Microsoft KB articles related to OdbcConnection, click here

* OLE DB .NET Data Provider
System.Data.OleDb

The Microsoft .NET Framework Data Provider for OLE DB allow you to use native OLE DB providers (e.g. Microsoft.JET.OLEDB.4.0) through COM interop to enable data access.

The Microsoft .NET Framework Data Provider for OLE DB is included in both the 1.0 and 1.1 version of the .NET Framework.

For IBM AS/400 OLE DB Provider

' VB.NET
Imports System.Data.OleDb
...
Dim oOleDbConnection As OleDbConnection
Dim sConnString As String = _
"Provider=IBMDA400.DataSource.1;" & _
"Data source=myAS400DbName;" & _
"User Id=myUsername;" & _
"Password=myPassword"
oOleDbConnection = New OleDb.OleDbConnection(sConnString)
oOleDbConnection.Open()

For JET OLE DB Provider

' VB.NET
Imports System.Data.OleDb
...
Dim oOleDbConnection As OleDbConnection
Dim sConnString As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:myPathmyJet.mdb;" & _
"User ID=Admin;" & _
"Password="
oOleDbConnection = New OleDb.OleDbConnection(sConnString)
oOleDbConnection.Open()

For Oracle OLE DB Provider

' VB.NET
Imports System.Data.OleDb
...
Dim oOleDbConnection As OleDbConnection
Dim sConnString As String = _
"Provider=OraOLEDB.Oracle;" & _
"Data Source=MyOracleDB;" & _
"User ID=myUsername;" & _
"Password=myPassword"
oOleDbConnection = New OleDb.OleDbConnection(sConnString)
oOleDbConnection.Open()

For SQL Server OLE DB Provider

' VB.NET
Imports System.Data.OleDb
...
Dim oOleDbConnection As OleDbConnection
Dim sConnString As String = _
"Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"User Id=myUsername;" & _
"Password=myPassword"
oOleDbConnection = New OleDb.OleDbConnection(sConnString)
oOleDbConnection.Open()

For Sybase ASE OLE DB Provider

' VB.NET
Imports System.Data.OleDb
...
Dim oOleDbConnection As OleDbConnection
Dim sConnString As String = _
"Provider=Sybase ASE OLE DB Provider;" & _
"Data Source=MyDataSourceName;" & _
"Server Name=MyServerName;" & _
"Database=MyDatabaseName;" & _
"User ID=myUsername;" & _
"Password=myPassword"
oOleDbConnection = New OleDb.OleDbConnection(sConnString)
oOleDbConnection.Open()

For more information, see: OleDbConnection Class and .NET Data Providers

To view Microsoft KB articles related to OleDbConnection, click here

* Oracle .NET Data Provider - From Microsoft
System.Data.OracleClient

The Microsoft .NET Framework Data Provider for Oracle is an add-on component to the .NET Framework 1.0 that provides access to an Oracle database using the Oracle Call Interface (OCI) as provided by Oracle Client software.

Oracle 8i Release 3 (8.1.7) Client or later must be installed for this provider to function correctly.

Note: This .NET Data Provider is included in version 1.1 of the .NET Framework. You need only download this, if you are running version 1.0.

Using C#:

using System.Data.OracleClient;

OracleConnection oOracleConn = new OracleConnection();
oOracleConn.ConnectionString = "Data Source=Oracle8i;" +
"Integrated Security=SSPI";
oOracleConn.Open();

Using VB.NET:

Imports System.Data.OracleClient

Dim oOracleConn As OracleConnection = New OracleConnection()
oOracleConn.ConnectionString = "Data Source=Oracle8i;" & _
"Integrated Security=SSPI";
oOracleConn.Open()

For more information, see: OracleConnection Class and .NET Data Providers

To view Microsoft KB articles related to OracleConnection, click here

* Oracle .NET Data Provider - From Oracle
Oracle.DataAccess.Client

The Oracle .NET Framework Data Provider from Oracle is an add-on component to the .NET Framework.

Using C#

using Oracle.DataAccess.Client;
...
OracleConnection oOracleConn = new OracleConnection();
oOracleConn.ConnectionString = "Data Source=MyOracleServerName;" +
"Integrated Security=SSPI";
oOracleConn.Open();

Using VB.NET

Imports Oracle.DataAccess.Client
...
Dim oOracleConn As OracleConnection = New OracleConnection()
oOracleConn.ConnectionString = "Data Source=MyOracleServerName;" & _
"Integrated Security=SSPI";
oOracleConn.Open()

For more information, see: Oracle Data Provider for .NET

* OraDirect .NET Data Provider - From CoreLab
CoreLab.Oracle

The OraDirect .NET Data Provider is an add-on component to the .NET
Framework that provides access to an Oracle database using the Oracle
Call Interface (OCI) as provided by Oracle Client software.

Using C#

using CoreLab.Oracle;

OracleConnection oOracleConn = new OracleConnection();
oOracleConn.ConnectionString = "User ID=myUsername;" +
"Password=myPassword;" +
"Host=(local);" +
"Pooling=true;" +
"Min Pool Size=0;" +
"Max Pool Size=100;" +
"Connection Lifetime=0";
oOracleConn.Open();


Using VB.NET

Imports CoreLab.Oracle

Dim oOracleConn As OracleConnection = New OracleConnection()
oOracleConn.ConnectionString = "User ID=myUsername;" & _
"Password=myPassword;" & _
"Host=(local);" & _
"Pooling=true;" & _
"Min Pool Size=0;" & _
"Max Pool Size=100;" & _
"Connection Lifetime=0"
oOracleConn.Open()

For more information, see: OraDirect .NET Data Provider

* MySQL .NET Data Provider
EID.MySqlClient

The MySQL .NET Native Provider is an add-on component to the .NET Framework that allows you to access the MySQL database through
the native protocol, without going through OLE DB or ODBC.

Using C#

using EID.MySqlClient;
...
MySqlConnection oMySqlConn = new MySqlConnection();
oMySqlConn.ConnectionString = "Data Source=(local);" +
"Database=myDatabaseName;" +
"User ID=myUsername;" +
"Password=myPassword;" +
"Command Logging=false";
oMySqlConn.Open();

Using VB.NET

Imports EID.MySqlClient
...
Dim oMySqlConn As MySqlConnection = New MySqlConnection()
oMySqlConn.ConnectionString = "Data Source=(local);" & _
"Database=myDatabaseName;" & _
"User ID=myUsername;" & _
"Password=myPassword;" & _
"Command Logging=false"
oMySqlConn.Open()

For more information, see: EID's MySQL ADO.NET native provider

* PostgreSQLDirect .NET Data Provider
CoreLab.PostgreSql

The PostgreSQLDirect .NET Data Provider is an add-on component to the
.NET Framework that allows you to access the PostgreSQL database using
native message-based protocol, without going through OLE DB or ODBC.

Using C#

using CoreLab.PostgreSql;

PgSqlConnection oPgSqlConn = new PgSqlConnection();
oPgSqlConn.ConnectionString = "User ID=myUsername;" +
"Password=myPassword;" +
"Host=localhost;" +
"Port=5432;" +
"Database=myDatabaseName;" +
"Pooling=true;" +
"Min Pool Size=0;" +
"Max Pool Size=100;" +
"Connection Lifetime=0";
oPgSqlConn.Open();



Using VB.NET

Imports CoreLab.PostgreSql

Dim oPgSqlConn As PgSqlConnection = New PgSqlConnection()
oPgSqlConn.ConnectionString = "User ID=myUsername;" & _
"Password=myPassword;" & _
"Host=localhost;" & _
"Port=5432;" & _
"Database=myDatabaseName;" & _
"Pooling=true;" & _
"Min Pool Size=0;" & _
"Max Pool Size=100;" & _
"Connection Lifetime=0"
oPgSqlConn.Open()

For more information, see: PostgreSQLDirect .NET Data Provider

* SQL Server .NET Data Provider
System.Data.SqlClient

The SQL Server .NET Data Provide allows you to connect to a Microsoft SQL Server 7.0 or 2000 database. For Microsoft SQL Server 6.5 or earlier, use the OLE DB .NET Data Provider with the "SQL Server OLE DB Provider" (SQLOLEDB).

Note: The SQL Server .NET Data Provider knows which data provider it is. Hence the "provider=" part of the connection string is not needed.

Using C#:

using System.Data.SqlClient;
...
SqlConnection oSQLConn = new SqlConnection();
oSQLConn.ConnectionString = "Data Source=(local);" +
"Initial Catalog=myDatabaseName;" +
"Integrated Security=SSPI";
oSQLConn.Open();

Using VB.NET:

Imports System.Data.SqlClient
...
Dim oSQLConn As SqlConnection = New SqlConnection()
oSQLConn.ConnectionString = "Data Source=(local);" & _
"Initial Catalog=myDatabaseName;" & _
"Integrated Security=SSPI"
oSQLConn.Open()

If connection to a remote server (via IP address):

oSQLConn.ConnectionString = "Network Library=DBMSSOCN;" & _
"Data Source=xxx.xxx.xxx.xxx,1433;" & _
"Initial Catalog=myDatabaseName;" & _
"User ID=myUsername;" & _
"Pas sword=myPassword"

Where:
- "Network Library=DBMSSOCN" tells SqlConnection to use TCP/IP Q238949
- xxx.xxx.xxx.xxx is an IP address.
- 1433 is the default port number for SQL Server. Q269882 and Q287932
- You can also add "Encrypt=yes" for encryption

For more information, see: SqlConnection Class, Q308656, and .NET Data Providers

Note: Microsoft SQLXML Managed Classes exposes the functionality of SQLXML inside the Microsoft .NET Framework.

To view Microsoft KB articles related to SQLClient, click here

Use the WebBrowser control

Title Make a Web browser that can only view certain URLs Keywords Web browser, restrict, URL Categories Utilities, Controls, Internet

Use the WebBrowser control. In its BeforeNavigate2 event handler, examine the URL. If the URL is not allowed, set Cancel to True. Also set Cancel to True in the NewWindow2 event handler so the user cannot open a link in a new window. This example allows only URLs that begin with "http://mcp-vb.blogspot.com/"

Private Sub Form_Load() WebBrowser1.Navigate "http://mcp-vb.blogspot.com/" End Sub ' Cancel any navigation that moves outside VB helper. Private Sub WebBrowser1_BeforeNavigate2(ByVal pDisp As _ Object, URL As Variant, Flags As Variant, _ TargetFrameName As Variant, PostData As Variant, _ Headers As Variant, Cancel As Boolean) Const TARGET = "http://mcp-vb.blogspot.com/" Cancel = (LCase$(Left$(URL, Len(TARGET))) <> TARGET) If Cancel Then MsgBox URL & " is blocked" End Sub ' Don't let the user open a new window. Private Sub WebBrowser1_NewWindow2(ppDisp As Object, Cancel _ As Boolean) Cancel = True MsgBox "You cannot open a new window." End Sub

Use linkserver to Excel Spreadsheet

Use the Microsoft OLE DB Provider for Jet on an Excel Spreadsheet

To create a linked server definition using the Microsoft OLE DB Provider for Jet to access an Excel spreadsheet, first create a named range in Excel specifying the columns and rows of the Excel worksheet to select. The name of the range can then be referenced as a table name in a distributed query.

EXEC sp_addlinkedserver 'ExcelSource',
  'Jet 4.0',
  'Microsoft.Jet.OLEDB.4.0',
  'd:\book1.xls',
  NULL,
  'Excel 5.0'
GO


In order to access data from an Excel spreadsheet, associate a range of cells with a name. A given named range can be accessed by using the name of the range as the table name. The following query can be used to access a named range called Sheet1 using the linked server set up as above.

SELECT * FROM ExcelSource...Sheet1$
GO


Drop That link Server:

sp_dropserver 'ExcelSource'

Toggle ShowInTaskbar at Run Time

Q. Toggle ShowInTaskbar at Run Time
In the process of making a window appear in the taskbar, why did Microsoft make the ShowInTaskbar property read-only at run time?

A. Good question. ShowInTaskbar is another property that's ideally suited for addition to the CFormBorder class, which sets many normally untouchable properties at run time (see the November 2000 Ask the VB Pro column). The Property Let/Get pair simply locks the window from update to prevent flicker, hides the window, toggles the WS_EX_APPWINDOW extended style bit, then shows and unlocks the. Use the class from your form like this:

' Border handler class
Private m_bdr As CFormBorder

Private Sub Check1_Click()
' Toggle replacement property
m_bdr.ShowInTaskbar = _
  CBool(Check1.Value)
End Sub

Private Sub Form_Load()
' Set up replacement properties
Set m_bdr = New CFormBorder
Set m_bdr.Client = Me
End Sub

Q. Obtain the Domain Controller Name
I've been looking at some of the new functions that come built into Windows 2000, and I found one I'd like to use. The DsGetDcName API looks extremely useful, but no matter what I try, I can't make sense of the pointer returned in DomainControllerInfo, the last parameter.

A. The SDK docs appear to be less than clear in this case. That parameter is documented as a "Pointer to a variable that receives a pointer to a structure... ." However, it appears that DomainControllerInfo is a pointer to a buffer that contains the data.

Given that clue, you can declare a DOMAIN_CONTROLLER_INFO consisting entirely of Longs and a GUID, and use CopyMemory to sling the API-provided bits into your local structure. You still need to dereference the strings so you can declare a more VB-friendly structure and use it as the destination for the recovered strings. Finally, you need to free the API-allocated buffer with a call to NetApiBufferFree (see Listing 3).

For more information on dereferencing API pointers, see the detailed techniques examined in the November 1999 Ask the VB Pro column. In the future, I'll discuss how I determined the SDK docs were misleading and how you can expand on this lesson to solve your own memory-based mysteries.

All ASP Server Variables

How many server variables are available for use in your .asp scripts? Display them all with this simple script.

-----------------
for each name in request.servervariables
response.write name
request.servervariables(name)
Next
-----------------

Get the Source of an .asp Document

Viewing the source of an asp document from a web browser allows only for the viewing of the output html text. Any text written in between <% %> is not outputted to the browser unless a write command is inserted. This is good for security, but bad for developers who made need to look at the code and do not have a local copy. The following script will read any text file (.asp, .html, .inc are all examples) and output it to the browser by changing the page_to_read value to a document relative to the position of the .asp code your using to read it (Example to read the index.asp document in the parent directory it should read page_to_read="../index.asp")


-------------------------------
' Target page to be read
page_to_read="view_source.asp"

' Create a server object
set fso = createobject("scripting.filesystemobject")

' Set the path to document to be read
set act = fso.opentextfile(server.mappath(page_to_read))

' Read the contents of the document to the
' read_text variable

read_text = act.readall

' Close the server object
act.close

' Write the inputted text out to the browser
' and html encode it to display as source
' enclosed in

 tags to display as read
response.write "
" & server.htmlencode(read_text) & "
"

------------------------------------

How to find Harddisk SerialNumber

How to find Harddisk SerialNumber ?

### Computer Unique No or hard drive serialNumber From Visual Basic

Private Declare Function GetVolumeInformation _
Lib "kernel32.dll" _
Alias "GetVolumeInformationA" _
(ByVal lpRootPathName As String, _
ByVal lpVolumeNameBuffer As String, _
ByVal nVolumeNameSize As Integer, _
lpVolumeSerialNumber As Long, _
lpMaximumComponentLength As Long, _
lpFileSystemFlags As Long, _
ByVal lpFileSystemNameBuffer As String, _
ByVal nFileSystemNameSize As Long) As Long

############ This Function will return SerialNumber of a Drive

Public Function GetSerialNumber(DriveLetter As String) As String

Dim SerialNum As Long
Dim VolNameBuf As String
Dim FileSysNameBuf As String

Select Case Len(DriveLetter)
Case 1
If DriveLetter Like "[a-z]" Then
DriveLetter = Left$(DriveLetter, 1) & ":\"
Else
GetSerialNumber = "Error - Bad drive designation"
End If
Case 2
If LCase(DriveLetter) Like "[a-z]:" Then
DriveLetter = DriveLetter & "\"
Else
GetSerialNumber = "Error - Bad drive designation"
End If
Case 3
If LCase(DriveLetter) Like "[!a-z]:\" Then
GetSerialNumber = "Error - Bad drive designation"
End If
Case Else
GetSerialNumber = "Error - Bad drive designation"
End Select

If Len(GetSerialNumber) = 0 Then
VolNameBuf = String$(255, Chr$(0))
FileSysNameBuf = String$(255, Chr$(0))
GetVolumeInformation DriveLetter, VolNameBuf, _
Len(VolNameBuf), SerialNum, 0, 0, _
FileSysNameBuf, Len(FileSysNameBuf)
GetSerialNumber = Right$("00000000" & Hex$(SerialNum), 8)
End If
End Function

The FileSystemObject Object

The FileSystemObject object is used to access the file system on the server. This object can manipulate files, folders, and directory paths. It is also possible to retrieve file system information with this object.

The following code creates a text file (c:\test.txt) and then writes some text to the file:

<% dim fs,fname set fs=Server.CreateObject("Scripting.FileSystemObject") set fname=fs.CreateTextFile("c:\test.txt",true) fname.WriteLine("Hello World!") fname.Close set fname=nothing set fs=nothing %>

The FileSystemObject object's properties and methods are described below:

Properties

Property	Description
Drives	Returns a collection of all Drive objects on the computer

Methods

Method	Description
BuildPath	Appends a name to an existing path
CopyFile	Copies one or more files from one location to another
CopyFolder	Copies one or more folders from one location to another
CreateFolder	Creates a new folder
CreateTextFile	Creates a text file and returns a TextStream object that can be used to read from, or write to the file
DeleteFile	Deletes one or more specified files
DeleteFolder	Deletes one or more specified folders
DriveExists	Checks if a specified drive exists
FileExists	Checks if a specified file exists
FolderExists	Checks if a specified folder exists
GetAbsolutePathName	Returns the complete path from the root of the drive for the specified path
GetBaseName	Returns the base name of a specified file or folder
GetDrive	Returns a Drive object corresponding to the drive in a specified path
GetDriveName	Returns the drive name of a specified path
GetExtensionName	Returns the file extension name for the last component in a specified path
GetFile	Returns a File object for a specified path
GetFileName	Returns the file name or folder name for the last component in a specified path
GetFolder	Returns a Folder object for a specified path
GetParentFolderName	Returns the name of the parent folder of the last component in a specified path
GetSpecialFolder	Returns the path to some of Windows' special folders
GetTempName	Returns a randomly generated temporary file or folder
MoveFile	Moves one or more files from one location to another
MoveFolder	Moves one or more folders from one location to another
OpenTextFile	Opens a file and returns a TextStream object that can be used to access the file

The Folder Object

The Folder object is used to return information about a specified folder.

To work with the properties and methods of the Folder object, you will have to create an instance of the Folder object through the FileSystemObject object. First; create a FileSystemObject object and then instantiate the Folder object through the GetFolder method of the FileSystemObject object.

The following code uses the GetFolder method of the FileSystemObject object to instantiate the Folder object and the DateCreated property to return the date when the specified folder was created:

-------------------
Dim fs,fo
Set fs=Server.CreateObject("Scripting.FileSystemObject")
Set fo=fs.GetFolder("c:\test")
Response.Write("Folder created: " & fo.DateCreated)
set fo=nothing
set fs=nothing
--------------------

Output:
Folder created: 10/22/2001 10:01:19 AM

Extended Document Property Information

This code will retrieve all Extended properties of any document
like Title, Description, Comments, Author etc.
Lists the summary information properties for a document named C:\Scripts\Test.doc.

Set objPropertyReader = CreateObject("DSOleFile.PropertyReader")
Set objDocument = objPropertyReader.GetDocumentProperties _
("C:\Scripts\Test.doc")

Wscript.Echo "Application name: " & objDocument.AppName
Wscript.Echo "Author: " & objDocument.Author
Wscript.Echo "Byte count: " & objDocument.ByteCount
Wscript.Echo "Category: " & objDocument.Category
Wscript.Echo "Character count: " & objDocument.CharacterCount
Wscript.Echo "Character count with spaces: " & _
objDocument.CharacterCountWithSpaces
Wscript.Echo "CLSID: " & objDocument.CLSID
Wscript.Echo "Comments: " & objDocument.Comments
Wscript.Echo "Company: " & objDocument.Company
Set colCustomProperties = objDocument.CustomProperties
For Each strProperty in colCustomProperties
Wscript.Echo vbTab & strProperty.Name & ": " & strProperty.Value
Next
Wscript.Echo "Date created: " & objDocument.DateCreated
Wscript.Echo "Date last printed: " & objDocument.DateLastPrinted
Wscript.Echo "Date last saved: " & objDocument.DateLastSaved
Wscript.Echo "Has macros: " & objDocument.HasMacros
Wscript.Echo "Hidden slides: " & objDocument.HiddenSlides
Wscript.Echo "Icon: " & objDocument.Icon
Wscript.Echo "Is read only: " & objDocument.IsReadOnly
Wscript.Echo "Keywords" & objDocument.Keywords
Wscript.Echo "Last edited by: " & objDocument.LastEditedBy
Wscript.Echo "Line count: " & objDocument.LineCount
Wscript.Echo "Location: " & objDocument.Location
Wscript.Echo "Manager: " & objDocument.Manager
Wscript.Echo "Multimedia clips: " & objDocument.MultimediaClips
Wscript.Echo "Name: " & objDocument.Name
Wscript.Echo "Page count: " & objDocument.PageCount
Wscript.Echo "Paragraph count: " & objDocument.ParagraphCount
Wscript.Echo "Presentation format: " & objDocument.PresentationFormat
Wscript.Echo "Presentation notes: " & objDocument.PresentationNotes
Wscript.Echo "ProgID: " & objDocument.ProgID
Wscript.Echo "Revision number: " & objDocument.RevisionNumber
Wscript.Echo "Slide count: " & objDocument.SlideCount
Wscript.Echo "Subject: " & objDocument.Subject
Wscript.Echo "Template: " & objDocument.Template
Wscript.Echo "Thumbnail: " & objDocument.Thumbnail
Wscript.Echo "Title: " & objDocument.Title
Wscript.Echo "Version: " & objDocument.Version
Wscript.Echo "Word count: " & objDocument.WordCount



Retrieving Extended File Properties

Uses the Shell object to return extended properties for all the files in the folder C:\Scripts.

Dim arrHeaders(34)

Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace("C:\Scripts")

For i = 0 to 33
arrHeaders(i) = objFolder.GetDetailsOf(objFolder.Items, i)
Next

For Each strFileName in objFolder.Items
For i = 0 to 33
Wscript.Echo i & vbtab & arrHeaders(i) _
& ": " & objFolder.GetDetailsOf(strFileName, i)
Next
Next

WebBrowser Navigate Method

How To Use WebBrowser HEADERS Param in Navigate Method Step-by-Step Example

1.	Create a new Standard .exe project in Microsoft Visual Basic 5.0. Form1 is created by default.
2.	From the Project menu, click Components.
3.	Select the Microsoft Internet Controls component. Click OK.
4.	Add the following controls to Form1: Control Name -------------- --------- Command Button Command1 WebBrowser WebBrowser1
5.	In Form1's code window, add the following code: Option Explicit Private Sub Command1_Click() WebBrowser1.Navigate URL:= "http://www.microsoft.com" _ ,Headers:= "Authorization: Basic XXXXXX" & chr$(13) & chr$(10) ' Note: All headers must be terminated with a ' carriage return linefeed pair. If WebBrowser1.Visible = False Then WebBrowser1.Visible = True End If End Sub
6.	From the File menu, click Save Project1.
7.	From the Run menu, click Start. Note that when you click Command1, the WebBrowser appears and automatically loads the URL specified in the URL parameter of Navigate.