Forums

DREAMWEAVER

This topic is locked

Newbie: Why doesn't my sort work?

Michael Pomeroy

Posted 29 Nov 2001 00:46:23

has voted

Vote

29 Nov 2001 00:46:23 Michael Pomeroy posted:

All my code is below.
I have a database which will be running queries returning between 50 and 150 records.

I have three simple pages: Search, Result, Invoice.
The invoice is working properly, displaying the records chosen from the results page.

To the invoice, I have added four pull-down list menus which allow the user to create a complex sort using four sort parameters (listMenuSort 1,2,3,4 in order of precedence.

I am getting this error:

500 Internal Server Error
/jserv/Invoice.jsp:Exception thrown on line '30' from page 'C:\\Inetpub\\wwwroot\\Beachwear\\jserv\\Invoice.jsp'
java.sql.SQLException: Column not found: Unknown column 'listMenuSort1' in 'order clause'

--------------------------------------------------------------------------
<%@page language="java" import="java.sql.*"%>
<%@ include file="../Connections/connBeachwear.jsp" %>
<%
String rsBeachwear__varCheckbox = "1";
if (request.getParameter ("valueCheckbox"

!=null) {rsBeachwear__varCheckbox = (String)request.getParameter ("valueCheckbox"

;}
%>
<%
Driver DriverrsBeachwear = (Driver)Class.forName(MM_connBeachwear_DRIVER).newInstance();
Connection ConnrsBeachwear = DriverManager.getConnection(MM_connBeachwear_STRING,MM_connBeachwear_USERNAME,MM_connBeachwear_PASSWORD);
String chkValues[]=request.getParameterValues("valueCheckbox"

;
StringBuffer prepStr=new StringBuffer("SELECT ID, Item, Color, Size FROM Beachwear WHERE ID="

;
for(int x=0;x<chkValues.length;++x){
prepStr.append( chkValues[x] );
if((x+1)<chkValues.length){
prepStr.append(" OR ID="

;
}//end if
}//end for loop
PreparedStatement StatementrsBeachwear=ConnrsBeachwear.prepareStatement(prepStr.toString());
ResultSet rsBeachwear = StatementrsBeachwear.executeQuery();
boolean rsBeachwear_isEmpty = !rsBeachwear.next();
boolean rsBeachwear_hasData = !rsBeachwear_isEmpty;
Object rsBeachwear_data;
int rsBeachwear_numRows = 0;
%>
<%
int Repeat1__numRows = -1;
int Repeat1__index = 0;
rsBeachwear_numRows += Repeat1__numRows;
%>
<title>Beachwear Title</title>
<body bgcolor="#FFFFFF">
<p> </p>
<p>

//New code attempting to use pull-down List Menu variables to perform complex sort:

<form name="form1" method="get" action="Invoice.jsp ORDER BY listMenuSort1 ", "listMenuSort2", "listMenuSort3", "listMenuSort4">

<p><br>
INVOICE<br>
<br>
</p>
<% while ((rsBeachwear_hasData)&&(Repeat1__numRows-- != 0)) { %>
<table width="75%" border="1">
<tr>
<td width="13%">ID:</td>
<td width="87%"><%=(((rsBeachwear_data = rsBeachwear.getObject("ID"

)==null || rsBeachwear.wasNull())?"":rsBeachwear_data)%></td>
</tr>
<tr>
<td width="13%">ITEM:</td>
<td width="87%"><%=(((rsBeachwear_data = rsBeachwear.getObject("Item"

)==null || rsBeachwear.wasNull())?"":rsBeachwear_data)%></td>
</tr>
<tr>
<td width="13%">COLOR:</td>
<td width="87%"><%=(((rsBeachwear_data = rsBeachwear.getObject("Color"

)==null || rsBeachwear.wasNull())?"":rsBeachwear_data)%></td>
</tr>
<tr>
<td width="13%">SIZE:</td>
<td width="87%"><%=(((rsBeachwear_data = rsBeachwear.getObject("Size"

)==null || rsBeachwear.wasNull())?"":rsBeachwear_data)%></td>
</tr>
</table>
<%
Repeat1__index++;
rsBeachwear_hasData = rsBeachwear.next();
}
%>
<p> </p>
<br>
<table width="37%" border="1">
<tr>
<td width="16%">Parameter</td>
<td width="11%">
<div align="center">1</div>
</td>
<td width="20%">
<div align="center">2</div>
</td>
<td width="20%">
<div align="center">3</div>
</td>
<td width="33%">
<div align="center">4</div>
</td>
</tr>

//New pull-down list Menus:
<tr>
<td width="16%">
<input type="submit" name="Submit" value="Sort">
</td>
<td width="11%">
<select name="listMenuSort1" size="1">
<option value="ID" selected>ID</option>
<option value="Item">ITEM</option>
<option value="Color">COLOR</option>
<option value="Size">SIZE</option>
</select>
</td>
<td width="20%">
<select name="select" size="1">
<option value="ID">ID</option>
<option value="Item" selected>ITEM</option>
<option value="Color">COLOR</option>
<option value="Size">SIZE</option>
</select>
</td>
<td width="20%">
<select name="select2" size="1">
<option value="ID">ID</option>
<option value="Item">ITEM</option>
<option value="Color" selected>COLOR</option>
<option value="Size">SIZE</option>
</select>
</td>
<td width="33%">
<select name="select3" size="1">
<option value="ID">ID</option>
<option value="Item">ITEM</option>
<option value="Color">COLOR</option>
<option value="Size" selected>SIZE</option>
</select>
</td>
</tr>

</table>
</form>
<%
rsBeachwear.close();
ConnrsBeachwear.close();
%>

Replies

Joel Martinez

Replied 01 Dec 2001 15:30:46

01 Dec 2001 15:30:46 Joel Martinez replied:

don't you see what's happening.... <b>chkValues[x]</b> is returning the name of the listbox and not the value...

unfortunately I don't know JSP, but I think if you fix this little syntax problem, the code will work.

Joel Martinez [ ]
----------
E-Commerce Concepts with Ultradev...pre-order yours at
www.basic-ultradev.com/ecomm_concepts/

Michael Pomeroy

Replied 01 Dec 2001 17:25:52

01 Dec 2001 17:25:52 Michael Pomeroy replied:

Joel,
Now I have an exception handling issue. I've gotten rid of the irrelevant variables, and corrected the logic of the sort.
I've enclosed the different variables with exception handling debugger code, but with no results except the original error:
--------------------------------
Error:
500 Internal Server Error /jserv/Invoice3.jsp: java.lang.NullPointerException
at jrun__jserv__Invoice32ejsp13._jspService(jrun__jserv__Invoice32ejsp13.java:76)
--------------------------------
Sample debug use:
for(int x=0;x<chkValues.length;++x){
try{
prepStr.append(chkValues[x]);
}catch(NullPointerException e){out.println("Exception 1 : "+e.toString()+"chkValues[x]="+chkValues[x]);}
-------------------------------------
<%@page language="java" import="java.sql.*"%>
<%@ include file="../Connections/connBeachwear.jsp" %>
<%
String rsBeachwear__varCheckbox = "1";
if (request.getParameter ("valueCheckbox"

!=null) {rsBeachwear__varCheckbox = (String)request.getParameter ("valueCheckbox"

;}
%>
<%
//NEW SORT VARIABLES:
String rsBeachwear__name = "ID";//default sort value
if (request.getParameter ("order"

!=null) {rsBeachwear__name = (String)request.getParameter ("order"

;}
String rsBeachwear__sort = "ASC";//default sort value
if (request.getParameter ("sort"

!=null) {rsBeachwear__sort = (String)request.getParameter ("sort"

;
StringBuffer prepStr=new StringBuffer("SELECT ID, Item, Color, Size FROM Beachwear WHERE ID="

;
for(int x=0;x<chkValues.length;++x){
try{ //DEBUG CODE
prepStr.append(chkValues[x]);
}catch(NullPointerException e){out.println("Exception 1 : "+e.toString()+"chkValues[x]="+chkValues[x]);} //DEBUG CODE
if((x+1)<chkValues.length){
prepStr.append(" OR ID="

;
}//end if
}//end for loop
prepStr.append(" ORDER BY '%" + rsBeachwear__name + "%' '%" + rsBeachwear__sort + "%'"

; //NEW SQL SORT CODE:
PreparedStatement StatementrsBeachwear=ConnrsBeachwear.prepareStatement(prepStr.toString());
ResultSet rsBeachwear = StatementrsBeachwear.executeQuery();
Object rsBeachwear_data;
%>
<title>Beachwear Title</title>
<body bgcolor="#FFFFFF">
<p> </p>
<p> </p>
<p><br>
INVOICE<br>
</p>
<%//FORM "GET" METHOD<%>
<form name="form1" method="get" action="Invoice3.jsp">
<p><br>
</p>
<%while(rsBeachwear.next()){ //NEW LOOP; RELEVANT, NOT POINTLESS %>
<table width="75%" border="1">
<tr>
<td width="13%">ID:</td>
<td width="87%"><%=(((rsBeachwear_data = rsBeachwear.getObject("ID"

)==null || rsBeachwear.wasNull())?"":rsBeachwear_data)%></td>
</tr>
<tr>
<td width="13%">ITEM:</td>
<td width="87%"><%=(((rsBeachwear_data = rsBeachwear.getObject("Item"

)==null || rsBeachwear.wasNull())?"":rsBeachwear_data)%></td>
</tr>
<tr>
<td width="13%">COLOR:</td>
<td width="87%"><%=(((rsBeachwear_data = rsBeachwear.getObject("Color"

)==null || rsBeachwear.wasNull())?"":rsBeachwear_data)%></td>
</tr>
<tr>
<td width="13%">SIZE:</td>
<td width="87%"><%=(((rsBeachwear_data = rsBeachwear.getObject("Size"

)==null || rsBeachwear.wasNull())?"":rsBeachwear_data)%></td>
</tr>
</table>
<%
}
%>
<p> </p>
<br>
<table width="54%" border="1">
<tr>
<td width="29%">
<div align="center">Parameter </div>
</td>
<td width="28%">
<div align="center">1</div>
</td>
<td width="43%">
<div align="center">2</div>
</td>
</tr>
<%//TWO NEW PULL-DOWN MENUS FOR "ORDER BY" SQL SORT%>
<tr>
<td width="29%">
<div align="center">
<input type="submit" value="Sort Now">
</div>
</td>
<td width="28%">
<div align="center">
<select name="order" size="1">
<option value="ID" selected>ID</option>
<option value="Item">Item</option>
<option value="Color">Color</option>
<option value="Size">Size</option>
</select>
</div>
</td>
<td width="43%">
<div align="center">
<select name="sort" size="1">
<option value="ASC" selected>Ascending</option>
<option value="DESC">Descending</option>
</select>
</div>
</td>
</tr>
</table>
</form>
<%
rsBeachwear.close();
ConnrsBeachwear.close();
%>

Reply to this topic

Latest Extensions

App Connect Summernote File Upload Upload different files in Summernote Editor
App Connect Summernote Simple, yet powerful and modern WYSIWYG Editor
Bootstrap 4 Dynamic Table Generator 2 Automatically generate dynamic data tables
Bootstrap 4 Dynamic Form Generator 2 Auto generate perfect Bootstrap 4 dynamic forms in seconds
App Connect Bootstrap 4 Dynamic Paging Generator Auto generate awesome dynamic Bootstrap 4 paging in seconds
App Connect Event Calendar The most powerful full-sized event calendar for Dreamweaver
DMXzone App Connect Next generation client side framework for data-driven web sites and apps
DMXzone Database Custom Query Create advanced, complex custom SQL queries with ease

Latest Videos

Using App Connect Autocomplete Learn how to add autocomplete functionality on your page, using the App Connect Autocomplete
Creating Custom SQL Queries Learn how to create custom SQL Queries with DMXzone Database Custom Query
Restricting Access to Your Pages Learn how to restrict access to your pages, using DMXzone Security Provider 2
Adding Log Out Functionality with DMXzone Security Provider 2 Learn how to add log out functionality to your page with DMXzone Security Provider 2
Creating a Log In Page with DMXzone Security Provider 2 Learn how to create a log in page with DMXzone Security Provider 2
Setting Up Site Security Settings Learn how to setup your site security, using DMXzone Security Provider 2
Generating an Update Record Form Learn how to create an update record form, using the Bootstrap 4 Dynamic Form Generator 2
Generating an Insert Record Form Learn how to create an insert record form, using the Bootstrap 4 Dynamic Form Generator 2