ASP development web-host provider

Contact  Place Order


resellers spacer

NT Web Hosting FAQ

Internet access for California spacer

Contact Wired Hosting in Bay Area spacer

Dedicated Servers spacer

Sign up! spacer

Virtual Web Hosting spacer

Web Hosting Help spacer



Have Questions?

Catch us on ICQ @ 40927225


   Database
 
Creating your own SQL Statements using Microsoft Access

 
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 question.
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.

Help I don't know what a recordset is.

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 record.

Now that you have an understanding of what a recordset is we can start with pulling one up using our DNS-Less Links. 

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.

Step 1:
First lets create a New Database called "FirstDatabase" using Microsoft Access.

 

Step 2:
Next let's make a Table in the databe and Call it "OurFirstTable".   Just select the design view to skip all of the wizards. 

Step 3:
Next lets make some fields in our table and call them:
"Field1" and make it an Autonumber and make this field your primary key.
"Field2" and make it a Text field.
"Field3" and make it a Number field.
Save the table with your new fields.

 

Step 4:
Now that we have the table set up add some data to the table.  Use this data to stay with this example.
Field1 Field2 Field3
1 Chicago 24568
2 New York 55555
3 San Ramon 94583

 

Step 5:
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 1 record.

 

Step 6:
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 menu.

 

Step 7:
Hopefully you 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.

SELECT OurFirstTable.*
FROM OurFirstTable
WHERE (((OurFirstTable.Field1)=1));

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 example below.

SQQL ="SELECT OurFirstTable.* FROM OurFirstTable WHERE (((OurFirstTable.Field1)=1));"

That was pretty easy.

Step 8:
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 page.

<!--#Include virtual = "/includes/errorinc.asp"-->

Second we need to tell our webserver what database we are connecting to.  To do this we use this code.

Set ConnSQL = DBConnect("FirstDatabase.mdb")

Third we need to add our SQL statement as above.

SQQL ="SELECT OurFirstTable.* FROM OurFirstTable WHERE (((OurFirstTable.Field1)=1));"

 Fourth we need to execute the SQL Statement.

Set RS = ExecuteSQL(ConnSQL, SQQL)

Finally we need to be able to view the information on a web page.

Response.Write(RS("Field1") & "<BR>")
Response.Write(RS("Field2") & "<BR>")
Response.Write(RS("Field3") & "<BR>")

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.

 

<!--#Include virtual ="/includes/errorinc.asp"-->
<%
Set ConnSQL = DBConnect("FirstDatabase.mdb")
SQQL ="SELECT OurFirstTable.* FROM OurFirstTable WHERE (((OurFirstTable.Field1)=1));"
Set RS = ExecuteSQL(ConnSQL, SQQL)
Response.Write(RS("Field1") & "<BR>")
Response.Write(RS("Field2") & "<BR>")
Response.Write(RS("Field3") & "<BR>")
%>

The Output of you page should look like this.

1
Chicago
24568

 


Pretty simple.  See the lessons on the other types of SQL Statements.

Delete
Update
Insert Into

 
Go back to database self help


 


spacer 

Web Hosting Home |ASP.NET 4.0 Web Hosting | Internet Access | Hosting FAQ | Frontpage Hosting | OrderASP.NET Hosting Resellers | Contact
All content copyrighted by Wired Hosting© All rights reserved. Acceptable Use Policy


Microsoft, FrontPage, ASP, Windows NT, ASP.NET 4.0, Windows, Visual Studio 2010, SQL Server 2008 Express, Visual Interdev are registered trademarks of Microsoft Corporation