Adding a WHERE goes wrong, but why?

Question

1.00/5 (1 vote)

See more:

Adding a WHERE goes wrong, but why

C#

WHERE Omschrijving like @Materiaalstuk AND FK_Materiaalgroep like @Materiaalgroep AND FK_Leverancier like @Leverancier

C#

var query = "SELECT [IDMateriaal], Omschrijving, Inkoop, Opslag, FK_eenheid, Bepaald_Tarief, FK_Materiaalgroep, FK_leverancier,Datum_wijziging, Eenheid, MateriaalGroep WHERE Omschrijving like @Materiaalstuk AND FK_Materiaalgroep like @Materiaalgroep AND FK_Leverancier like @Leverancier, CASE WHEN (Bepaald_Tarief IS NULL) THEN((Inkoop / 100) * Opslag) + Inkoop ELSE Bepaald_tarief END as Verkoop from Materiaal LEFT OUTER JOIN Eenheid ON Materiaal.FK_Eenheid = Eenheid.IDEenheid LEFT OUTER JOIN MateriaalGroep ON Materiaal.FK_Materiaalgroep = MateriaalGroep.IDMateriaalgroep ORDER BY MateriaalGroep DESC, Omschrijving ASC";

using (var adapter = new SqlDataAdapter(query, connection))
using (SqlCommand command = new SqlCommand(query, connection))

{
    command.Parameters.AddWithValue("@Materiaalstuk", "%" + TX_Materiaalstuk_Zoeken.Text + "%");

    XX_MateriaalgroepZoeken.Text = CMB_Materiaalgroep.SelectedValue.ToString();
    command.Parameters.AddWithValue("@Materiaalgroep", XX_MateriaalgroepZoeken.Text);

    XX_LeverancierZOEKEN.Text = CMB_Leverancier.SelectedValue.ToString();
    command.Parameters.AddWithValue("@Leverancier", XX_LeverancierZOEKEN.Text);

What I have tried:

var query = "SELECT [IDMateriaal], Omschrijving, Inkoop, Opslag, FK_eenheid, Bepaald_Tarief, FK_Materiaalgroep, FK_leverancier,Datum_wijziging, Eenheid, MateriaalGroep WHERE Omschrijving like @Materiaalstuk AND FK_Materiaalgroep like @Materiaalgroep AND FK_Leverancier like @Leverancier, CASE WHEN (Bepaald_Tarief IS NULL) THEN((Inkoop / 100) * Opslag) + Inkoop ELSE Bepaald_tarief END as Verkoop from Materiaal LEFT OUTER JOIN Eenheid ON Materiaal.FK_Eenheid = Eenheid.IDEenheid LEFT OUTER JOIN MateriaalGroep ON Materiaal.FK_Materiaalgroep = MateriaalGroep.IDMateriaalgroep ORDER BY MateriaalGroep DESC, Omschrijving ASC";

Posted 12-Apr-16 23:24pm

MaikelO1

Updated 12-Apr-16 23:56pm

v3

Add a Solution

Comments

Beginner Luck 13-Apr-16 5:33am

can you use join table.

Herman<T>.Instance 13-Apr-16 8:38am

is FK_<anyname> the defined Foreign Key or a field name?

2 solutions

Solution 2

if you are using Sql Server then you can not use "where" clause before "from" you can use where clause in sub query if you want.

here you are using where clause before from so you are getting error.

Posted 12-Apr-16 23:56pm

Nigam,Ashish

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2016-04-12T23:42:00

Solution 1

Because the WHERE clause does not go in the SELECT columns list: it's a separate clause which goes at the end of the SELECT:

SQL

SELECT <columns> FROM MyTable m
JOIN MyOtherTable o ON m.ColumnName = o.OtherColumnName
WHERE <WhereCondition>

Posted 12-Apr-16 23:42pm

OriginalGriff