syntax error (missing operator) in query expression status = 'Available'

Question

I am working a C# Windows Application Form connecting to MS Access. I am filling the datagridview with a specific query but this happened

Syntax error (missing operator) in query expression 'Model WHERE Status = 'AVAILABLE''.

on this query

OleDbDataAdapter daAvailable = new OleDbDataAdapter("SELECT Type, Brand, Model, SerialNo, Status, Remarks, RAM, HDD, ODD, VideoCard, PS FROM Available ORDER BY Type, Brand, Model WHERE Status = 'AVAILABLE'", cnn);

What should I do?

Here's my code

OleDbConnection cnn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\james\Documents\Visual Studio 2010\Projects\Vault\Vault\VaultDatabase.accdb");
DataSet dstAvailable = new DataSet();
DataSet dstData = new DataSet();
DataSet dstDeployment = new DataSet();
DataSet dstLog = new DataSet();


public void FillAvailable(DataGridView dgv)
{
    OleDbDataAdapter daAvailable = new OleDbDataAdapter("SELECT Type, Brand, Model, SerialNo, Status, Remarks, RAM, HDD, ODD, VideoCard, PS FROM Available ORDER BY Type, Brand, Model WHERE Status = 'AVAILABLE'", cnn);
    daAvailable.Fill(dstAvailable);
    dgv.DataSource = dstAvailable.Tables[0];
}

AVAILABLE is a string

James Kevin De Jesus
– James Kevin De Jesus

2013-05-16 09:19:48 +00:00
Commented May 16, 2013 at 9:19 — James Kevin De Jesus
– James Kevin De Jesus, Commented May 16, 2013 at 9:19

Rajeev Kumar · Accepted Answer · 2013-05-16 09:19:03Z

1

Write like this

public void FillAvailable(DataGridView dgv)
{
    OleDbDataAdapter daAvailable = new OleDbDataAdapter("SELECT Type, Brand, Model, SerialNo, Status, Remarks, RAM, HDD, ODD, VideoCard, PS FROM Available  WHERE Status = 'AVAILABLE' ORDER BY Type, Brand, Model", cnn);
    daAvailable.Fill(dstAvailable);
    dgv.DataSource = dstAvailable.Tables[0];
}

*Order By Comes After Where *

answered May 16, 2013 at 9:19

Rajeev Kumar

4,9739 gold badges53 silver badges91 bronze badges

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

Comments

Carlos Landeras · Accepted Answer · 2013-05-16 09:19:17Z

1

ORDER BY

is always after the WHERE clause, it should be:

OleDbDataAdapter daAvailable = new OleDbDataAdapter("SELECT Type, Brand, Model, SerialNo, Status, Remarks, RAM, HDD, ODD, VideoCard, PS FROM Available WHERE Status = 'AVAILABLE'  ORDER BY Type, Brand, Model", cnn);

answered May 16, 2013 at 9:19

Carlos Landeras

11.1k11 gold badges61 silver badges84 bronze badges

Comments

Habib · Accepted Answer · 2013-05-16 09:29:13Z

1

ORDER BY clause should be after the WHERE clause in SQL

So your query should be:

OleDbDataAdapter daAvailable = new OleDbDataAdapter("SELECT Type, Brand, Model, SerialNo, "+
     "Status, Remarks, RAM, HDD, ODD, VideoCard, PS " + 
     "FROM Available " +
     "WHERE Status = 'AVAILABLE' "+
     "ORDER BY Type, Brand, Model", cnn);

     //The above query is broken down on multiple lines for clarity

edited May 16, 2013 at 9:29

answered May 16, 2013 at 9:18

Habib

224k30 gold badges420 silver badges446 bronze badges

Collectives™ on Stack Overflow

syntax error (missing operator) in query expression status = 'Available'

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related