webhosting Web Hosting Wit.Com
Webhosting, Server and Web Site Help & Information
Internet Services
  Domain Names
  Web Hosting
  Accept Credit Cards
  SSL Certificates
  T1 Connections
 
Coding Tutorials
  HTML Tags
  HTML Code
  Style Sheets
  Javascript
  PHP Tutorial
  ASP Tutorial
  SQL (Database)
  ASP Code
  HTML Templates
 
Tips & Tricks
  Design Dos & Don'ts
  HTML Tips
  Useful Applications
  Search Engines
  My IP Lookup
 
Charts
Contact Us
Home Page
 
html basic code
free html codes
SQL (Database)
Free Database Tutorial

What is SQL?
SQL stands for Structured Query Language. This is used to access information from a database by the use of commands. The following guide will show you how to use SQL with ASP (Active Server Pages) to add databsae interaction to your website(s).

To use SQL you must be using a database management system that supports it. A few of those are Microsoft SQL Server, Access, Oracle, and Sybase. If you need web hosting with SQL click here to find a server or web host with the options you need.

First save your pages as .asp instead of .html because this tells the browser to open the ASP side server for ASP functions (which, in this case, will include the SQL database commands) within the web page.  You will have to load the pages on a Windows-based server.

Getting Started
Since we're going to use ASP along with SQL, you will need to refer to the ASP tutorial to setup your ASP pages correctly. In addition to that, you need to have database access configured on an SQL server. You may wish to contact your webhosting provider for that information if you don't already have it.

Once you have everything ready, you must make connections to the database from your .asp code. Since this is not HTML code, you must have these statements as part of your ASP code (i.e. between the <% and %> markers).

Reserve memory for the following variables at the start of your code:
Dim connection
Dim record
Dim query

Set the connection variable to connect to the database server:
Set connection = server.CreateObject ("ADODB.Connection")

Set the record variable to get records from the database:
Set record = server.CreateObject ("ADODB.RecordSet")

(The query variable doesn't need to be assigned in the beginning of your code.)

To connect to your database you will need the database name, and a username and password to gain access to it.
connection variable.Open("dsn=database name; uid=username; pwd=password")

Once you have a connection, accessing the database is easy. Assign your query varilable to an SQL instruction. (See the Quick Reference below for an abbreviated list of SQL instructions.)

Using SQL
Say you kept the members of an organization in a database, and wanted to see all of the members' first names, last names, and phone numbers. Assuming the field names are firstname, lastname, and phone, you would do this:
query = "Select firstname, lastname, phone from table"

Next step is to use your record and connection variables to open the database with your query request.
record.open query.connection

The best way to go through all the entries selected by your query is by using Do While / Loop. For an explanation of how Do While / Loop works, please see the ASP Tutorial. Use the eof (end of file) command to tell the loop to keep repeating as long as we're not at the end of the query.
Do While record.eof = false

To display the fields on the screen, you would use Response.Write. For an explanation on how Response.Write works, please see the ASP Tutorial. Use the field command to specify which fields to retrieve.
Response.Write(record.fields("lastname") & ", " & record.fields("firstname") & ", phone: " & record.fields("phone"))

To get to the next entry, simply use:
record.movenext

This is a good spot to end your Do While loop, so you would put:
Loop

For more examples on how to use SQL with ASP, please see SQL under Coding Examples.

Quick Reference
The following are common instructions used in accessing databases in SQL, with what they do and how to use them. Note: column and table should be replaced with valid column names from a valid SQL database table.

Select   Selects the specified records from the database table. You can select any of the columns in the table. Some commonly used options to use with Select are Where, Order By, and Count. See below for their definitions.
Select column, column from table
Select / Where   Where is optional, and is used to only select entries where column is related to value by the following operators: = (equal), > (greater than), < (less than), >= (greater than or equal), <= (less than or equal), <> (not equal to), or Like. Like can be used to find matching records by using a few letters or numbers. Use a % for wildcards.
Select column, column from table Where column='value'
Select / Order By   Order By is optional, and if being used with Where, must follow the Where clause. Use Order By to specify the order of the contents by either asc (ascending order, or a-z) or desc (descending order, or z-a).
Select column, column from table Order By desc
Select / Count   Count must be used by itself. This will count the number of entries in a specified table.
Select Count(column) from table
Update   This command updates columns in a database table. You can update any of the columns in the table. A recommended option to use with Update is Where (to avoid updating all rows in the table with the same column information). See below for its definition.
Update table set column='value', column='value'
Update / Where   Where is optional, and is used to only select entries where column is related to value by the following operators: = (equal), > (greater than), < (less than), >= (greater than or equal), <= (less than or equal), <> (not equal to), or Like. Like can be used to find matching records by using a few letters or numbers. Use a % for wildcards.
Update table set column='value', column='value' Where column='value'
Insert Into   Adds a new row to the desired table, setting the specified columns to their respected values. (i.e. column1 receives the value of value1, column2 receives the value of value2, etc.)
Insert Into table (column1, column2, column3) values ('value1', 'value2', 'value3')
Delete From   This command deletes a row from a database table. A recommended option to use with Delete From is Where (to avoid deleting the entire table). See below for its definition.
Delete From table
Delete From / Where   Where is optional, and is used to only select entries where column is related to value by the following operators: = (equal), > (greater than), < (less than), >= (greater than or equal), <= (less than or equal), <> (not equal to), or Like. Like can be used to find matching records by using a few letters or numbers. Use a % for wildcards.
Delete From table Where column='value'


If you have any programming tips, information, or scripts you would like to add to the site e-mail it to webmaster@webhostingwit.com

2003 Web Hosting Wit.Com and its licensors. All Rights Reserved
Designed by Fencl Web Design