ASP Programming | ASP Web Pro
  Active Server Pages Programming by ASP Web Pro

ASP Tutorials

SQL ADVANCED

OK, so you got through our SQL Basics Tutorial. Now, we are moving on to some more advanced SQL statements. As a refresher, here is our standard connection and recordset information, which shows you where your SQL will go.

<%
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
%>
<HTML>
<HEAD><TITLE></TITLE><HEAD>
<BODY>
Display data from database here.
</BODY>

Now, let's get to the good stuff. Here are some of the more advanced things you can do with your SQL statements.

mySQL = "SELECT Count(*) AS intTotal FROM tblInvoices"

This example counts the number of records contained in our intInvoices table.

mySQL = "SELECT SUM(Revenue) AS intTotal FROM tblInvoices"

This example sums a currency field called revenue from our intInvoices table. There are other commands you can use other than SUM in the same way. You can also replace SUM with AVG, MIN, or MAX to find the average, minimum, or maximum values. If you are really into statistical analysis, you can even use VARIANCE or STDDEV to return the variance or standard deviation values.

mySQL = "SELECT * FROM tblInvoices WHERE DueDate = # " & Date() & " # "

This example selects all of the records in our tblInvoices tables where the DueDate field equals todays date.

mySQL = "SELECT * FROM tblInvoices WHERE Type = ' " & strVariable & " ' "

This example selects all of the records in our tblInvoices tables where the Type field is equal to a string or text variable such as "Customer" that is entered on your ASP page.

mySQL = "SELECT * FROM tblInvoices WHERE Number = " & intVariable & " "

This example selects all of the records in our tblInvoices tables where the Number field is equal to an integer or numeric variable such as "100" that is entered on your ASP page.

These are only some of the more advanced SQL statements. We are still only scratching the surface here, but we will continue to add more advanced SQL statements to this page as time goes on so be sure to check back.

< Back to Tutorials

 

 

Main Menu
Home
ASP Scripts
ASP Tutorials
JavaScripts
Awards
Contact Us

Email This Page

Other Resources
ASP Web Hosting
Search Engine Submission
Programming Help

 

 

 

Other ASP Web Sites

Site Map

 

Last Updated:
28 August 2008

 

© 1997-2008 ASP Web Pro, Inc. | Terms of Use