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

Servlet SQL query

Hi
I am trying to display in HTML page data coming from two different tables depending in the user selection.
I want to display the category#, description, brand name and the price of a category item if user has selected example `Hard drive`. The only thing is that the brand name is in the Brand table and all the others information from Item table.  I want to display this in an HTML table called from a Servlet program
Can you give the code?  because what I have programed, does not show me anything in the HTML table but does not give me any error message either, so I don't know where my problem is
Thanks
0
Mimi1979
Asked:
Mimi1979
  • 18
  • 10
  • +1
1 Solution
 
JimBrandleyCommented:
Can you post the select statement you have tried, and define the column (or columns) that relates the Item table to the brand table?

Jim
0
 
SQL_SERVER_DBACommented:
lets see your html
0
 
KuldeepchaturvediCommented:
how that HTML page is being built? a static page will not work for you.. the page either needs to be build up using a jsp / from the servlet..
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
Mimi1979Author Commented:
This is the index page:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=windows-1252"></meta>
    <title>TecnoPlus - Inventaire</title>
    <link rel="stylesheet" type="text/css" href="style1.css"></link>
  </head>
  <body><table cellspacing="0" border="0" width="100%">
      <tr>
        <th>
          <p>Bienvenue dans TechnoPlus</p>
          <h1>Dans ce site, vous trouverez tous les produits disponible selon ce
              que vous cherchez. Notre inventaire est &agrave; votre
              disposition, vous n'avez qu'&agrave; cliquer sur le lien</h1>
        </th>
      </tr>
    </table><p>
      &nbsp;
    </p><form name="form1" action="servlet_affichercategories"
                         method="POST">
      <input type="submit" name="afficherCat"
             value="Afficher toutes categories"/>
    </form></body>
</html>

Servlet_AfficherCategories ---:

package servlets;

import java.io.IOException;
import java.io.PrintWriter;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.*;
import javax.servlet.http.*;

public class Servlet_AfficherCategories extends HttpServlet {
        private static final String CONTENT_TYPE =
            "text/html; charset=windows-1252";

        private Connection connexion = null;
        private String URL = "jdbc:odbc:inventaire";

        public void init(ServletConfig config) throws ServletException {
            super.init(config);

            try {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                connexion = DriverManager.getConnection(URL, "", "mp");
            } catch (Exception e) {
                e.printStackTrace();
                connexion = null;
            }
        }

        public void doPost(HttpServletRequest request,
                           HttpServletResponse response) throws ServletException,
                                                                IOException
                                                                 {

            PrintWriter sortie = response.getWriter();
            response.setContentType("text/html");
            Statement requete = null;
            ResultSet resultat = null;
            try {
                requete = connexion.createStatement();
                resultat =
                        requete.executeQuery("Select no_categorie, nom_categorie from Categories");
            } catch (Exception e) {
                System.err.println("ERREUR: problème à la lecture de la selection");
                e.printStackTrace();

            }

            if (resultat != null) {
                // envoie la page HTML au client avec la table categories
                sortie.println("<HTML><HEAD><TITLE>");
                sortie.println("Toutes les categories de la BD Inventaire");
                sortie.println("</TITLE>");
                sortie.println("<link rel=stylesheet type=\"text/css\" href=\"style1.css\"></HEAD><BODY>");
                sortie.println("<FORM name=\"form1\" action=\"servlet_afficherpieces\" method=\"post\">");
           
               try{ while (resultat.next()){
                String numCat = resultat.getString(1);
                String nomCat = resultat.getString(2);
                sortie.println("<H2>");
                sortie.println("<input type=\"radio\" name=\"cate\" value=\"" + numCat + "\">" + nomCat +"<BR>");}}
                               catch (SQLException ex){ ex.printStackTrace();}
                sortie.println(" <input type=\"submit\" name=\"soumission\" value=\"Soumettre\">" + "<BR>");
                sortie.println("</H2>");
                sortie.println("</form>");
            } else
                sortie.print("<H2>Une erreur s'est produite. " +
                             "Veuillez réessayer plus tard.</H2>");

            sortie.println("</BODY></HTML>");
            sortie.close(); // clôturer le flux.
        }

