CODEWITHSUNDEEP
javamysqlsqlnetbeans
user3061044
user3061044

Reputation: 1

error in java about SQLsyntax while query works correctly in MySQL

while creating a program for a store that lists his assortment in a txt-file getting his information from a database from MySQL(phpMyAdmin), I always get the same error in netbeans while running the file. Netbeans show this error: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.....' at line 1". my Query is correct, i've checked over a hundred times in MySQL and it gives always the right tables. also in java i've checked my code plenty of times.

i've googled it and find some other people with some similar issues but none of the proposed solutions work for my project. i've entered extra quotation marks (because I forget them). here's my code:

String Lijst = "";

    Connection con = null;
    try
    {
        con = getConnection();
        Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                             ResultSet.CONCUR_READ_ONLY);

        String sql = "SELECT  ArtikelWinkel.Artikelnummer, Artikel.Naam, Artikel.Prijs, Artikel.GekregenPunten, ArtikelMetBonus.MinimumAantalGekocht, ArtikelMetBonus.BonuspuntenVerdiend, PuntenInwisselbaarArtikel.PrijsInPunten, PuntenInwisselbaarArtikel.MinimumGeldbedrag "
                + "FROM ArtikelWinkel LEFT OUTER JOIN Artikel ON Artikel.Artikelnummer=ArtikelWinkel.Artikelnummer  "
                + "LEFT OUTER JOIN ArtikelMetBonus ON ArtikelWinkel.Artikelnummer=ArtikelMetBonus.Artikelnummer "
                + "LEFT OUTER JOIN PuntenInwisselbaarArtikel ON ArtikelWinkel.Artikelnummer=PuntenInwisselbaarArtikel.Artikelnummer"
                + "WHERE ArtikelWinkel.Naam='" + naamWinkel + "'";
        ResultSet srs = stmt.executeQuery(sql); 
        Lijst = "Naam Winkel   -   Artikel  -  Artikelnummer     -   Prijs  -   Gekregen punten per artikel -   "
                + "min. # kopen om bonus te verdienen   -   # bonuspunten verdiend  -   min. bedrag kopen om prijs in punten  \n";

        while(srs.next())
        {
            Lijst +="\t\t"+ naamWinkel +"   -   "+srs.getString("Artikel.Naam")+"   -   "
                    +srs.getString("ArtikelWinkel.Artikelnummer") +"   -   "+srs.getString("Artikel.Prijs") 
                    + "   -   "+srs.getString("Artikel.GekregenPunten") +"  -   " +srs.getString("ArtikelMetBonus.MinimumAantalGekocht") +
                    "   -   "+srs.getString("ArtikelMetBonus.BonuspuntenVerdiend") + "  -   " +srs.getString("PuntenInwisselbaarArtikel.MinimumGeldbedrag")+ "\n";
        }
        con.close();

they were also people that suggested te let out the sql between the brackets in the statement: ResultSet srs = stmt.executeQuery(sql); I found that very unlogical and it gave an error too.

Some people thought I may had a connection problem but i have other methods in my class that work perfectly with my connectionMethod.

Any suggestions? thank you very much

Upvotes: 0

Views: 85

Answers (2)

Pradeep Simha
Pradeep Simha

Reputation: 18123

There is no space between Artikelnummer and your WHERE clause, change your query like this and try again:

+ "LEFT OUTER JOIN PuntenInwisselbaarArtikel ON ArtikelWinkel.Artikelnummer=PuntenInwisselbaarArtikel.Artikelnummer"
+ " WHERE ArtikelWinkel.Naam='" + naamWinkel + "'";

Upvotes: 0

Masudul
Masudul

Reputation: 21961

You didn't put space before WHERE at SQL query. 

String sql = "SELECT..  .."
            ...PuntenInwisselbaarArtikel.Artikelnummer"
 + "WHERE ArtikelWinkel.Naam='" + naamWinkel + "'";   |_ put space here.

Upvotes: 1

Related Questions