Announcement

**violinsoundcool** · February 7, 2008, 02:39 PM

Re: Constructing an SQL string in Java

Yeah, here's index.jsp. It decided to let me put the <form> tag at the top. I got results when I searched and hit "submit." However, after hitting the submit button, my computer was getting bogged down, and it took a long time. Once it was up and loaded, my computer continued to move slowly. I have 1 GB of RAM, should I have this much trouble? I didn't with the other application.

Attached Files

index2jsp.txt (1.6 KB, 118 views)

**bryce4president** · February 7, 2008, 02:43 PM

Re: Constructing an SQL string in Java

For starters...your </form> needs to be directly above the </body> tag. How many results are you expecting to get back from your search?

**bryce4president** · February 7, 2008, 02:45 PM

Re: Constructing an SQL string in Java

And why the empty <td></td> tags? A web page loads the entire table from start tag to end tag before it displays it. So the more stuff in your table the longer it will take before it loads on the screen...I ran into a problem with long load times because of very large result sets.

I've since fixed this by using paging. But that might not be the problem you are having though.. Can I see your results.jsp too?

**violinsoundcool** · February 7, 2008, 02:45 PM

Re: Constructing an SQL string in Java

...maybe 50.

**violinsoundcool** · February 7, 2008, 02:50 PM

Re: Constructing an SQL string in Java

The empty <td></td> tags are there to create a third column. There is probably a better way to do it, but I'm ignorant of it so far. Here's the Results.jsp.

Attached Files

results2jsp.txt (1.7 KB, 118 views)

**bryce4president** · February 7, 2008, 03:00 PM

Re: Constructing an SQL string in Java

This should be your new index2.html file

Code:

<&#37;@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %><%@ taglib uri="http://java.sun.com/jstl/fmt" prefix="fmt" %>
<html>
<head>
<title>Scrap Load Search</title>
</head>
<body bgcolor="000099">
<p align="center"><font face="arial" color="ffcc00" size="6"><b>Scrap Load Search</b></font></p>
<hr color="ffcc00"/>

<form name="ScrapSearch" action="ScrapSearchServlet" method="POST">
	<table width="100%" cellpadding="2" align="center">
		<tr>
			<td width="45%" align="right" valign="top"><b><font face="arial" color="ffcc00" size="5">Name:</font></b></td>
			<td width="55%" align="left" valign="middle"><input type="text" name="name"></td>
			<td></td>
		</tr>
		<tr>
			<td align="right" valign="top"><b><font face="arial" color="ffcc00" size="5">City:</font></b></td>
			<td align="left" valign="middle"><input type="text" name="city"></td>
			<td></td>
		</tr>
		<tr>
			<td align="right" valign="top"><b><font face="arial" color="ffcc00" size="5">State:</font></b></td>
			<td align="left" valign="middle"><input type="text" size="2" maxlength="2" name="state"></td>
			<td></td>
		</tr>
		<tr>
			<td align="right" valign="top"><b><font face="arial" color="ffcc00" size="5">Product:</font></b></td>
			<td align="left" valign="middle"><input type="text" size="7" maxlength="7" name="item"></td>
			<td></td>
		</tr>
	</table>
	<br />
	<table width="95%" cellpadding="3">
		<tr>
			<td width="50%" align="right"><input type="submit" value="Submit"></td>
			<td width="50%" align="left"><form name="Done" onclick="self.close()"><input type="button" value="Done"></td>
		</tr>
	</table>
</form>
</body>
</html>

you had multiple </form> tags....i got rid of them and put just 1 where it belonged.

**violinsoundcool** · February 7, 2008, 03:46 PM

Re: Constructing an SQL string in Java

It underlines the second <form> and says "invalid locatio of tag(form)."

**bryce4president** · February 7, 2008, 04:01 PM

Re: Constructing an SQL string in Java

I didn't see that second form tag....what exactly is that inner form supposed to do? You want the browser to close when you click the "Done" button?

Let me know what you are trying to accomplish....you could put the </form> tag back in after the <input> and the warning will go away. I'm just not sure that is the best solution...I could be wrong though

**violinsoundcool** · February 7, 2008, 04:13 PM

Re: Constructing an SQL string in Java

Yes. I'm wanting the "Done" button to close the window. Even with that problem, the submit button still does its job. Now, it's a problem with the sql within the servlet recognizing the variable names given to the input fields in index.jsp. Here is how the sql section in the servlet looks now.

Code:

StringBuffer sql = new StringBuffer(); 
			
sql.append("SELECT tdydno, tdload, tltype, tlacctno, taname, tlcymd, tlamount, 
              tdstatus, tdsrn FROM SCF031J3 WHERE tdstatus='C' ");           

     if(!"".equals(request.getParameter("name")))
     {}
     else
     {
          sql.append("AND ucase(taname) LIKE ucase ('%name%') ");
     }

     if(!"".equals(request.getParameter("city")))
     {}
     else
     {
          sql.append("AND ucase(tacity) LIKE ucase ('%city%') ");
     }
			
     if(!"".equals(request.getParameter("state")))
     {}
     else
     {
          sql.append("AND ucase(tastate) LIKE ucase ('%state%') ");
     }
			
     if(!"".equals(request.getParameter("item")))
     {}
     else
     {
          sql.append("AND ucase(tditem) LIKE ucase ('%item%') ");
     }
			
sql.append("ORDER BY tlcymd DESC");
			
ResultSet MyQuery = ScrapSearchRS.myRS(sql.toString(), opnConn);

If I replace ('%name%') with ('%John%') and the same to the others, then I get correct results. So, it makes me think that the user inputs in fields name, city, state, and item are not being put into the sql string. How do I make sure that this happens?

**bryce4president** · February 8, 2008, 07:10 AM

Re: Constructing an SQL string in Java

You have to code that like this...

sql.append(AND ucase(taname) LIKE ucase('%");
sql.append(name);
sql.append("%')");

you have to end that append and close up the quotes, then when you append(name) the value of name gets put in there.

**violinsoundcool** · February 8, 2008, 08:37 AM

Re: Constructing an SQL string in Java

Cool. Thanks. There's still something wrong with the variables name, city, state, and item. The errors say they cannot be resolved. Should I have defined them somewhere?

**bryce4president** · February 8, 2008, 08:40 AM

Re: Constructing an SQL string in Java

What are name, city, state, and item? Where are you getting their values?

**violinsoundcool** · February 8, 2008, 08:45 AM

Re: Constructing an SQL string in Java

I think the first line of the following code does that, but once again, I could be mistaken.

Code:

if(!"".equals(request.getParameter("name")))
                {}
	else
	{
		sql.append("AND ucase(taname) LIKE ucase ('%");
		sql.append(name);
		sql.append("%')");
	}

**bryce4president** · February 8, 2008, 08:50 AM

Re: Constructing an SQL string in Java

do you want to use the form parameter there? That looks like that is what you are doing. In this case you could need to change....

sql.append(name);

to

sql.append(request.getParameter("name"));

You are just checking the parameter to see if its empty. You aren't actually storing it in a program variable.

**violinsoundcool** · February 8, 2008, 09:12 AM

Re: Constructing an SQL string in Java

Okay. I've got to solve this server issue. At least every other time that I try to run something, it takes a looooooong time. Then, it stops responding. The search that I entered was only supposed to return 27 results. When I open the task manager, it shows that javaw.exe is taking up 150 MB! Surely, it's not supposed to do that. What should I do?

Announcement

Constructing an SQL string in Java

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment