Forums Register Login

SQL error

+Pie Number of slices to send: Send
Hey,
I'm back with yet another SQLException I don't understand, or know how to fix. In this case my controller servlet forwards a string and a connection to my model and the model is to search for a that string within the artist field of my cd database.

Here is my model function:


Here is the exception:
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source) at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source) at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(Unknown Source) at infostore.models.searchModel.findArtists(searchModel.java:14) at infostore.controllers.SearchController.doPost(SearchController.java:36) at javax.servlet.http.HttpServlet.service(HttpServlet.java:760) at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:243) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:201) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566) at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943) at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2344) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566) at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:170) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:170) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:462) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:163) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943) at org.apache.catalina.connector.http.HttpProcessor.process(HttpProcessor.java:1011) at org.apache.catalina.connector.http.HttpProcessor.run(HttpProcessor.java:1106) at java.lang.Thread.run(Unknown Source)

I'm using MS Access, and if anyone has any suggestions I'd really appreciate it!!
+Pie Number of slices to send: Send
Jason,
Although I'm not terribly familiar with Microsoft Access, as far as I know, the "wildcard" character in SQL is the percent sign -- '%' -- and not the asterisk -- '*'. Perhaps replace '*' with '%' in your SQL query string?

Try this:

Good Luck,
Avi.
+Pie Number of slices to send: Send
Well I ran the query directly in Access with the * and it worked just fine. However, I'm in no position to be picky so I'll give it a shot!!
+Pie Number of slices to send: Send
It didn't work, I got a missing operator exception. For those just tuning in , the purpose of this servlet and model is to search for a string that the user enters in. I can't get my query to work, any suggestions on what I can do to fix or another method around this one?
+Pie Number of slices to send: Send
Sorry, one more thing; this is the exact query I created in MS Access which gets the results I'm looking for. I tried this in my model as well but it didn't work either.

SELECT cd.* FROM cd WHERE ((([cd].[artist]) Like \"*is*\") And (([cd].[active])=True))
+Pie Number of slices to send: Send
 

Originally posted by Jason Kwok:
this is the exact query I created in MS Access which gets the results

SELECT cd.* FROM cd WHERE ((([cd].[artist]) Like \"*is*\") And (([cd].[active])=True))



Did you look at Avi's post? do you find any difference in your query and what he suggested.


Shailesh
[ April 26, 2005: Message edited by: Shailesh Chandra ]
+Pie Number of slices to send: Send
Using the % sign instead of the * gives me the following SQLException:

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source) at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source) at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(Unknown Source) at infostore.models.searchModel.findArtists(searchModel.java:14) at infostore.controllers.SearchController.doPost(SearchController.java:36) at javax.servlet.http.HttpServlet.service(HttpServlet.java:760) at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:243) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:201) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566) at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943) at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2344) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566) at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:170) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:170) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:462) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:163) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943) at org.apache.catalina.connector.http.HttpProcessor.process(HttpProcessor.java:1011) at org.apache.catalina.connector.http.HttpProcessor.run(HttpProcessor.java:1106) at java.lang.Thread.run(Unknown Source)
+Pie Number of slices to send: Send
Put the code here

Shailesh
+Pie Number of slices to send: Send
Hey Shailesh, I'm not too sure what code you're referring to so I'll post it all.

This is the doPost method in my SearchController


and this is my search model:
+Pie Number of slices to send: Send
 


String query = "SELECT * FROM cd WHERE (((artist) Like "*"+criteria+"*\"))";



String query = "SELECT * FROM cd WHERE (((artist) Like '%"+criteria+"%'))";


Shailesh
+Pie Number of slices to send: Send
Hey Shailesh,

Yet again I'm in your debt!! It seems to work just fine now!! I really appreciate your help once again!! How Microsoft Access allowed % as a wildcard when the * and ? are it's only wildcards... I'll never know!!

Jason
+Pie Number of slices to send: Send
 

Originally posted by Jason Kwok:
How Microsoft Access allowed % as a wildcard when the * and ? are it's only wildcards...



* and ? are the wild cards for operating system not sql.

had you paid attention to Avi's post he has mentioned same and following his advice you could have solve this problem even before.

any way good to know its working

Shailesh
+Pie Number of slices to send: Send
Shailesh,
Thanks for the tip, I did try Avi's advice. If you read my posts directly after Avi's you'll see I tried and responded to him. I used the % with double quotes at the time which perhaps made things over complicated in translation and resulted in my errors.

Jason
+Pie Number of slices to send: Send
ok got it

actuually I was referring to query hint given by him which has no double quote


Shailesh
Can you really tell me that we aren't dealing with suspicious baked goods? And then there is this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com


reply
reply
This thread has been viewed 1713 times.
Similar Threads
Apache Tomcat/4.0.1 - HTTP Status 500 - Internal Server Error at time of jsp executio
server not found
Using CachedRowSet
Model to add to a database
HTTP Status 500
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 29, 2024 01:50:56.