Labtest.ASP Notes

Labtest.ASP Notes

<HTML> <HEAD> <META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-1252"> <TITLE>INSERT STATUS</TITLE> </HEAD> <BODY> <% mydsn= "websysx" Set conn = Server.CreateObject("ADODB.Connection") conn.open mydsn,"","" %> <HTML> <HEAD> <META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-1252"> <TITLE>INSERT STATUS</TITLE> </HEAD> <BODY> <% mydsn= "websysx" Set conn = Server.CreateObject("ADODB.Connection") conn.open mydsn,"","" %>

<% ' retrieve values from form (GET) 2 text fields ' Note we need to put them in quotes for the insert statement ' & is the VB concatentation character ' get the values FirstName =Request.QueryString("FirstName") LastName =Request.QueryString("LastName") Netid =Request.QueryString("Netid") Quote =Request.QueryString("Quote") Operation =Request.QueryString("Operation") Doform =Request.QueryString("Doform")

if Doform <> "False" then %> Use this form to insert, update, delete and query the data base<p> <form name="labtest" action="labtest.asp" method="GET"> <input type="text" name="FirstName" size="30"> FirstName <p> <input type="text" name="LastName" size = "30" > LastName <p> <input type="text" name="Netid" size="8"> Netid (Required for all operations)<p> <textarea name= "Quote" rows="4" cols="60"> </textarea> Favorite Quote<p> <input type="radio" name="Operation" value="Insert"> Insert data <p> <input type="radio" name="Operation" value="Update"> Update data <p> <input type="radio" name="Operation" value="Delete"> Delete this person <p> <input type="radio" name="Operation" value="Query"> Find this person <p> <input type="hidden" name="Doform" value="False""> <input type="submit" value="submit"> </form> <% End If

If Doform = "False" then If Operation = "Insert" then 'SQL INSERT SYNTAX: 'INSERT INTO table_name (column1, column2,...) 'VALUES (value1, value2,....) ' BE CAREFUL numeric fields are not quoted, text fields are ' ' Create the insert statement sql = "INSERT into labtest (FirstName, LastName, Netid, Quote) " sql = sql & "VALUES(" & "'" & FirstName & "'" & "," sql = sql & "'" & LastName & "'" & "," sql = sql & "'" & Netid & "'" & "," sql = sql & "'" & Quote & "')"

' write the insert statement out for debugging response.write "<p>INSERT Statement: " & sql & "<P>" ' execute the insert statement Set rs = Server.CreateObject("ADODB.Recordset") rs.Open sql, conn, 3, 3 Else response.write "Currently only Insert Operations are supported" End If End If %> <% ' close the connection down conn.close conn="" %> </BODY>

Some Problems How do I add the new functions? How do I do error checking? • Update – • What is the easiest way to allow the user to change a record. • Delete – • How can I allow a user to delete multiple records at a time • How do I minimize errors? • Query • How does the user specify the query? • What are some options they might want? • Can I pass (link) the results of a query to another operation?

New Functions • IF Operation =“DELETE” then • Sql = “DELETE ROW from labtest where Netid=‘” & Netid & “’” • Set rs = Server.CreateObject("ADODB.Recordset") • rs.Open sql, conn, 3, 3 • END IF

Error Checking • The Result code of the Open statement returns error status • The result set has properties that can be checked by Visual Basic

Update Possibilities • 1) User specifies all fields • 2) User just supplies netid, and a new form is generated with the fields already filled out with existing information • 3) User supplies a query, all records satisfying query are returned with fields filled out, user makes changes and resubmits all of the record at once.

Delete Options • 1) User supplies netid and indicates DELETE • 2) User is provided with a list of all netids and then can delete those they wish. (problems??) • 3) User supplies a query, results of query generate a form where user can delete any items.

Other problems • What if user wants to insert a “special” character in their Quote. • What characters could be a problem? Why? What can you do?

Labtest.ASP Notes