Collecting Information from Forms

The two most common reasons for interacting with databases in a web-based online help system are to extract data from a database to include in a topic, and to store data collected from your users.

There are at least two ways of collecting data from your users. One method is to track their progress through the online help, and, based on that data, modify the content, the links, and so on to improve the documentation. There is a JSP module available for purchase that performs this kind of data collection.

Another method of data collection is to collect information explicitly from your users, through the use of forms.

This tutorial assumes that you know how to create HTML forms. If you do not, please review any of these HTML form primers:

• http://www.HTMLCodeTutorial.com/forms/

• http://www.mcli.dist.maricopa.edu/tut/tut28a.html

• http://mercury.he.net/~btaber/howto/forms.htm

If you merely need a refresher on the code for the specific form components, read my HTML Form “CopyMe” Page. This shows you the different form objects that HTML supports, and the code that you use to create them.

If you just want to collect data from forms without learning all of the back-end syntax, you can use the Form Processor Builder on this site. This wizard will collect information from you about your form and your database, and will generate all of the JSP code you need to store data collected in a database. Even if you learn all of the back-end syntax, you may still opt to use the Form Processor Builder, as it is faster and less error-prone than hand-coding.

Retrieving data

This topic shows you how to capture the data provided in a form, and insert it into a database record. If you are not familiar with coding pages to interact with a database, read the topic Database Overview before proceeding. The topic Email from JSP Pages shows you how to email the data provided in a form, instead of inserting it into a database.

The code that you add to a site for collecting user-provided data and inserting it into a database will reside on the page that is called by the page that has the form on it. The page with the code is represented by the JSP page with database code module in the following:

There doesn't need to be any JSP code in the page that has the form on it. In fact, you can use a plain HTML page for your form page, and only have a .jsp extension on the page that the form calls.

On the page that collects the data, you need to add some standard JSP code. The first elements that you need to add are some includes. These will enable the web page to access the Java support code for collecting form data and for interacting with a database. The code that you need to add, at the very top of the page, to enable the pages to collect form data is:

<%@ page import="javax.servlet.*" %>
<%@ page import="javax.servlet.http.*" %>

The (first bit of) code that you need to add to enable the page to interact with a database is:

<%@ page import="java.sql.*" %>

You need to add these code snippets before any HTML code on the page. In fact, depending on how you design your site, the page that processes the data that your users submit may not have any HTML on the page. For example, you can include the Java code in the page to process the data, and then redirect the user to another page (even basing what page they are redirected to on the data that they enter).

Next, you need to set up the code for connecting to your database. This code will vary depending on the type of database you are using, and what JDBC driver you are using. You will likely need help from your IS department to set up your first code segment. Thankfully, the code segment will be the same for all of your pages, if you are using the same database for all of your content.

Here is a facsimile of the code that I enter on my pages (I have modified some of the information to maintain security on my site, but it still shows you a representation of what you will likely code):

<%
Connection con = null;
Statement stmt = null;
ResultSet rs = null;

String queryText = "insert into tablename values(\"firstvalue\",\ "secondvalue\",\"thirdvalue\")";

try {
  Class.forName("org.gjt.mm.mysql.Driver");
  con = DriverManager.getConnection("jdbc:mysql://localhost:3306/username",
  "username", "password");
  stmt = con.createStatement();
  rs = stmt.executeQuery(queryText);
} catch (Exception e) { }

response.sendRedirect("/path/to/filename.jsp");
%>

The first line has two characters, a less than sign, and a percentage sign. You use this to enclose Java code within a JSP page. You reverse the order of the characters to close the Java code.

The next three lines set up three variables that you use to set up your connection to the database. The third line sets up a SQL query for the database. You will code your query differently than this, as I will show later, but for now, it shows you the basic syntax for inserting a value into a database.

The next section is a try/catch block. This is a Java construct that makes sure that if something goes wrong within the first segment, the processing in the second segment (which in this example is blank) is done. This allows you to provide information to your users if the query is unable to execute. You would typically include HTML code in the second segment to display an error message.

The text that is in red is the text that you will modify to fit your environment. The rest, you should be able to copy in, as is.

The final segment is the code that you would enter to forward the user on to another page once the data has been inserted into the database. You can enter a full URL between the quote marks, or a relative path. It could be as short as response.sendRedirect("file.jsp"), or as long as response.sendRedirect("http://www.mydomainname.com/path/to/filename.jsp").

Creating an insert statement

In the previous section, I showed you the code you need to write to insert a record into a database. In the code that I provided, I used a placeholder for the query you submit to the database. The placeholder I gave you was:

String queryText = "insert into tablename values(\"firstvalue\",\ "secondvalue\",\"thirdvalue\")";

One thing to mention about this placeholder insert query statement is the syntax. If you were typing a query into a standard query interface for a database, you would enter this particular query as follows:

insert into tablename values("firstvalue", "secondvalue", "thirdvalue")

But in this case, because you are coding it in the Java programming language, you need to code it differently. In Java, when you want to add a quotation mark within two sets of quotation marks, you need to preface the quotation mark with a backslash. This basically tells Java, "this isn't the end of the quoted material; put a quote mark in, instead." So, to create the string with the quotation marks in it, you need to preface the quotation marks with a backslash. This is called putting in an escaped quote mark.

When you are collecting information from your users, and inserting that data into a database, you won't know what the values are until you collect them from the users. So, the previous example won't work for what you want to do. You need to modify it by replacing the hardcoded values "firstvalue," "secondvalue," and so on with values that the users have provided you with.

