|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
May 2nd, 2005, 01:46 PM
|
||||
|
||||
|
ASP and SQL
Hi all,
I thought that I would create a short tutorial about using databases and sql with your ASP pages. Firstly, one needs to decide which database to use. MS Access would be a good option if you dont have many concurrent users accessing your site. A popular database to use with ASP is MS SQL Server, and for a cheaper option you could use a MYSQL database. Moving on... When using a database with ASP, you would need a two main components to use the database and results returned from it. 1. a connection to the database 2. and a recordset to hold results returned from the database. A basic example of a database connection to an Access database: Code:
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("myDataBase.mdb")
A complete list of connection strings for each database can be found at http://www.connectionstrings.com Next the recordset: Code:
Dim rs
set rs = Server.CreateObject("ADODB.Recordset")
to get values from the database, you should create your sql statement and open the recordset using that sql statement and your connection to the database: Code:
Dim strSql strSql = "SELECT * FROM myTable" rs.Open strSql, conn If you want to use criteria from a asp page, you can build up the sql statement accordingly: Code:
strSql = "SELECT * FROM myTable WHERE theID = " & request.form("myID")
and if theID is a text field, you should wrap it in single quotes, and when dealing with text, you should replace illegal character's from the input, other wise you are going to experience syntax errors, the most common one, is a ' in your input, you can use a replace() function to replace the single quotes with two single quotes Code:
strSql = "SELECT * FROM myTable WHERE theID = '" & replace(request.form("myID"),"'","''") & "'"
A recordset should only be used for queries that return results, for DELETE and UPDATE queries, you can execute the command like this: Code:
strSql = "DELETE * FROM myTable WHERE theID = '" & replace(request.form("myID"),"'","''") & "'"
conn.Execute strSql
Always remember to check for results before using the recordset in your display pages. Code:
Dim strSql strSql = "SELECT * FROM myTable" rs.Open strSql, conn IF rs.EOF or rs.BOF then 'THE RECORDSET IS EMPTY AND NO RESULTS ARE RETURNED ELSE 'DISPLAY ALL RECORDS END IF To display the results from the recordset in a text field (for instance): Code:
<input type="text" id="name" value="<%=rs("Name")%>"/>
Another common question, is how do I select values from more than one table? if there is a common field in both tables, you could use a inner join to select all matching records, or a left/right join to select only the values that dont match. You have two tables: Code:
EmpTable, and projectTable: EMPTABLE: EmpID = Number (Primary Key) EmpName = Text EmpSurname = Text ----------------------- PROJECTTABLE: ProjectID = Number (Primary Key) ProjectName = Text EmpID = Number (Foreign Key) Now you want to select all employee details that is busy working on a project, and you want to display the employee name, and project name: Code:
strSql = "SELECT Emptable.EmpName, Projecttable.ProjectName FROM Emptable INNER JOIN Projecttable ON Emptable.EmpID = Projecttable.EmpID" rs.Open strSql, conn If you want to select all employee's who is not busy with a project at the moment, use a left join Code:
strSql = "SELECT Emptable.EmpName FROM Emptable LEFT JOIN Projecttable ON Emptable.EmpID = Projecttable.EmpID WHERE Projecttable.EmpID IS NULL" rs.Open strSql, conn Well thats it for now. a Few things to remember: 1. Make sure that you have the correct type of database for the job at hand. 2. Check if you have any results returned from the database before you try and use them in your page. 3. Always close, and set the recordset and connection objects to nothing, after using them. Code:
rs.close conn.close set rs = nothing set conn = nothing 4. if using form values in your sql statements, make sure to replace illegal characters and wrap text fields, in single quotes. Hope this helps! And happy coding  PS. Any comments and suggestions/additions more than welcome 
__________________
Look! Its a ShemZilla   
|
|
#2
May 2nd, 2005, 02:14 PM
|
||||
|
||||
|
Looks good, probably should ask a mod to close this thread, and make it a sticky... and delete this post
EDIT: www.connectionstrings.com have connection strings for most common databases, just in case people were wondering what they should do if they are using oracle or some other database.
__________________
ford-forum.com Last edited by A2k : May 2nd, 2005 at 02:16 PM.
|
|
#3
May 7th, 2005, 05:21 PM
|
||||
|
||||
|
the Code Help is already full with useful threads, can't Stick them all. instead I've posted sticky with links to such useful threads, this one is in there as well. and no need to close anything, ppl are always welcome to respond and give remarks.
|
|
#5
May 9th, 2005, 06:06 AM
|
||||
|
||||
|
no sweat.
|
|
#6
November 16th, 2005, 11:24 AM
|
|||
|
|||
|
Excellent tutorial
|
|
#8
November 17th, 2005, 05:41 AM
|
||||
|
||||
|
hi jako.....it is really sweet code and nice guide for newbies.......
__________________
Nothing is Impossible bcoz IMPOSSIBLE itself says.. I M POSSIBLE........................ Be cool !!!!!!!!
|
|
#9
March 12th, 2006, 01:41 AM
|
|||
|
|||
connectivity to Oracle 9i
hi friends,
The Database which i am using is Oracle 9i..Plz help me with the code for the same. Thanks a lot Last edited by Shadow Wizard : December 1st, 2006 at 04:22 PM.
|
|
#10
March 12th, 2006, 01:45 AM
|
||||
|
||||
|
Hi,
you have been answered in this thread already http://forums.aspfree.com/asp-devel...ity-102843.html
|
|
#11
April 12th, 2007, 02:49 AM
|
|||
|
|||
|
very good and very helpful post dear i need this just now and i got it at right time
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
