SQL BASICS

SQL stands for Structured Query Language and it is the primary tool for selecting data from databases. If you are selecting data directly from a database and not via an ASP web page, there are many different versions of SQL. For example, MS SQL Server uses its own version of SQL called Transact SQL and MS Access also uses a less modified version of SQL.

With that said, we are here to learn how to use SQL within an ASP page. So once again, we will start with the code from our Database Connection Tutorial.

<% DIM objConn Set objConn = Server.CreateObject("ADODB.Connection") objConn.ConnectionString = "DSN=myCONNECTION.dsn" objConn.Open DIM mySQL mySQL = "SELECT * FROM myTABLE" DIM objRS Set objRS = Server.CreateObject("ADODB.Recordset") objRS.Open mySQL, objConn %>



Display data from database here.


Anytime you want to display a specific subset of data from your database onto a web page, you need to use a SQL statement. The example above show a basic SQL statement.

First, just like with the connection and recordset objects you must create a variable. In this case, we chose mySQL. Then, you write your statement. In SQL, SELECT and FROM are constants. The * is a wildcard meaning ALL as in SELECT ALL FROM. Last, myTABLE is the name of the table in your database where the records should be selected from.

Now, there are a lot of extra things you can include in a SQL statement to be more particular about records to display and even how to display them. Here are a few examples.

mySQL = "SELECT * FROM tblUsers ORDER BY DateVisited DESC"

This example includes a date field called DateVisited and would display records on your web page according to date with the most recent listed first.

mySQL = "SELECT TOP 10 * FROM tblUsers ORDER BY DateVisited DESC"

This example would display the top 10 records in your database according to date.

mySQL = "SELECT * FROM tblUsers WHERE Type = ' Customer ' "

This example includes a field called Type and would display all of your records that have Customer listed as a type.

mySQL = "SELECT * FROM tblUsers WHERE DateVisited BETWEEN 1/1/01 AND 12/31/01"

This example would display all of your records where the date contained in the DateVisited field is between 1/1/01 and 12/31/01.

mySQL = "SELECT * FROM tblInvoices WHERE Customer LIKE ' M% ' "

This example selects all of the records from our intInvoices table where the Customer field begins with the letter M.

mySQL = "SELECT * FROM tblInvoices WHERE Customer LIKE ' _icrosoft ' "

This example selects all of the records from our intInvoices table where the Customer field ends with the letters icrosoft.

mySQL = "SELECT * FROM tblInvoices WHERE Customer LIKE ' [ m - o ]icrosoft ' "

This example selects all of the records from our intInvoices table where the Customer field begins with any letter between m and o and also ends with the letters icrosoft.

These are some of the basics for writing SQL statements. There are a lot more, but this will help get you started. Later on we will be adding an advanced SQL Advanced Tutorial.

http://www.aspwebpro.com/tutorials/asp/sqlbasics.asp