        public void destroy() {
            try {
                connexion.close();
            } catch (Exception e) {
                System.err.println("Problème à la fermeture de" +
                                   " la base de données");
            }
        }
        }


Servlet_AfficherPieces:

package servlets;

import java.io.IOException;
import java.io.PrintWriter;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.*;
import javax.servlet.http.*;

public class Servlet_AfficherPieces extends HttpServlet {
    private static final String CONTENT_TYPE = "text/html; charset=windows-1252";

    private Connection connexion = null;
    private String URL = "jdbc:odbc:inventaire";

    public void init(ServletConfig config) throws ServletException {
        super.init(config);

        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            connexion = DriverManager.getConnection(URL, "", "mp");
        } catch (Exception e) {
            e.printStackTrace();
            connexion = null;
        }
    }

    public void doPost(HttpServletRequest request,
                     HttpServletResponse response) throws ServletException, IOException {
        PrintWriter sortie;
        String numCatChoisie = request.getParameter("cate");
   
        response.setContentType( "text/html" );
        sortie = response.getWriter();
   
        // envoie la page HTML au client avec la categorie chosie
        sortie.println( "<HTML><HEAD><TITLE>" );
        sortie.println("Table de la categorie choisie");
        sortie.println("</TITLE>");
        sortie.println("<link rel=stylesheet type=\"text/css\" href=\"style1.css\"></HEAD><BODY>");
        sortie.println("<H1>Categorie choisie : " + numCatChoisie + "</H1>");
        sortie.println("<table border='1'><tr>");
       
        //Creation de l<objet pour la requete
        Statement requete = null;
        ResultSet resultat = null;
        try {
            requete = connexion.createStatement();
            resultat = requete.executeQuery("SELECT description FROM Pieces WHERE no_categorie='numCatChoisie'");
            while ( resultat.next() ){
                sortie.println("<td>" + resultat.getString(1) + "</td>");
                sortie.println("</tr>");
                }
            resultat.close();
            }
        catch (SQLException e) {
                    System.err.println("ERREUR: problème à la lecture de la selection");
                    e.printStackTrace();}

        sortie.println("</table></BODY></HTML>");
        sortie.close(); // clôturer le flux.
    }

    public void destroy() {
        try {
            connexion.close();
        } catch (Exception e) {
            System.err.println("Problème à la fermeture de" +
                               " la base de données");
        }
    }
    }

In Servlet_AfficherPieces, I want to display what the user selection depending what categories the user is selecting
I want to display the category#, description, brand name and the price of a categoryof the item the user has selected, example; `Hard drive`.
Thank you in advance
0
 
Mimi1979Author Commented:
Does anyone has an idea??? or any suggestion?
Thank you
0
 
KuldeepchaturvediCommented:
>> this line in your last servlet

 resultat = requete.executeQuery("SELECT description FROM Pieces WHERE no_categorie='numCatChoisie'");

needs to be

 resultat = requete.executeQuery("SELECT description FROM Pieces WHERE no_categorie='"+numCatChoisie+"'");

remember you want to pass the value of the selected parameter not the name of the variable..!!!
0
 
Mimi1979Author Commented:
I tried resultat = requete.executeQuery("SELECT description FROM Pieces WHERE no_categorie='"numCatChoisie"'"); and did not give any rows, but I didn`t try with + sign
I will try that
Thank you
0
 
Mimi1979Author Commented:
I have now another error message ---  when I run Servlet_AfficherCategories
Resource /HTMLDyn_servlets-labo2-context-root/Servlet_AfficherCategories not found on this server
I am not sure what this means.
0
 
Mimi1979Author Commented:
When I click on Àfficher toutes categories`from index, which calls Servlet_AfficherCategories, it gives 'Resource /HTMLDyn_servlets-labo2-context-root/Servlet_AfficherCategories not found on this server'

But when I run directly Servlet_AfficherCategories it gives me : '405 Method Not Allowed
HTTP method GET is not supported by this URL'  but I changed it to a doPost and still coming up
Do you have any ideas?
Thank you
0
 
KuldeepchaturvediCommented:
public void doG(HtettpServletRequest request,
                     HttpServletResponse response) throws ServletException, IOException
{
doPost(request,response);
}

adding this in servlet will take care of your 405 issue..

& error Resource /HTMLDyn_servlets-labo2-context-root/Servlet_AfficherCategories not found means your servlet mappings are not correct..

