Server Side Programming Database Integration (cont.)

1. Server Side ProgrammingDatabase Integration (cont.) Internet Systems Design Server Side Programming ASP

2. Overview • Review of Server Side Programming • ASP • VBScript • Advanced Database Interfacing with SQL • Insert • Delete • Update • Examples with Code Server Side Programming ASP

3. How can database be accessed by web browser? Server Side Programming e.g. Microsoft IIS server : ASP (VBScript, JScript, SQL) Database Internet Web browser Web browser Web browser Server Side Programming ASP

4. ASP Definition “Microsoft Active Server Pages (ASP) is a server-side scripting environment that you can use to create and run dynamic, interactive Web server applications. With ASP, you can combine HTML pages, script commands, and COM components to create interactive Web pages or powerful Web-based applications, which are easy to develop and modify.” -Microsoft’s Latest Definition Server Side Programming ASP

5. What are Active Server Pages? • Runs on IIS • Can provide compile-free application environment Server Side Programming ASP

6. ASP Basics • ASP file is a text file with the extension .asp • it contains any combination of: • Text • HTML Tags • ASP Script Commands/Components Server Side Programming ASP

7. ASP Script • ASP Script could be VBScript or Jscript (ECMAScript) • A script is a series of commands or instructions. • Script command instructs the web server to perform an action. • VBScript is similar to Visual Basic and Visual Basic for Application (VBA). Server Side Programming ASP

8. VBScript Basics • Not case sensitive • Declaring Variables VBScript does not require variable declarations, but it is good scripting practice to declare all variables before using them. To declare a variable in VBScript, use the Dim, Public, or Private statement. Server Side Programming ASP

9. VBScript Basics • VBScript Operators: • Arithmetic: +, -, *, /, ^ • Comparison: =, <>, <, >, <=, >= • Logical (for Boolean variables): Not, And, Or, Xor Server Side Programming ASP

10. Running ASP • Ensure .asp extensions are enabled in IIS • Save .htm, .asp, and .mdb on server in same folder (or give full folder extensions of file locations in your code) • Client accesses the file in similar fashion to .htm (or .html) file • Server detects .asp extension and runs script within ASP tags • Results sent to client Server Side Programming ASP

11. Using ASP • Homework 2 review • HTML form took input from a list box and invoked the .asp file • The .asp file retrieved data from a database using VB script, SQL and displayed the results to the user • Homework 3 will expand on these concepts Server Side Programming ASP

12. Advanced Database Interfacing Server Side Programming ASP

13. Database Connectivity • When a database is tied to a web site, .ASP uses an object library called ActiveX Data Objects, or ADO • E.g. The Connection object: • Set objConn = Server.CreateObject("ADODB.Connection") • Several ways to connect to a database • http://www.engineering.uiowa.edu/~ie181/Documents/DatabaseConnectionsFromASP.htm Server Side Programming ASP

14. Structured Query Language • SQL is a standard computer language for accessing and manipulating databases • SQL (Structured Query Language) is a syntax for executing queries. But the SQL language also includes a syntax to update, insert, and delete records. • http://www.w3schools.com/sql/sql_intro.asp Server Side Programming ASP

15. Structured Query Language • These query and update commands together form the Data Manipulation Language (DML) part of SQL: • SELECT - extracts data from a database table • UPDATE - updates data in a database table • DELETE - deletes data from a database table • INSERT INTO - inserts new data into a database table Server Side Programming ASP

16. Homework 3 • 1st part will involve inserting, deleting, and updating records in the database used in Homework 2 Server Side Programming ASP

17. Examples of Insert, Delete, Update Statements • "INSERT INTO Products (Candy, Price) SELECT '" & candy & "'," & price &"" • "DELETE FROM Products Where Candy = '" & candy & "'" • "UPDATE Products SET Price = " & price & " WHERE Candy = '" & candy & "'" Server Side Programming ASP

18. Example 1: Inserting Records • See http://128.255.21.191/Example5/AddCandy.htm • Only viewable in the ALF lab • Step 1: Create an Access Database Server Side Programming ASP

19. Example 1: Inserting Records • Step 2: Create a .html form <html> <head> <title>Candy</title> </head> <body> <form name=frmAddCandy action="addCandy.asp" method=post> <p>Candy: <input type="text" name="txtCandy" size="20"> Price: $<input type="text" name="txtPrice" size="20"></p> <p><input type="submit" value="Add Candy" name="butAdd">&nbsp; <input type="reset" value="Clear Form" name="butCancel" size="20"></p> <p><a href='DeleteCandy.htm'>Delete Candy</a></p> </form> </body> </html> Server Side Programming ASP

