What is an SQL Statement.
SQL statements are like questions or
commands you wish to act on a database. There are
basically four types of SQL Statements.
Select Statement - Used to pull a
recordset out of a database this is definately a
Delete Statement - Used
to delete a recordset in a database.
Update Statement - Used to
Update a recordset in a database.
Insert Into Statement - Used to
add a record to the database.
I don't know what a recordset
A recordset can
be a whole table in a database or it can be a single record in a
database you get to decide what it consist of. For instance
let's say you need to find all the people who live in Chicago
that are in your table. The table consists of people
that live in the United States millions of people.
You would ask for a recordset that
consists of only the people who live in Chicago that would be
your recordset, still thousands
of records If you asked for something more specific
like who lives at '433 West 3rd Street Apt 12',
'Chicago', 'IL', 98765 . Then your recordset would consist of hopefully one
Now that you have an understanding of
what a recordset is we can start with pulling one up using our
Please note that if you want to
follow the examples then all names are very important the computer
will not try to figure things out it will just spit errors back at
you. Also you need to download the DSN-Less Links code on the
previous page and install and upload them to your website. You
also need to contact wiredhosting to have them specify the correct
permissions on your database directory. Once you have done all
of that you are ready to begin. This tutorial is written in
the context that you are familiar with Microsoft Access and we are
Using Access '97 for the basis of our examples however the same
basic premisis will work with other databases.
create a New Database called "FirstDatabase" using Microsoft
Next let's make a Table in the
databe and Call it "OurFirstTable". Just select the design view to skip all of
Next lets make some fields in our table and call
"Field1" and make it an Autonumber and make this field your
"Field2" and make it a Text field.
make it a Number field.
Save the table with your new
Now that we have the table set up add some data to
the table. Use this data to stay with this example.
Now lets create a query in Microsoft
Access to create a recordset. First go to the querys table and
create a New Query go directly to design mode. When you see
the first window pop up select "OurFirstTable" and press add Button
then close that window. Next you will see the table box and a list
of fields. Click and drag the field with "*" in it. By
doing this you will be asking for all the fields to be shown in your
select query. Next click and drag the "field1" field down to
the query builder on the criteria part under "field1" add a 1
to it and click the show "check" off. You want to make sure
the check is off because it will result in error when we move our
SQL statement to the webserver access however does not have a
problem with showing duplicate fields. The result of all of
this should look like the following picture. You have now
created your first Select Sql statement. Basically you asked
for all the records in the database with 'field1' =
1 this will create a recordset of
The next thing you want to do is to test you query
in access go ahead and run it and make sure you get this
result. To run the query simply select the DataSheet view below the file
got the correct results. Next lets take a lool at the SQL
view. Use the same menu as you did in Step 6. Notice we
have a SQL statement that should look like this.
We are on our way however this is not complete compatible
with the ADO object so we need to make some changes. First all
the SQL statement is a string that is sent to the database
engine. Basically the database engine recieves your statement
whicha is a string and then acts on it. We have to put it in a
string format. For ease of coding I usually set it up equal to
a variable call SQQL. So this is what it should look like on
you ASP coded page. A short statement like the one above
should be on one line of code like this and you should insure that
the spacing between the word is the same as access has it and the
SQQL ="SELECT OurFirstTable.*
FROM OurFirstTable WHERE
That was pretty easy.
I am ready
for the the web.
First we need to add in our include files
so that we have the rest of the code attached to our current ASP
Second we need to tell our webserver what
database we are connecting to. To do this we use this
Set ConnSQL = DBConnect("FirstDatabase.mdb")
Third we need to add our SQL statement as
="SELECT OurFirstTable.* FROM OurFirstTable WHERE
Fourth we need to execute the SQL
Set RS = ExecuteSQL(ConnSQL,
Finally we need to be able to view the
information on a web page.
Response.Write is simply like a print command
and prints information on the web page you will use this function
often when debugging your applications. The <BR> is a
line break in HTML code. OK here is what your ASP Page
should look like.