either fix your servlet mappings or fix

sortie.println("<FORM name=\"form1\" action=\"servlet_afficherpieces\" method=\"post\">");

to
sortie.println("<FORM name=\"form1\" action=\"/servlet/servlet_afficherpieces\" method=\"post\">");

in your first servlet


0
 
Mimi1979Author Commented:
So I changed the sortie.println("<FORM name=\"form1\" action=\"servlet_afficherpieces\" method=\"post\">");

to
sortie.println("<FORM name=\"form1\" action=\"/servlet/servlet_afficherpieces\" method=\"post\">");
but I am getting
 '404 Not Found
Resource /HTMLDyn_servlets-labo2-context-root/afficherCategories not found on this server'.
I tried to change it in the web.xml file as well but I am not sure that the url-patern is exactly
0
 
Mimi1979Author Commented:
This is my web.xml ---

<?xml version = '1.0' encoding = 'windows-1252'?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd" version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee">
    <description>Empty web.xml file for Web Application</description>
    <servlet>
        <servlet-name>afficherCategories</servlet-name>
        <servlet-class>labo2.afficherCategories</servlet-class>
    </servlet>
    <servlet>
        <servlet-name>afficherPiece</servlet-name>
        <servlet-class>labo2.afficherPiece</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>afficherCategories</servlet-name>
        <url-pattern>/affichercategories</url-pattern>
    </servlet-mapping>
    <servlet-mapping>
        <servlet-name>afficherPiece</servlet-name>
        <url-pattern>/afficherpiece</url-pattern>
    </servlet-mapping>
    <session-config>
        <session-timeout>35</session-timeout>
    </session-config>
    <mime-mapping>
        <extension>html</extension>
        <mime-type>text/html</mime-type>
    </mime-mapping>
    <mime-mapping>
        <extension>txt</extension>
        <mime-type>text/plain</mime-type>
    </mime-mapping>
</web-app>
0
 
Mimi1979Author Commented:
I changed the package name to labo2 now on each servlet.
any ideas?
thanks
Mimi
0
 
KuldeepchaturvediCommented:
URL pattern is what system uses to find a particular servlet. according to your web.xml

<servlet-mapping>
        <servlet-name>afficherPiece</servlet-name>
        <url-pattern>/afficherpiece</url-pattern>
    </servlet-mapping>


afficherpiece will get invoked when your URL is looking something like.

http://localhost:8080/youapp/affieherpiece..

which means your HTML form should be submitting to


sortie.println("<FORM name=\"form1\" action=\"/afficherpieces\" method=\"post\">");

Also try accessing your servlet directly from browser to see that you can access it.

0
 
Mimi1979Author Commented:
When I access it from the Web I get same error message
HTTP Status 404 - /HTMLDyn_servlets-labo2-context-root/afficherCategories

type Status report

message /HTMLDyn_servlets-labo2-context-root/afficherCategories

description The requested resource (/HTMLDyn_servlets-labo2-context-root/afficherCategories) is not available.
Apache Tomcat/6.0.13
I don't see where my error is. The web.xml and the html form seem correct.
0
 
KuldeepchaturvediCommented:
your servlet name is

public class Servlet_AfficherCategories extends HttpServlet {

but the name in your web.xml is different..
<servlet>
        <servlet-name>afficherCategories</servlet-name>
        <servlet-class>labo2.afficherCategories</servlet-class>
    </servlet>

make sure that the class name matches... also Java is case sensitive..
so
servlet_afficherpieces and Servlet_afficherpieces are two different names.. make sure your names are matching
0
 
Mimi1979Author Commented:
I changed the name of the class to afficherCategories

