1

I'm using C# to retrive data from MS Access. When I'm trying to set up a query that "collects" numbers, everything workd good, but when i'm dealing with strings everything goes crazy.

Basically, I have a data base that simulates the periodic table. I have columns like state of matter, density, atomic number etc.

Let's say that I want to get all the elements with an atomic number greater than 20. My query will be as follow :

"Select * From Periodic Where (AtomicNumber > 20)"

This gives me exactly what I want. But le'ts say I want all elements with an atomic number greater than 20 and are currently in a gas state. So I tried something like :

"Select * From Periodic Where (AtomicNumber > 20 AND StateOfMatter = Gas)"

Failed. It throws an error stating :

No value given for one or more required parameters.

I'm aware of this question. I've tried everything, including all these queries : 

 "Select * From Periodic Where (AtomicNumber > 20 AND StateOfMatter = `Gas`)"
 "Select * From Periodic Where (AtomicNumber > 20 AND StateOfMatter LIKE `Gas`)"
 "Select * From Periodic Where (AtomicNumber > 20 AND StateOfMatter = `%Gas%`)"

Everything gives me the above error. I'm trying to solve this for two days now, but with no success.

Here is my C# code that creates the connection :

private void ConnectToDataBase(string Query)
    {
        OleDbConnection DBConnection = new OleDbConnection();
        OleDbDataAdapter DataAdapter;
        DataTable LocalDataTable = new DataTable();

        try
        {
            DBConnection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\user\Documents\PeriodicTable.accdb;
                                              Persist Security Info=False";
            DBConnection.Open();
            DataAdapter = new OleDbDataAdapter(Query, DBConnection);
            // The error occurs here.
            DataAdapter.Fill(LocalDataTable);

            if (Elements.Count == 0)
            {
                return;
            }

        }
        catch (OleDbException ex)
        {
            throw new Exception("Error : " + ex.Message);
        }
        finally
        {
            DBConnection.Close();
        }

    }

Where is the problem and how can I solve it? Why matching strings is so much harder than numbers?

Community
  • 1
  • 1
Eminem
  • 870
  • 5
  • 20

1 Answers1

3

If your StateOfMatter is a character typed, you need to use it's value with single quotes like;

StateOfMatter = 'Gas'

` character (grave accent) is not equal to ' character (apostrophe).

And since OleDbCommand doesn't care about parameter names, it thinks your Gas is a parameter and that's why it says;

No value given for one or more required parameters.

Because it knows if this is not a parameter, it should be surrounded by single quotes.

Soner Gönül
  • 97,193
  • 102
  • 206
  • 364