20. Example 1: Inserting Records • Step 3: Create the .asp file <%@ LANGUAGE='VBSCRIPT'%> <html> <head> </head> <body> <% 'strSql will be the variable used to hold the sql statement string dim strSql Set MyConn = Server.CreateObject("ADODB.Connection") MyConn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("Candy.mdb") 'grab the values from the form candy = Request.form("txtCandy") price = Request.Form("txtPrice") 'create the SQL statement that will insert the data to the table 'remember text values need single quotes wrapped around them strSql = "INSERT INTO Products (Candy, Price) SELECT '" & candy & "'," & price &"" MyConn.Execute strSql Response.Write "Added the candy with the sql statement: " & strSql Set MyConn = nothing %> <p> </html> Server Side Programming ASP

21. Example 1: Inserting Records • Step 4: Save the files to a folder in the wwwroot folder, run the example and view changes…notice Licorice was added to the Products table Server Side Programming ASP

22. Example 2: Deleting Records • http://128.255.21.191/Example5/deleteCandy.htm • Step 1: Create a .html form <html> <head> <title>Candy</title> </head> <body> <form name=frmDeleteCandy action="deleteCandy.asp" method=post> <p>Candy: <input type="text" name="txtCandy" size="20"> </p> <p><input type="submit" value="Delete Candy" name="butDelete">&nbsp; <input type="reset" value="Clear Form" name="butCancel" size="20"></p> <p><a href='addCandy.htm'>Add Candy</a></p> </form> </body> </html> Server Side Programming ASP

23. Example 2: Deleting Records • Step 2: Create the .asp file <%@ LANGUAGE='VBSCRIPT'%> <html> <head> </head> <body> <% 'strSql will be the variable used to hold the sql statement string dim strSql Set MyConn = Server.CreateObject("ADODB.Connection") MyConn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("Candy.mdb") 'grab the candy name from the form candy = Request.form("txtCandy") 'create the SQL statement that will insert the data to the table 'remember text values need single quotes to wrapped around them strSql = "Delete FROM Products Where Candy = '" & candy & "'" MyConn.Execute strSql Response.Write "Deleted the candy with the sql statement: " & strSql Set MyConn = nothing %> <p> <a href='addCandy.htm'>Add Candy</a> - <a href='updateCandy.htm'>Update Candy</a> - <a href='deleteCandy.htm'>Delete Candy</a> </p> </body> </html> Server Side Programming ASP

24. Example 2: Deleting Records • Step 3: Save the files to a folder in the wwwroot folder, run the example and view changes…notice Licorice was deleted from the Products table Server Side Programming ASP

25. Example 3: Updating Records • http://128.255.21.191/Example5/updateCandy.htm • Step 1: Create a .html form <html> <head> <title>Candy</title> </head> <body> <form method="Post" action="updateCandy.asp"> <p> <select name = "selCandy" method ="post" size ="1"> <option selected value="Gum">Gum</option> <option selected value="Suckers">Suckers</option> <option selected value="Taffy">Taffy</option> <option selected value="Skittles">Skittles</option> <option selected value="M&Ms">M&Ms</option> <option selected value="Lifesavers">Lifesavers</option> <option selected value="Snickers">Snickers</option> </select> New Price:<Input type='text' name='txtPrice' size=10> <input type="submit" value="Update Price"> </p> </form> </body> </html> Server Side Programming ASP

26. Example 3: Updating Records • Step 2: Create the .asp file <%@ LANGUAGE='VBSCRIPT'%> <html> <head> </head> <body> <% 'strSql will be the variable used to hold the sql statement string dim strSql Set MyConn = Server.CreateObject("ADODB.Connection") MyConn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("Candy.mdb") 'grab the candy name from the form candy = Request.form("selCandy") price = Request.form("txtPrice") 'create the SQL statement that will insert the data to the table 'remember text values need single quotes to wrapped around them strSql = "UPDATE Products SET Price = " & price & " WHERE Candy = '" & candy & "'" MyConn.Execute strSql Response.Write "Update the candy price with the sql statement: " & strSql Set MyConn = nothing %> </body> </html> Server Side Programming ASP

27. Example 3: Updating Records • Step 3: Save the files to a folder in the wwwroot folder, run the example and view changes…notice Snicker’s price was changed from $4.00 to $0.25 in the Products table Server Side Programming ASP