The following example will show you how to create an insert query for inserting text values that your users enter on a form. Later sections will show you how to collect information from checkboxes, radio buttons, list boxes, and so on.

To do that, you will create your SQL query by pasting together some of the text from the original example with the information the users provide. Before you can determine how you will code your page, you need to know two things:

• the structure of your database, including the number of fields in the record you will be adding, and the order of the fields

• the names of the form elements in the referring page

The first part of the placeholder insert query remains the same:

String queryText = "insert into tablename values(

where tablename is the name of the table into which you are inserting data. Next, you will add an escaped quote mark:

String queryText = "insert into tablename values(\"

Now, you are going to add a value that your user has entered to the query, so you need to close this part of the string. To do that, you add another quote mark:

String queryText = "insert into tablename values(\""

Next, you add a plus sign, and the Java code to retrieve the value of the form element that corresponds to the first element in your database table. Let's say that you are collecting the user's first name from a form element named "FirstName," and that it is the first element in the database table. The code for this will look like:

String queryText = "insert into tablename values(\"" + request.getParameter("FirstName")

Let's say that the user has entered the name "Jehosephat" in the FirstName field on the referring page. When the code above gets executed on the server, it will change the getParameter code into the value from the field, and it will end up looking like this:

String queryText = "insert into tablename values(\"Jehosephat

The rest of the examples on this page will have the code that you will add on the first line, and what the string you are building will look like on the second line. Do not add the second line to your JSP pages.

Next, you need to add the code to close the value, put in a comma, and start the next value:

String queryText = "insert into tablename values(\"" + request.getParameter("FirstName") + "\",\"" +

insert into tablename values("Jehosephat","

There's a lot of quotes and slashes after the getParameter syntax! It can be hard to follow. I usually don't try to remember the syntax...I merely open another page in which I have that bit of code, and copy and paste. In fact, I typically copy and paste all of the code I have presented to you in this topic, and then modify it to fit my needs on the page I paste it into.

Now, you can add more getParameter code segments to the query string until you have all of the form elements included. Your query string might look like this:

String queryText = "insert into tablename values(\"" + request.getParameter("FirstName") + "\",\"" + request.getParameter("LastName") + "\",\"" + request.getParameter("Address") + "\",\"" + request.getParameter("City") + "\",\"" + request.getParameter("State") + "\",\"" + request.getParameter("Zip")

insert into tablename values("Jehosephat","Jones","123 Main Street","Jonesville","AR","55013"

This query will collect the user's first and last name, street address, city, state, and zip code. Remember that you must collect these values in the order in which they appear in the database.

When you have finished adding getParameter code to your query (the number you have added matches the number of columns in the table you are inserting into), you add a closing quote mark for the Zip parameter, a closing parenthesis for the values() syntax, an ending quote mark for the string, and a semicolon to end the Java statement:

String queryText = "insert into tablename values(\"" + request.getParameter("FirstName") + "\",\"" + request.getParameter("LastName") + "\",\"" + request.getParameter("Address") + "\",\"" + request.getParameter("City") + "\",\"" + request.getParameter("State") + "\",\"" + request.getParameter("Zip") + "\"")";

This last set of quote marks, backslashes, parentheses, and semicolons is another bit you will probably want to copy and paste.

This shows you how to build an insert statement to retrieve values from text boxes, radio buttons, and list boxes. There is some special processing that you need to perform to collect information from check boxes, which is covered in the following section.

Check boxes

Check boxes allow you to collect true/false information from your users. I opt to store true/false data (also called Boolean data) as character values “Y” and “N,” rather than storing them as true Boolean values. It's simply easier to code. It takes up a smidgen more room per record in the database, but with the type of information that you will be collecting from users, this is most likely an insignificant amount. This section will show you how to insert a record into a database that stores two values: a checked check box, and an unchecked check box.

Let's say that you are collecting data about the primary language of your users, and you have a checkbox in your form with the following syntax:

<input type="checkbox" name="NNSE" value="NNSE"> English is not my primary language

where NNSE = Non-Native Speaker of English. If the user checked the box, you are going to want to add the value “Y” to the database table, otherwise you want to add the value “N.” To do this, you will create a char variable, and you will set the value of the variable based on whether or not the checkbox was checked on the referring page. So, you will add the following code to your page:

char NNSE;

Next, you set the value of the NNSE variable based on whether or not the user checked the checkbox on the referring page:

char NNSE;
if (request.getParameter("NNSE") != null) {NNSE = 'Y';} else {NNSE = 'N';}

The if keyword is followed by a statement within a set of parentheses. What this is saying, is "if the user checked the checkbox, then...." If the user did not check the checkbox, then the getParameter statement will return a null value. The syntax != means "not equal." So, if the getParameter statement returns a value that is not null, then the statement in the first set of braces should be performed, namely, setting the NNSE variable to the value “Y.” If the value is null, then the statement in the second set of braces should be performed (setting the variable to “N”).

At this point, you have created a char variable, and you have set its value to either “Y” or “N.” Next, you create the queryText string variable, just as you did in the previous section. It starts off the same way:

String queryText = "insert into tablename values(\"" +

But this time, instead of adding a getParameter statement, you will add the variable you just created and populated. Your query will look like this:

String queryText = "insert into tablename values(\"" + NNSE +

If you are collecting more than one checkbox value, you would continue adding them to the statement, as in the previous section:

String queryText = "insert into tablename values(\"" + NNSE + "\",\"" + secondvalue + "\",\"" + thirdvalue + "\"")";

Of course, you would need to have created and populated the secondvalue and thirdvalue variables in the preceding lines.