SQL Database Tutorial - Webhosting, Server, and Web Site Help and Information

Web Hosting Wit.Com
Webhosting, Server and Web Site Help & Information

Internet Services

Coding Tutorials

Tips & Tricks

Charts

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