• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

Newbie question: How to pass a value from a text box to a query and return the result.

Hi, I need some help with the basic syntax for retrieving user input, embedding it in a query, passing it to the DB (Oracle) and displaying the result.

I have provided some sample code here, and have been thinking along the lines of doing a postback and parsing the query string into the query. I am not sure if this can be done at all. I know the embedding of the query here is not the best method, at least there should be a stored procedure, so if anyone want to provide me with a better method please feel free.

<sql:transaction dataSource="${conn}">

 <sql:query var="available_offer">
    select distinct City, to_char(Date, 'dd.mm.yyyy')
from Offer
where Offer.Date = // The value from the text box //
and film.film_id = forestilling.film_id
order by Dato
  </sql:query>

</sql:transaction>

<h1>Offer</h1>
<table border="1">

  <%-- Get the column names for the header of the table --%>
  <c:forEach var="columnName" items="${available_offers.columnNames}">
    <th><c:out value="${columnName}"/></th>
  </c:forEach>

  <%-- Get the value of each column while iterating over rows --%>
  <c:forEach var="row" items="${available_offers.rows}">
    <tr>
    <c:forEach var="column" items="${row}">
      <td><c:out value="${column.value}"/></td>
    </c:forEach>

  </tr>
  </c:forEach>
</table>

<br />
<form method="post" action="index2.jsp?date="> // The value from the date text box //
Velg dato: <input type="text" name="date" size="25">
<input type="submit" name="send" value="Send">
<br />
0
Sparkle101
Asked:
Sparkle101
  • 7
  • 6
1 Solution
 
bloodredsunCommented:
<sql:transaction dataSource="${conn}">

 <sql:query var="available_offer">
    select distinct City, to_char(Date, 'dd.mm.yyyy')
from Offer
where Offer.Date = <c:out value='${param.date}'/>
and film.film_id = forestilling.film_id
order by Dato
  </sql:query>
0
 
bloodredsunCommented:
Better yet, use a prepared statement

<sql:transaction dataSource="${conn}">

<sql:query var="available_offer">
    select distinct City, to_char(Date, 'dd.mm.yyyy') from Offer where Offer.Date = ? and film.film_id = forestilling.film_id order by Dato
    <sql:param value='${param.date}'/>
</sql:query>

NB both assume that the name of the parameter from the form (i.e. the name of the text box) is "date"
0
 
Sparkle101Author Commented:
Yes, it should be date, bad translation on my behalf from norwegian.

But, should I not use a postback? What should the action attribute in the form tag be?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
bloodredsunCommented:
<form method="post" action=""> will post back to the original page
e.g.

<form method="post" action="index2.jsp"> will post to the index2.jsp page. You don't need to add the parameter name in the action as it's done for you.
0
 
bloodredsunCommented:
>>But, should I not use a postback?

It's up to you. You can do error checking on this page and check for when there is no parameter if  it breaks your pages.

<c:if test='${empty param.date}' >
this will only be executed if there IS NO request parameter called date
</c:if>

<c:if test='${not empty param.date}' >
this will only be executed if there IS request parameter called date <c:out value='${param.date}'/>
</c:if>

You can also use a c:choose statement for if/else.....
0
 
Sparkle101Author Commented:
OK, looks great, I think I understand it, but: when I include the  <sql:param value='${param.date}'/> in the query tag I get a "illegal column type" error. (I run this on Tomcat). I suppose the question mark indicates the parameter input. If more than one are they dealt with consecutively?

I've tried to put the query block in the if statement, without any luck.

S
0
 
bloodredsunCommented:
Probably as you are trying to enter a NULL into a Date type.

You haven't specified it but I should have guessed earlier that the coumn type is a "Date". In that case you need  <sql:dateParam> rather than <sql:param> and also need to format the date using the fmt lib (which you need to include the taglib declaration):
-----------

<sql:transaction dataSource="${conn}">
<c:if test='${not empty param.date}' >
    <fmt:parseDate var='javaDate' value='${param.date}' dateStyle='short' />
    <sql:query var="available_offer">
        select distinct City, to_char(Date, 'dd.mm.yyyy') from Offer where Offer.Date = ? and film.film_id = forestilling.film_id order by Dato
            <sql:dateParam value='${javaDate}'/>
    </sql:query>
</c:if>
0
 
Sparkle101Author Commented:
Well, I see about the date format, but I've tried a very simple query:

<sql:transaction dataSource="${conn}">

<c:if test='${empty param.City}'>
 <sql:query var="choose_city">
SELECT * FROM City WHERE City = ?
<sql:param value="${param.City}" />
 </sql:query>
</c:if>

</sql:transaction>


<table border="1">

  <%-- Get the column names for the header of the table --%>
  <c:forEach var="columnName" items="${choose_city.columnNames}">
    <th><c:out value="${columnName}"/></th>
  </c:forEach>

  <%-- Get the value of each column while iterating over rows --%>
  <c:forEach var="row" items="${choose_city.rows}">
    <tr>
    <c:forEach var="column" items="${row}">
      <td><c:out value="${column.value}"/></td>
    </c:forEach>

  </tr>
  </c:forEach>
</table>

<br />

<form method="post" action="index2.jsp">
Velg by: <input type="text" name="City" size="25" value="City"><br />


<input type="submit" name="send" value="Send">
</form>

I get the same error. Can you run this?

Thanks

S
0
 
Sparkle101Author Commented:
I have these taglibs included now, do I need more?

<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

0
 
bloodredsunCommented:
>> I have these taglibs included now, do I need more?

Yes, you also need...
<%@ taglib  prefix='fmt' uri='http://java.sun.com/jsp/jstl/fmt'%>


You probably get the error as nothing is being passed into the sql statement the first time. Your if statement only works if there IS no parameter (<c:if test='${empty param.City}'> !!!!!!!!)

It should be:

<c:if test='${not empty param.City}'>
 <sql:query var="choose_city">
SELECT * FROM City WHERE City = ?
<sql:param value="${param.City}" />
 </sql:query>
</c:if>


and a general rule of thumb, name everything with a lower case and separate composite words with underlines "_" as it stops little errors creeping in.

0
 
Sparkle101Author Commented:
I see, thanks!
0
 
bloodredsunCommented:
Cool, glad to help. Thanks for the points and the grade :-)
0
 
Sparkle101Author Commented:
OK, the answer is great, but it only works if I post it to another page. Should it also work when I post it to the same page?
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now