Dynamic Content - Crying for help ...... Urgent
I am new to this forum, maybe this issue has been resolved already. Please Help!
I have been asked to build a small system that will do certain things. I am using Tomcat with JSP. The connection and the interaction to the database are great. The only issue I am having is that I don't know how to link/convert the result of the query displayed on the screen to a clickable content i.e. a link.
Eg: When the user wants to view all the European cities, he will click on view all cities on a web page. The code behind the "view" button is a select query that will be run and display back all the cities from a database. My problem is that I don't know how to make the result of that query a link. Let say, if the user wants to click on one of the cities, example Dublin, how can i generate a link from a dynamic page?
I hope it makes sense!
# 1
How much do you know about HTML?
Basically you just have to generate <a href=...> tags for each of the results.
eg
<a href="selectCity?id=1">Dublin</a>
<a href="selectCity?id=2">Manchester</a>
<a href="selectCity?id=3">Liverpool</a>
Presumably you have a list of city results. The jsp page would then loop through that list, and create an html link for each city.
# 2
Thank you for your reply. I am pretty much ok with html.
I should have mentioned earlier that I am new to the JSP business. Your reply is so advanced that I didn't know what to do.
Like I said, I need some codes that will activate a new query from whatever result the user would have on their screen.
E.g. If the user wants to view all the branches starting with B from a database, they can run the query and when the result is displayed example BLANCHARD, they should then be able to click on BLANCHARD and a new select query should be run again in order to display all the relevant information regarding BLANCHARD.
I am using JSP (JSTL) with Tomcat. I am looking for the full working codes to do this, please! Thank you for you help
# 3
> Your reply is so advanced that I didn't know what to do.
Really? I couldn't explain it more simple ..
Try to think and code in steps. Don't expect that this all is done magically with one class and one method and/or one tag ;)
> E.g. If the user wants to view all the branches starting with B from a database,
> they can run the query and when the result is displayed example
> BLANCHARD, they should then be able to click on BLANCHARD and a new
> select query should be run again in order to display all the relevant
> information regarding BLANCHARD.
This is perfect step by step example. Code accordingly. If you're stucking during one step, please be specific in the problem description.
# 4
OK. Thank you for that.
I am working with steps:
My application does the following:
Step 1: The user logs in
Step2: The system verifies the logging details, and if they are correct, he can proceed if not, the loggin_error.jsp page is displayed prompting the user to enter again the correct details.
Step3: If the details are correct, the user can search the database according to some criteria.
Step4: The system displays back the results of the search.
This is where I am stuck because, the user cannot click on whatever result that has been displayed. I need something that will run the query behind the scene.
This is my code to display the result from the search:
<sql:setDataSource var="TableView" driver="sun.jdbc.odbc.JdbcOdbcDriver" url="jdbc:odbc:Reports"/>
<sql:query var="all_fields" dataSource="${TableView}" scope="session">
SELECT MegaDoc.name, MegaDoc.Description
FROM MegaDoc
WHERE MegaDoc.name = ?
</sql:query>
<table>
<tr>
<th>Name</th>
<th>Description</th>
</tr>
<c:forEach items = "${all_fields.rows}" var ="row" >
<tr>
<td align="left"><font size="2"><c:out value = "${row.name}" /></font></td>
<td align="left"><font size="2"><c:out value = "${row.Description}" /></font></td>
</tr>
</c:forEach>
</table>
Thank you
Message was edited by:
Guy_Mpiana
# 5
Please elaborate "the user cannot click".
Are there actually links? E.g. <a href="showdetails.jsp?id=1">show details</a>
Or maybe buttons? E.g. <input type="submit" value="show details">
If so, what exactly happens when an user clicks on this link or button?
Edit
Now I see you've edited your message and added some code.
Well .. Add links. Where did you expect that the user can click on? =) And you told that you was "pretty much ok" in HTML.
By the way .. The use of <font> is deprecated. Use CSS.
# 6
fetch the values from the database.
use the html link and append the query string with jsp values.
like
<a href = "test.jsp?<%=put the jsp value here%>Click here</a>
after cliking the link use
request.getParameter(); to get the value from the page.Then make Db queries using the values">
# 7
Hi BalusC
>Well .. Add links. Where did you expect that the user can click on?
The result of the code i submitted looks like this in a table if the user wants to view all the names starting with A:
NameDescription
Alain Murphy Manager
Alfred TomSupervisor
So, I want the user to be able to click on any name in the Name column if he wants to view more details or edit
Regards
# 9
> Yes. Add links :)That's exactly my point. I don't know how to add link in the <c:out value = "${row.name}" />And this new link should be a query that will run behind the scene.
# 10
Again: links have this format:<a href="url">click here</a>Go figure.
# 11
Maybe there's a small misunderstanding. I know how to use the traditional <a href ="blah blah blah">
.
My point is the search result will be created dynamically from a previous query: SELECT name from MYTABLE
. Depending on whatever result that will be displayed eg:
NameDescription
BalusCProgrammer
The name BalusC should be automatically activated like this:
BalusC allowing the user to click on it. And when the user does so, the new query will be run behind the scene saying:SELECT * from MYTABLE where Name = 'BalusC'
.
I hope this makes sense.
# 12
appand the name with the url
# 13
> appand the name with the urlMan, like I said before, I am new into JSP/Java business. I don't how to do it.Thank you!
# 14
i previously posted some code.did u got anything from that?
# 15
> > appand the name with the url
>
> I don't how to do it.
Finally, after a long time, there is the exact problem!!
Now we can provide you the suitable answer: you can use scriptlets to inject local variables in HTML:<!-- First define it -->
<% String value = "Hello World"; %>
<!-- Show it in HTML -->
HTML goes here.
<h1><%= value %></h1>
# 16
i think now its clear.......?
# 17
Could u please have a look on how I just tried to do <a href="newtest.jsp"><c:out value="${row.name}"/></a>
and in the file newtest.jsp I have the sql statement of Select * from MYTABLE.
But I keep getting everything back again on the next screen. It seems that it runs the query for every name displayed.
Now I think the problems lies on the SQL Statement.
This is how i did: <sql:setDataSource var="TableView" driver="sun.jdbc.odbc.JdbcOdbcDriver" url="jdbc:odbc:Reports"/>
<sql:query var="all_fields" scope="application" dataSource = "${TableView}">
SELECT Name, Description
FROM MYTABLE
WHERE Name = '${currentName}'
</sql:query>
<c:set var = "currentName" value = "${param.name}" scope = "application" />
<table width="800" >
<tr>
<th>Name </th>
<th>Description</th>
</tr>
<c:forEach items = "${all_fields.rows}" var ="row" >
<tr height="10">
<td><c:out value="${row.name}"/></td>
<td ><c:out value = "${row.description}" /></td>
</tr>
</c:forEach>
</table>
# 18
Couple of points.
I'm not a big fan of SQL in a JSP. In the purist approach, JSPs are meant for display only. Your database code should be in a servlet/bean. And you should probably use a JNDI datasource rather than making a connection every time.
What database are you using? MSAccess? If possible I would recommend a driver other than the JDBC ODBC bridge. That thing has given me no end of trouble.
Now, do you have ids in your table MyTable? Or are names a unique identifier?
I am a big fan of using unique ids and numbering everybody :-)
Anytime you refer to a person via link, you use their number rather than their name.
</rants which you probably can't understand ;-) >
--
Ok, going back to basics, Lets assume we have two pages
Page 1: listUsers.jsp: displays a list of users
Page2: showUser.jsp: displays the details for ONE user. The user to display the information for is passed in via the request parameter "userid"
Currently you have listUsers.jsp (or something very close to it)
<sql:setDataSource var="TableView" driver="sun.jdbc.odbc.JdbcOdbcDriver" url="jdbc:odbc:Reports"/>
<sql:query var="all_fields" scope="application" dataSource = "${TableView}">
SELECT id, Name, Description
FROM MYTABLE
</sql:query>
<table width="800" >
<tr>
<th>Name </th>
<th>Description</th>
</tr>
<c:forEach items = "${all_fields.rows}" var ="row" >
<%-- // build up a url for the current link --%>
<c:url value="showUser.jsp" var="showUserURL">
<c:param name="userid" value="${row.id}"/>
</c:url>
<tr height="10">
<%-- // render a url for the name --%>
<td><a href="<c:out value="${showUserURL}"/>"><c:out value="${row.name}"/></a></td>
<td ><c:out value = "${row.description}" /></td>
</tr>
</c:forEach>
</table>
You will see that instead of just displaying the name, I have included the code there to make that name a hyperlink.This would create a list of users, each name being a hyperlink you can click that would go to showUser.jsp, passing along the userid to display.
On showUser.jsp, you would have a seperate query that would run and retrieve that users information based on the userid passed in.
Is that clear enough?
Cheers,
evnafets
# 19
Thank you very much for your help Evnafets.
I am using Ms Access and the JDBC ODBC bridge because I am only new in this business and I am still learning.
I do have IDs in the table and they are unique identifier. I have included the piece of code that you have given and it has activated the names to be hyperlink, thank you again for that. And if I put the mouse over a name, I could easily see the full URL being activated like this:
http://localhost/showUser.jsp?userid=115
depending on the userid.
The problem now is in my showUser.jsp, it seems like the query isn't working properly. I have the following:
<sql:setDataSource var="TableView" driver="sun.jdbc.odbc.JdbcOdbcDriver" url="jdbc:odbc:Reports"/>
<sql:query var="all_fields" scope="application" dataSource = "${TableView}">
SELECT MegaDoc.id, MegaDoc.Name, MegaDoc.Description
FROM MegaDoc
WHERE MegaDoc.id ='${userid}'
</sql:query>
<table width="800" >
<tr>
<th align="left"><font color="#0000CC" size="1">WBS No</font></th>
<th align="left"><font color="#0000CC" size="1">CA No</font></th>
<th align="left"><font color="#0000CC" size="1">Description of work</font></th>
</tr>
<c:forEach items = "${all_fields.rows}" var ="row" >
<tr height="10">
<td align="left"><font size="2"><c:out value="${row.WBS_No}"/></font></td>
<td align="left"><font size="2"><c:out value = "${row.CA_No}" /></font></td>
<td align="left"><font size="2"><c:out value = "${row.Description_Of_Work}"/></font></td>
</tr>
</c:forEach>
</table>
The reason why I used WHERE MegaDoc.id='${userid}' in my query is that I am taking the value of userid from the previous page. But what I am getting as a result is the full list again of all the names that have been displayed previously. Please don't get mad at me. I am so confused now.
# 20
Is there anyone who knows how to fix the above issue? Please help me if you do!
# 21
Ok, the better way of writing that query using JSTL is to use the <c:param> tag:
<sql:query var="all_fields" scope="application" dataSource = "${TableView}">
SELECT MegaDoc.id, MegaDoc.Name, MegaDoc.Description
FROM MegaDoc
WHERE MegaDoc.id = ?
<c:param value="${param.userid}"/>
</sql:query>
Try running that query against Access. Does it return what you want it to return?
Also, you have to use ${param.userid} to get the request parameter value correctly.
You might want to test it like this just to make sure it comes through:
You selected id <c:out value="${param.userid}"/>
# 22
><c:param value="${param.userid}"/>
evnafets , you are a star. Thanks for ending up my nightmare. I completely forgot that I was supposed to include that param tag. Actually I used: <sql:param value="${param.userid}" />
And it works fine.
Once again, thank you!
# 23
Hello again, I am back with almost the same issue. I have done so many things but I am stuck again. I need your help.
After a user has viewed whatever information they need to view, they can click on any name and view all the relevant information concerning that name (person). I have two different pages:
1. processData.jsp
This page displays back the name of the person that has been clicked with different v alues e.g. Name, D.O.B, Address. There's a button on this page that says Modify. If the user clicks on it, it will take him to the next page editContent.jsp
2.editContent.jsp
This page has a series of input fields that display back all the values from the previous page. I used a series of <input type="text" size="30" name="myaddress" value = "<c:out value="${row.address}"/>"> to do so. Everything works fine so far. At this stage, the connection to the database is still ok. And also, this is where the user can change directly into the input fields values like addresses, etc.
There's a submit button that can be clicked on to allow the user to save the change. This button connects to an Update Query behind the scene.
This is the content of the Update Query file:
<sql:setDataSource var="TableView" scope="application" driver="sun.jdbc.odbc.JdbcOdbcDriver" url="jdbc:odbc:Reports"/>
<sql:update var="all_fields" scope="application" dataSource = "${TableView}">
UPDATE MegaDoc
SET MegaDoc.address='${myaddress}'
WHERE MegaDoc.id='${userid}'
</sql:update>
<jsp:forward page="confirmChange.jsp"/>
The above Query runs ok without errors and the confirmChange.jsp page is displayed OK. But it doesn't update the Database. If I include this code: <c:out value="${param.myaddress}"/> in the confirmChange.jsp page, I can view this new change. But it doesn't update the Database
However, if I include the following: <sql:param value = "${myaddress}" />
<sql:param value = "${userid}" />
UPDATE MegaDoc
SET MegaDoc.address='${myaddress}'
WHERE MegaDoc.id='${userid}'
<sql:param value = "${myaddress}" />
<sql:param value = "${userid}" />
</sql:update>
it gives the apache error page.
Could someone have a look and tell me what's wrong?
Thank you
# 24
Again I will point out that SQL code like this more properly belongs in a java bean, rather than using JSTL SQL tags.
These tags are not meant for much more than
1 - small trivial applications or
2 - prototyping
The most obvious thing in your code so far, is that you have neglected question marks as parameter place holders:
<sql:update var="all_fields" scope="application" dataSource = "${TableView}">
UPDATE MegaDoc
SET MegaDoc.address=?
WHERE MegaDoc.id=?
<sql:param value = "${myaddress}" />
<sql:param value = "${userid}" />
</sql:update>
Don't know if that will fix it or not.
Can you post the stacktrace that you get from tomcat?
Particularly the second half of it which shows the "root cause"
# 25
>Again I will point out that SQL code like this more properly belongs in a java bean, rather than using JSTL SQL tags.
So far, I have only learned the JSTL approach. I am intending to do something else in the coming days.
I have once tried to use the question marks but they don't work either. Hereunder is the result from tomcat:
root cause
javax.servlet.ServletException: javax.servlet.jsp.JspException:
UPDATE MegaDoc
SET MegaDoc.address=?
WHERE MegaDoc.id=?
: [Microsoft][ODBC Microsoft Access Driver]Invalid SQL data type
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:851)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:784)
org.apache.jsp.editContentQry_jsp._jspService(editContentQry_jsp.java:103)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:384)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:320)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:266)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
root cause
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]Invalid SQL data type
sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958)
sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
sun.jdbc.odbc.JdbcOdbc.SQLBindInParameterNull(JdbcOdbc.java:987)
sun.jdbc.odbc.JdbcOdbcPreparedStatement.setNull(JdbcOdbcPreparedStatement.java:361)
sun.jdbc.odbc.JdbcOdbcPreparedStatement.setObject(JdbcOdbcPreparedStatement.java:1085)
sun.jdbc.odbc.JdbcOdbcPreparedStatement.setObject(JdbcOdbcPreparedStatement.java:1072)
sun.jdbc.odbc.JdbcOdbcPreparedStatement.setObject(JdbcOdbcPreparedStatement.java:1063)
org.apache.taglibs.standard.tag.common.sql.UpdateTagSupport.setParameters(Unknown Source)
org.apache.taglibs.standard.tag.common.sql.UpdateTagSupport.doEndTag(Unknown Source)
org.apache.jsp.editContentQry_jsp._jspx_meth_sql_005fupdate_005f0(editContentQry_jsp.java:181)
It looks like the two parameters are not being passed in appropriately.
# 26
Ok, that error message tells us the the JDBCODBC driver is **** ;-)
It als tells us that the value is being set to null.
sun.jdbc.odbc.JdbcOdbc.SQLBindInParameterNull(JdbcOdbc.java:987)
sun.jdbc.odbc.JdbcOdbcPreparedStatement.setNull(JdbcOdbcPreparedStatement.java:361)
So one or both of the parameters are null, hence the error.
Maybe try using ${param.myaddress} instead of just address.
Try printing out the values:
<c:out value="${param.myaddress}"/>
<c:out value="${param.userid}"/>
From what you mentioned in editContent.jsp, you had a field 'myaddress'
Do you also have userid on that page?
might need
<input type="hidden" name="userid" value="c:out value="${row.userid}"/>">
# 27
evnafets, Once again, that works perfectly. You are a Star!
# 28
Hi there,
Just for my own records:
I have been building a small system that interacts OK with the user in different aspects. I was using Tomcat Apache as a client server on my PC and I would like to know if is it possible to move this application to a live server or not. If it's, how can I install the Tomcat on this server?
One more thing, is it possible to link a web application to an oracle database or SQL-type database using Tomcat? What is the connection link (I mean, how do you do this)?
Thank you
# 29
> Hi there,
>
> Just for my own records:
>
> I have been building a small system that interacts OK
> with the user in different aspects. I was using
> Tomcat Apache as a client server on my PC and I
> would like to know if is it possible to move this
> application to a live server or not. If it's, how can
> I install the Tomcat on this server?
http://tomcat.apache.org/lists.html
> One more thing, is it possible to link a web
> application to an oracle database or SQL-type
> database using Tomcat? What is the connection link (I
> mean, how do you do this)?
You would go for JDBC connection pooling. The instructions are different for different versions of Tomcat.
What is your version of Tomcat?
Go here: http://tomcat.apache.org/
Look under Documentation
Pick the Version
Then pick JDBC DataSources or JNDI Resources
>
> Thank you
# 30
Hi there, could someone please help on this subject? I have the code to generate the "next" and "previous" on a search result page. Everything works fine but I need to implement a new function and I don't know how to do it. From the result page, the user has to have the ability to see how many pages are possible in the queue and also if I can have the page numbers as well. This is how it would look like:
Page 1 of 5 .....................................................1, 2, 3, 4, 5
__
| Name | Surname | Phone Number | Fax No |
__|
| ............................................................................................ |
| ............................................................................................ |
|__|
Next .......................................Previous
And here's my code for a next and previous link:
<c:choose>
<c:when test="${param.start > 0}">
<a href="Home.jsp?start=<c:out value="${param.start - noOfRows}"/>">Previous name</a> </c:when>
<c:otherwise></c:otherwise>
</c:choose>
<c:choose>
<c:when test="${all_fields.limitedByMaxRows}">
<a href="Home.jsp?start=<c:out value="${param.start + noOfRows}"/>">Next name</a> </c:when>
<c:otherwise>
</c:otherwise>
</c:choose>
And i have this declared like this:
<c:set var="noOfRows" value="5" />
<sql:setDataSource var="TableView" driver="sun.jdbc.odbc.JdbcOdbcDriver" url="jdbc:odbc:Reports"/>
<sql:query var="all_fields" dataSource="${TableView}" scope="session" startRow="${param.start}" maxRows="${noOfRows}">
Thank you for your help!
# 31
Anyone to help with the above issue? Please I need your help
