1 / 30

Database Code Explained

Review. In a previous assignment, you added the following code which copies information from a form to the database:rs("name") = request.form("name")There are many other lines of code in process2.asp. What do they do?What do you have to change in future studies?. Terminology. Visual Basic Scrip

deiondre
Download Presentation

Database Code Explained

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

    1. Database Code Explained

    2. Review In a previous assignment, you added the following code which copies information from a form to the database: rs("name") = request.form("name") There are many other lines of code in process2.asp. What do they do? What do you have to change in future studies?

    3. Terminology Visual Basic Script (the language in an ASP page) is partially an “object oriented language” Most “things” are objects which means they have a variety of properties and can be manipulated with a variety of “methods” It is common to declare objects in two steps: Create an abstract object “Instantiate” (in-stan-she-ate) the object into something you can actually use.

    4. adovbs.inc ADO is an acronym meaning ActiveX Data Object. VBS is the language we are using. .inc means it is an include file, i.e. it does not stand alone and is intended to be imported (included) into other files. adovbs.inc is a “helper file” written by Microsoft that contains useful short cuts.

    5. Including Files You can include (import) files into an ASP page with the following code: <!-- #include file="adovbs.inc" --> You NEED adovbs.inc to be included in order to talk to a database. Don’t forget! The include line should be at or near the top of the ASP page.

    6. DSN File You get to your database by referring to its nickname. DSN = Data Source Name. A nickname is assigned to your database by registering the database with the sever. Those of you taking the second part of this course will do this yourself.

    7. DSN Choices There is a way to refer to a database without a nickname but it is too much trouble. If you end up on a server where the administrator won’t make a DSN connection you can google for how to make a “DSN-less connection”. You can refer to your database’s DSN name by hard-coding it into every page. If you move to a different server, or copy your study to run a second version, you will have to change the name on every page! Make a mistake, and you could hurt your old data.

    8. DSN Include Files The best solution is to put your DSN name into a single file and then include it into every page. If you need to change the name, you only have to change it in one place. DSN file’s contents: <% DSN = "dsn=research;" %> (This changes a little bit if your database has a password.) Include the file with this line: <!--#include file="dsn.asp" -->

    9. .INC vs .ASP Why is adovbs.inc an .inc file but dsn.asp is an .asp file? If someone guesses the name and location of a .inc file they can steal it. Sometimes passwords and other vital information are stored in .inc files. If you put in a URL to an ASP include file your browser will not show anything. Try it. http://psych.unl.edu/930/your_username/dsn.asp

    10. Opening a Connection to a DB Set Connect = Server.CreateObject("ADODB.Connection") Connect.Open DSN First line creates an object called “Connect” which will tie our webpage into a database. You may call “Connect” something else. E.g., if you need to open more than one connection in a single page you can have a Connect1, Connect2, Connect3, ... Connect.Open DSN The DSN variable is declared in dsn.asp and gets replaced with your database’s name. “Dot notation” is how you operate on a variable. Take the “Connect” object and “Open” it. Connect1.Open, Connect2.Open, DBConn.Open, etc.

    11. Creating a Recordset Variable Set rs = Server.CreateObject("ADODB.Recordset") “Server” is a provided object. CreateObject is a built in “method” which knows that ADODB.Recordset is a database recordset. “Recordset” means a set of records from a database. All this gets stored in “rs” You can call “rs” something else: GetRecords, rs2, rs3, FromDB, etc.

    12. SQL SQL = Structured Query Language Possibly the easiest computer language ever! In order to get all the records in a database with the table kitty: sql = “SELECT * FROM kitty” * means everything. You can also ask for just certain fields: sql = “SELECT name, id, pet FROM kitty”

    13. SQL To get a specific record, such as the subject with an id number of 123: sql = “SELECT * FROM kitty WHERE id=123” If their id is in a form on the previous page: sql = “SELECT * FROM kitty WHERE id=“ & request.form(“id”) & connects “strings” together “kitty” & “cat” = “kittycat”

    14. SQL If the “where” item in the database is not a number, then its name needs to be in single quotes: ‘ ‘ sql = “SELECT * FROM kitty WHERE id=‘S42’ ” A Windows server with an Access database will not usually require any other SQL statements. Other configurations might require you to always use SQL to talk to a database. sql = “INSERT INTO kitty (name, id) VALUES (‘Doraemon’, 123)” There are many SQL books if you are ever in this situation.

    15. Getting the Records from the DB rs.Open sql, Connect, adOpenKeyset, adOpenDynamic Take the “rs” object Apply the “Open” method to it Use the connection string “sql” Go to the database specified in the “Connect” object Use the two options adOpenKeyset & adOpenDynamic Don’t worry about adOpenKeyset and adOpenDynamic There are different options depending on what you want to do These two choices work in all situations These two variables are defined in adovbs.inc “rs” will now hold information from the database

    16. About “rs” You can do a lot with “rs”. We will learn more tricks later You can cycle through all the records We’ll do this in the Powerpoint entitled, “Generating Subject IDs” to make sure we aren’t using an ID twice Make a new record: rs.AddNew rs only gives you access to one record at a time. Move to the first record: rs.MoveFirst Move to the next record: rs.MoveNext

    17. Using “rs” Add a new record: rs.AddNew This command will always put you on the new, blank record. Put form data into the recordset rs(“name1”) = request.form(“name1”) rs(“name2”) = request.form(“name2”) rs(“name3”) = request.form(“name3”) rs(“name4”) = request.form(“name4”) Send the information to the database rs.Update

    18. An Access Limitation Microsoft makes a high-end database product called SQL Server If Access did everything, no one would by SQL Server Access has limits built into it One such limit is that you can only have 10 connections to a database open on one server at one time

    19. An Access Limitation Only 10 connections at once! If connections are opened and closed quickly we can have many web sites running on the server. If you don’t close the connection, it will stay open for a few seconds or more. If you DO close the connection, it will be open for just a fraction of a second. This is good for everyone!

    20. Closing Connections rs.Close Set rs = nothing Connect.Close Set Connect = nothing “rs” and “Connect” were opened so now we apply the “Close” method to them Setting them equal to the reserved variable “nothing” will clear them from memory.

    21. Other Things to Know A given Connect object provides a connection to exactly one (1) database. You may use the same Connect object to read multiple tables in the same database. Each table will need a different sql statement and recordset variable. See code on next slide

    22. Reading Multiples Tables in One DB Set Connect = Server.CreateObject("ADODB.Connection") Connect.Open DSN sql1 = “SELECT * FROM kitty” sql2 = “SELECT * FROM puppy” sql3 = “SELECT id FROM employees” Set rs1 = Server.CreateObject("ADODB.Recordset") Set rs2 = Server.CreateObject("ADODB.Recordset") Set rs3 = Server.CreateObject("ADODB.Recordset") rs1.Open sql1, Connect, adOpenKeyset, adOpenDynamic rs2.Open sql2, Connect, adOpenKeyset, adOpenDynamic rs3.Open sql3, Connect, adOpenKeyset, adOpenDynamic

    23. Reading Multiple Databases Set Connect1 =Server.CreateObject("ADODB.Connection") Set Connect2 =Server.CreateObject("ADODB.Connection") Connect1.Open Database1 Connect2.Open Database2 sql1 = “SELECT * FROM kitty” sql2 = “SELECT * FROM puppy” Set rs1 = Server.CreateObject("ADODB.Recordset") Set rs2 = Server.CreateObject("ADODB.Recordset") rs1.Open sql1, Connect1, adOpenKeyset, adOpenDynamic rs2.Open sql2, Connect2, adOpenKeyset, adOpenDynamic

    24. Checkbox code explained We used this code for everything except checkboxes: rs(“name”) = request.form(“name”) We used this code for checkboxes: IF len(request.form(“name”)) > 0 THEN rs(“name”) = request.form(“name”) ELSE rs(“name”) = 0 END IF

    25. Checkbox code explained If a checkbox is checked, request.form(“name”) has the same value as was given in the form: <input type=checkbox name=“name” value=“1”> If a checkbox is not checked, it does not have a value, i.e. its value is “nothing” but what is nothing? How do we test if something is equal to nothing? Worse yet, I’m convinced that there is more than one type of nothing in VBS! Code that works in one place (e.g. IF variable = Null) does not work in other places.

    26. Checkbox code explained rs(“name”) = request.form(“name”) does not send anything to the database if the checkbox was not checked. (We usually want a 0 or something to be sent.) You canNOT make the default value 0 in the database because sending “nothing” to the database erases the 0. We need to send an actual 0 to the database if they do not check the checkbox. We need to know whether or not the checkbox was checked.

    27. IF-THEN statements IF something THEN Do this END IF If “something” is true then we will “do this”, otherwise we don’t do anything. IF x=3 THEN Response.write “x=3” END IF This only writes “x=3” to the screen if x is actually 3. If not, nothing gets written to the screen.

    28. IF-THEN-ELSE statements IF something THEN Do this ELSE Do something else END IF If “something” is true then we will “do this”, otherwise we “do something else”. IF x=3 THEN Response.write “x=3” ELSE Response.write “x is not 3” END IF This only writes “x=3” to the screen if x is actually 3. If not, “x is not 3” gets written to the screen.

    29. Try 1 IF request.form(“name”) = 1 THEN rs(“name”) = request.form(“name”) ELSE rs(“name”) = 0 END IF This does work, in this situation. Sometimes, if request.form(“name”) is not equal to anything, then trying to compare it to something else causes an error.

    30. Try 2 IF len(request.form(“name”)) > 0 THEN rs(“name”) = request.form(“name”) ELSE rs(“name”) = 0 END IF You can take the len of something, even if it doesn’t exist. If the checkbox is checked, it has some value, therefore its length is >0. The length of a form value will only be 0 if it doesn’t exist. Even “0” has a length of 1.

More Related