 when I run index.html I get he page with the form, as soon I click on the submit button then I get
404 Not Found
Resource /HTMLDyn_servlets-labo2-context-root/afficherCategories not found on this server

But when I run directly afficherCategories I get
 'Une erreur s'est produite. Veuillez réessayer plus tard.'
wich is normal since I call directly the servlet and resultat is null. So I think the problem might be something else in the web.xml even all seems fine, it`s not recognizing the servlet. I dont know where else to look....
0
 
KuldeepchaturvediCommented:
where is your index page sitting?
everything else is looking okay to me..

can you post your directory structure and also the URL that you are using to access your servlet directly, as well as URL to access your index page...

I think somewhere your relative path is getting messed up.
0
 
Mimi1979Author Commented:
ok, I found what the problem is. I am using JDeveloper and the application name has to be the same as the project name and Jdev will take automatically that name for  the package.
I renamed my application  labo2_servlets, now I am able to run the servlet afficherCategories but I get:
'Une erreur s'est produite. Veuillez réessayer plus tard.' wich is the statement if resultat=null


***************************************

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
  <head>
     <meta http-equiv="Content-Type" content="text/html; charset=windows-1252"></meta>
    <title> TecnoPlus - Inventaire</title>
    <link rel=stylesheet type="text/css" href="style1.css">
  </head>
  <body ><table cellspacing="0" border="0" width="100%">
      <tr>
        <th>
          <p> Bienvenue dans TechnoPlus </p>
          <h1>
            Dans ce site, vous trouverez tous les produits disponible selon ce
            que vous cherchez.
            Notre inventaire est &agrave; votre disposition, vous n'avez
            qu'&agrave; cliquer sur le lien
          </h1>
        </th>
      </tr>
    </table><p>
      <form name="form1" action="affichercategories" method="POST">
        <input type="submit" name="afficherCat"
               value="Afficher toutes categories">
      </form>
    </p></body>
</html>

***************************************

package labo2_servlets;

import java.io.IOException;
import java.io.PrintWriter;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.*;
import javax.servlet.http.*;

public class afficherCategories extends HttpServlet {
    private static final String CONTENT_TYPE =
        "text/html; charset=windows-1252";

    private Connection connexion = null;
    private String URL = "jdbc:odbc:inventaire";

    public void init(ServletConfig config) throws ServletException {
        super.init(config);

        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            connexion = DriverManager.getConnection(URL, "", "mp");
        } catch (Exception e) {
            e.printStackTrace();
            connexion = null;
        }
    }

    public void doGet(HttpServletRequest request,
                      HttpServletResponse response) throws ServletException,
                                                           IOException {
        doPost(request, response);
    }

    public void doPost(HttpServletRequest request,
                       HttpServletResponse response) throws ServletException,
                                                            IOException {

        PrintWriter sortie = response.getWriter();
        response.setContentType("text/html");
        Statement requete = null;
        ResultSet resultat = null;
        try {
            requete = connexion.createStatement();
            resultat = requete.executeQuery("select no_categorie, nom_categorie from Categories");
        } catch (Exception e) {
            System.err.println("ERREUR: problème à la lecture de la selection");
            e.printStackTrace();

        }

        sortie.println("<HTML><HEAD><TITLE>");
        sortie.println("Toutes les categories de la BD Inventaire");
        sortie.println("</TITLE>");
        sortie.println("<link rel=stylesheet type=\"text/css\" href=\"style1.css\"></HEAD><BODY>");
        if (resultat != null) {
            // envoie la page HTML au client avec la table categories
            //sortie.println("<FORM name=\"form1\" action=\"afficherpiece\" method=\"POST\">");
            // sortie.println("<FORM name=\"form1\" action=\"*/afficherPieces\" method=\"post\">");

            try {
                while (resultat.next()) {
                    String numCat = resultat.getString(1);
                    String nomCat = resultat.getString(2);
                    sortie.println("<H2>");
                    sortie.println("<input type=\"radio\" name=\"cate\" value=\"" +
                                   numCat + "\">" + nomCat + "<BR>");
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            //sortie.println(" <input type=\"submit\" name=\"soumission\" value=\"Soumettre\">" +
                        //   "<BR>");
            sortie.println("</H2>");
            sortie.println("</form>");
        } else
            sortie.print("<H2>Une erreur s'est produite. " +
                         "Veuillez réessayer plus tard.</H2>");

        sortie.println("</BODY></HTML>");
        sortie.close(); // clôturer le flux.
    }

    public void destroy() {
        try {
            connexion.close();
        } catch (Exception e) {
            System.err.println("Problème à la fermeture de" +
                               " la base de données");
        }
    }
}
*********************************************

any ideas?
0
 
Mimi1979Author Commented:
My DB was not loaded in ODBC, now I am getting all the 'categories' from Categories table in the HTML page when the first servlet is ran, now the second servlet I am still getting
'404 Not Found
Resource /labo2_servlets-labo2_servlets-context-root/afficherPieces not found on this server'

Here`s my structure
I have created my application as follow
labo2_servlets is the folder application
in ../labo2_servlets/public_html my index is sitting
in labo2_servlets/public_html/WEB-INF my web.xml is located
in labo2_servlets/src/labo2_servlets my servlets files are sitting (afficherCategories.java and afficherPieces.java)

and this is where JDev creates the classes files once the servlets have been ran (afficherCategories.class and afficherPieces.class)
.../labo2_servlets/classes/labo2_servlets
I am not sure how to run the servlet directly by the URL if I have Apache TomCat server  ;-(
 Jdev opens the browser directly by running the servlet
0
 
KuldeepchaturvediCommented:
on the browser when your first servlet is running ( showing all the category).. do a right click & look at the properties...

it should have the url that browser is using to access your servlet...
similarly on the other servlet where you are getting the 404 error, do the same and see the URL on that page as well...

they shd be looking similar. ( other than the servlet name)....
0
 
Mimi1979Author Commented:
ok now the two servlet are working, but not my query
I am not sure if the query is correct if I want to display items from two differents tables
This is the servlet executing the query depending what the user has selected
package labo2_servlets;

import java.io.IOException;
import java.io.PrintWriter;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.*;
import javax.servlet.http.*;

public class afficherPieces extends HttpServlet {
    private static final String CONTENT_TYPE = "text/html; charset=windows-1252";

    private Connection connexion = null;
    private String URL = "jdbc:odbc:inventaire";

    public void init(ServletConfig config) throws ServletException {
        super.init(config);

        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            connexion = DriverManager.getConnection(URL, "", "mp");
        } catch (Exception e) {
            e.printStackTrace();
            connexion = null;
        }
    }
   
    public void doGet(HttpServletRequest request,
                         HttpServletResponse response) throws ServletException, IOException
    {
        doPost(request,response);
    }

    public void doPost(HttpServletRequest request,
                     HttpServletResponse response) throws ServletException, IOException {
        PrintWriter sortie;
        String numCatChoisie = request.getParameter("cate");
   
        response.setContentType( "text/html" );
        sortie = response.getWriter();
   
        // envoie la page HTML au client avec la categorie chosie
        sortie.println( "<HTML><HEAD><TITLE>" );
        sortie.println("Table de la categorie choisie");
        sortie.println("</TITLE>");
        sortie.println("<link rel=stylesheet type=\"text/css\" href=\"style1.css\"></HEAD><BODY>");
        sortie.println("<H1>Categorie choisie : " + numCatChoisie + "</H1>");
       
        //Creation de l<objet pour la requete
        Statement requete = null;
        ResultSet resultat = null;
        try {
            sortie.println("Dans le try");
            requete = connexion.createStatement();
            resultat =
                    requete.executeQuery("SELECT Pieces.no_categorie, Pieces.description, " +
                    "Fabricant.nom_fabricant, Pieces.prix_vente WHERE Pieces.no_fabricant=Fabricant.no_fabricant" +
                    " AND Pieces.no_categorie='" + numCatChoisie + "'");
        } catch (Exception e) {
            System.err.println("ERREUR: problème à la lecture de la selection");
            e.printStackTrace();

        }

        if (resultat != null) {
            sortie.println("Dans resultat");
            sortie.println("<H2>");
            sortie.println("<TABLE><TR><TH>" + "Numero de categorie" + "</TH>");
            sortie.println("<TD>" + "Description" + "</TD>");
            sortie.println("<TD>" + "Nom fabricant" + "</TD>");
            sortie.println("<TD>" + "Prix vente" + "</TD></TR>");
           try{
               while (resultat.next()){
                String numCat = resultat.getString(1);
                String des = resultat.getString(2);
                String nomFab = resultat.getString(3);
                String prix = resultat.getString(4);
                sortie.println("<TR><TH>" + numCat +"</TH>");
                sortie.println("<TD>" + des + "</TD>");
                sortie.println("<TD>" + nomFab + "</TD>");
                sortie.println("<TD>" + prix + "</TD></TR>");
                }
            }
                catch (SQLException ex){ ex.printStackTrace();}
            sortie.println("</TABLE></H2>");
        } else
            sortie.print("<H2>Une erreur s'est produite. " +
                         "Veuillez réessayer plus tard.</H2>");

        sortie.println("</BODY></HTML>");
        sortie.close(); // clôturer le flux.
    }

    public void destroy() {
        try {
            connexion.close();
        } catch (Exception e) {
            System.err.println("Problème à la fermeture de" +
                               " la base de données");
        }
    }
}

Is my query correct?
0
 
Mimi1979Author Commented:
the primary key in table Pieces is 'no_piece' and the primary key in table Fabricant is 'no_fabricant'
What I want to display is the no_categorie, nom_fabricant, prix from table Pieces and the description from table Fabricant depending what the selection is for no_categorie
Any ideas?
0
 
Mimi1979Author Commented:
I have changed the query to this
                           requete.executeQuery("SELECT Pieces.no_categorie, Pieces.description," +
                                         " Fabricant.nom_fabricant, Pieces.prix_vente FROM Pieces, Fabricant" +
                                        " WHERE Pieces.no_fabricant=Fabricant.no_fabricant" +
                                         " AND Fabricant.nom_fabricant=" +
                                         "(SELECT nom FROM Fabricant" +
                                         " WHERE no_fabricant=(SELECT no_fabricant FROM Pieces WHERE no_categorie='" +
                                         numCatChoisie + "'" + "))" +
                                         " AND Pieces.no_categorie='" + numCatChoisie + "'");
but still getting resultset = null, help me here please...
Thanks
0
 
KuldeepchaturvediCommented:
do one thing..

String query = "SELECT Pieces.no_categorie, Pieces.description," +
                                         " Fabricant.nom_fabricant, Pieces.prix_vente FROM Pieces, Fabricant" +
                                        " WHERE Pieces.no_fabricant=Fabricant.no_fabricant" +
                                         " AND Fabricant.nom_fabricant=" +
                                         "(SELECT nom FROM Fabricant" +
                                         " WHERE no_fabricant=(SELECT no_fabricant FROM Pieces WHERE no_categorie='" +
                                         numCatChoisie + "'" + "))" +
                                         " AND Pieces.no_categorie='" + numCatChoisie + "'";

System.out.println(query);

