Sql Query Help, syntax error near =

Question

why is this not working ? It's something about the commandstring syntax near = but I can't seem to figure it out, the online examples seem exactly the same. Edit: Activated In is a column.

examples from How to select data from database with many filter options?

private void btnDist_Click(object sender, EventArgs e)
{
    string cmdText = "SELECT * FROM Inventory WHERE Rep <> '#NA' AND Activated In = '#NA'";
    SqlDataAdapter adapter = new SqlDataAdapter(cmdText, GetConnection());
    DataSet distDS = new DataSet();
    adapter.Fill(distDS);

    dataGridView1.DataSource = distDS.Tables[0].DefaultView;
}

Justin · Accepted Answer · 2011-07-11 10:18:56Z

6

If the name of the column on your database is Activated In (with a space) then you need to use square brackets when referring to the object / column name in queries, for example:

SELECT * FROM Inventory WHERE Rep <> '#NA' AND [Activated In] = '#NA'

This is because SQL treats spaces as a separator in the query language - square brackets are used to "escape" this (and other characters) in names.

Alternatively if the column is just called Activated then you don't need the IN bit - either test for equality or test to see if the value is in a given range, but don't do both.

-- Use this
SELECT * FROM Inventory WHERE Rep <> '#NA' AND Activated = '#NA'
-- Or this
SELECT * FROM Inventory WHERE Rep <> '#NA' AND Activated IN ('#NA', 'Some other value')

answered Jul 11, 2011 at 10:18

Justin

87.3k49 gold badges231 silver badges374 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

HLGEM Over a year ago

Which is why you should avoid using spaces or keywords in the table design inteh first place. If this is a new table not yet pushed to prod, I would immediately fix the fieldname.

Dani Over a year ago

@Kragen, thanks a lot for the answer man, Activated In is(was) a column name so the brackets fixed the problem. Wouldn't even know what the actual IN keyword is for

Pranay Rana · Accepted Answer · 2011-07-11 10:17:35Z

4

change you query to There is no need of IN keyword

SELECT * FROM Inventory WHERE Rep <> '#NA' AND Activated = '#NA'"

IN - used when you want to filter out data from the list or subquery is there.

answered Jul 11, 2011 at 10:17

Pranay Rana

177k37 gold badges244 silver badges267 bronze badges

Comments

C.Evenhuis · Accepted Answer · 2011-07-11 10:18:24Z

2

The In keyword is used as follows:

...AND Activated IN ('#NA')
...AND Activated IN ('#NA', 'other filter value', 'more filter values')

answered Jul 11, 2011 at 10:18

C.Evenhuis

26.4k2 gold badges60 silver badges73 bronze badges

Comments

Jethro · Accepted Answer · 2011-07-11 10:18:14Z

1

Activated In = '#NA'"

This needs to be Activated In ('#NA')" or Activated = '#NA'".
The In operator in SQL is reserved.

answered Jul 11, 2011 at 10:18

Jethro

5,9413 gold badges25 silver badges24 bronze badges

Comments

Alex R. · Accepted Answer · 2011-07-11 10:19:02Z

1

Remove the = after In

string cmdText = "SELECT * FROM Inventory WHERE Rep <> '#NA' AND Activated In ('#NA')";

You might also want to use = instead of In since you are only specifying one item.

string cmdText = "SELECT * FROM Inventory WHERE Rep <> '#NA' AND Activated = '#NA'";

If Activated In is a column, then use:

string cmdText = "SELECT * FROM Inventory WHERE Rep <> '#NA' AND [Activated In] = '#NA'";

answered Jul 11, 2011 at 10:19

Alex R.

4,7484 gold badges32 silver badges40 bronze badges

Comments

Illuminati · Accepted Answer · 2011-07-11 10:19:37Z

1

Activated In is a column ? then it should have been ActivatedIn or [Activated In]

answered Jul 11, 2011 at 10:19

Illuminati

4,6592 gold badges38 silver badges56 bronze badges

Collectives™ on Stack Overflow

Sql Query Help, syntax error near =

6 Answers 6

2 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related