SQL Query delivers no results despite working in e.g. Access?

Question

Here's the problem:

I'm working with Visual Studio and made a Winforms application. This is supposed to work with a database. Reading the database using a SQL Query works totally fine without any problems.

cmd.CommandText = 
     "SELECT Buecher.Bu_ISBN AS ISBN, 
             Buecher.Bu_Titel AS Titel,  
             Buecher.Bu_Originaltitel AS Originaltitel, 
             Buecher.Bu_Buchreihe AS Buchreihe, 
             Buecher.Bu_Genre AS Genre, 
             Autor.Au_Vorname AS [Autor-Vorname], 
             Autor.Au_Nachname AS[Autor-Nachname], 
             Verlag.Ve_Name AS Verlag 
      FROM ((((Buecher 
               INNER JOIN [Buch-Autor] 
                 ON Buecher.Bu_ID = [Buch-Autor].Bu_ID) 
               INNER JOIN Autor 
                 ON [Buch-Autor].Au_ID = Autor.Au_ID) 
               INNER JOIN [Buch-Verlag] 
                 ON Buecher.Bu_ID = [Buch-Verlag].Bu_ID) 
               INNER JOIN Verlag 
                 ON [Buch-Verlag].Ve_Name = Verlag.Ve_Name)";
        ausgabe();

(it is in German, ausgabe() is a method that transfers the Query into a DataGridView - works fine)

Later in the program the user shall search with various criteria using text boxes, that's where it doesn't work anymore, the Query simply is not executed.

if (optBuch.Checked == true)
        {
            cmd.CommandText = 
               "SELECT Buecher.Bu_ISBN AS ISBN, 
                       Buecher.Bu_Titel AS Titel, 
                       Buecher.Bu_Originaltitel AS Originaltitel, 
                       Buecher.Bu_Buchreihe AS Buchreihe, 
                       Buecher.Bu_Genre AS Genre, 
                       Autor.Au_Vorname AS [Autor-Vorname], 
                       Autor.Au_Nachname AS [Autor-Nachname], 
                       Verlag.Ve_Name AS Verlag 
                FROM ((((Buecher 
                         INNER JOIN [Buch-Autor] 
                            ON Buecher.Bu_ID = [Buch-Autor].Bu_ID) 
                         INNER JOIN Autor 
                            ON [Buch-Autor].Au_ID = Autor.Au_ID) 
                         INNER JOIN [Buch-Verlag] 
                            ON Buecher.Bu_ID = [Buch-Verlag].Bu_ID) 
                         INNER JOIN Verlag 
                            ON [Buch-Verlag].Ve_Name = Verlag.Ve_Name)  
              WHERE (Buecher.Bu_ISBN = '%" + txtOpt1.Text + "%') 
                AND (Buecher.Bu_Titel = '%" + txtOpt2.Text + "%') 
                AND (Buecher.Bu_Originaltitel = '%" + txtOpt3.Text + "%') 
                AND (Buecher.Bu_Buchreihe = '%" + txtOpt4.Text + "%') 
                AND (Buecher.Bu_Genre = '%" + txtOpt5.Text + "%') 
                AND (Autor.Au_Vorname = '%" + txtOpt6.Text + "%') 
                AND (Autor.Au_Nachname = '%" + txtOpt7.Text + "%') 
                AND (Verlag.Ve_Name = '%" + txtOpt8.Text + "%')";

I tried various forms of the Query but until now no one worked. It works totally fine when I delete the whole "Where" section...

I hope I could express myself in a comprehensible way.

remember that column like '%%' will not return a record if the column value is null...so you only want to query a column if you have data on that column's filter...otherwise youll have a nulls problem — Ctznkane525, Mar 09 '18 at 20:51

score 0 · Answer 1 · answered Mar 09 '18 at 19:44

0

You cannot use the operand " = " if you want to use a wildcard. Replace " = " with "like".

So it would looks something like this:

WHERE(Buecher.Bu_ISBN like '%" + txtOpt1.Text + "%') AND
(Buecher.Bu_Titel like '%" + txtOpt2.Text + "%')

answered Mar 09 '18 at 19:44

The Integrator

2,068
2
11
11

That would have been too easy - still does not work, I also wrote it in capitals as it's usual for SQL, still no results – Titan Mar 09 '18 at 19:48
can you update your question replacing the '=' with 'like'. I want to see what went wrong. also, realize that you are using the "AND", so that means all those conditions needs to be satisfied. even if one of those conditions were not satisfied, no record will be returned. – The Integrator Mar 09 '18 at 19:54

score 0 · Answer 2 · answered Mar 09 '18 at 19:54

0

Beside the use of LIKE as suggested by @The Integrator you probably need OR instead of AND

Otherwise the row has to match all the criteria to be returned.

          WHERE (Buecher.Bu_ISBN = '%" + txtOpt1.Text + "%') 
            OR (Buecher.Bu_Titel = '%" + txtOpt2.Text + "%') 
            OR (Buecher.Bu_Originaltitel = '%" + txtOpt3.Text + "%') 
            OR (Buecher.Bu_Buchreihe = '%" + txtOpt4.Text + "%') 
            OR (Buecher.Bu_Genre = '%" + txtOpt5.Text + "%') 
            OR (Autor.Au_Vorname = '%" + txtOpt6.Text + "%') 
            OR (Autor.Au_Nachname = '%" + txtOpt7.Text + "%') 
            OR (Verlag.Ve_Name = '%" + txtOpt8.Text + "%')";

Also you need use parameter, otherwise you are vulnerable to Sql Injection attacks

https://stackoverflow.com/a/5165985/3470178

answered Mar 09 '18 at 19:54

Juan Carlos Oropeza

47,252
12
78
118

1

OR instead of AND would not make sense in the way the program is supposed to work - if the user want to search for all books with "Potter" in their title that are written by author with "Rowling" as last name than OR wouldn't sense - sorry – Titan Mar 09 '18 at 20:06
My suggestion try to simplify the problem first. Remove all the where condition and only work with ISBN and try to find a book – Juan Carlos Oropeza Mar 09 '18 at 21:01
Of course, I also had this idea with the same disappointing result - it didn't matter which criterion I used there were still no results – Titan Mar 09 '18 at 23:05
can you show us a print screen of the result of your query without WHERE and another with `WHERE ISBN LIKE '%1234%'` where 1234 is part of one isbn you see on first result – Juan Carlos Oropeza Mar 10 '18 at 05:54

SQL Query delivers no results despite working in e.g. Access?

2 Answers2