 requete.executeQuery(query);

this should print the query that you are trying to execute.
you can use the query on the database and see what results it provides.
0
 
Mimi1979Author Commented:
I dont have a database. Is it beeter maybe that I create more than one query because the error I am getting is that the subquery returns more than one row...
0
 
KuldeepchaturvediCommented:
then you need to modify the query as

SELECT Pieces.no_categorie, Pieces.description," +
                                         " Fabricant.nom_fabricant, Pieces.prix_vente FROM Pieces, Fabricant" +
                                        " WHERE Pieces.no_fabricant=Fabricant.no_fabricant" +
                                         " AND Fabricant.nom_fabricant=" +
                                         "(SELECT nom FROM Fabricant" +
                                         " WHERE no_fabricant IN (SELECT no_fabricant FROM Pieces WHERE no_categorie='" +
                                         numCatChoisie + "'" + "))" +
                                         " AND Pieces.no_categorie='" + numCatChoisie + "'";
0
 
Mimi1979Author Commented:
We got it, thank you very much

This is what I did and worked for me

        try {
            //Creating the connexion
            stat = connexion.createStatement();
           
            String requete1 = "SELECT Pieces.no_categorie, Pieces.description, Fabricants.nom_fabricant, " +
            "Pieces.prix_vente FROM Pieces, Fabricants " +
            "WHERE Pieces.no_fabricant=Fabricants.no_fabricant " +
            "AND Fabricants.nom_fabricant IN " +
            "(SELECT nom_fabricant FROM Fabricants " +
            "WHERE no_fabricant IN (SELECT no_fabricant FROM Pieces WHERE no_categorie='" + numCatChoisie+ "')) " +
            "AND Pieces.no_categorie='" + numCatChoisie+ "'";
            resultat = stat.executeQuery(requete1);

Thank you
0
 
KuldeepchaturvediCommented:
:-) glad to be of help.
0

Featured Post

Independent Software Vendors: 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!

  • 18
  • 10
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now