CODEWITHSUNDEEP
c#sqlms-accessoledb
HuwF
HuwF

Reputation: 1

Select Query returns empty record

Hey all so basically I have a C# application that allows a user to enter their query about cars in stock (Fields are Car Manufacturer, Model, Age, Litre Size).

The query's contents can vary on what fields the user is looking for (e.g. a person may look for all Ford cars or another query may be all Ford cars that are 6 years old).

After the query is entered the program SHOULD return the data requested to a dataGridView on the form. My problem is that the query executes but only returns a blank record as though it cannot find a suitable record (I have only entered query's that would definitely return a record) this has lead me to believe something is wrong with my coding (particularly the parameters) but I can't figure out where I'm going wrong, could anyone lend a hand?

            string ConnStr = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = H:\\School Work\\Computing A Level\\Stock checker\\Program\\Morgan's Motors Database.mdb;";

            OleDbConnection conn_database = new OleDbConnection();
            conn_database.ConnectionString = ConnStr;

            OleDbCommand comm_database = new OleDbCommand();
            comm_database.CommandText = "SELECT * FROM [Car Info] WHERE ? = ?";
            comm_database.Connection = conn_database;
            conn_database.Open();

            OleDbDataAdapter adap_database = new OleDbDataAdapter(comm_database);

            DataTable data_database = new DataTable();

            for (int i = 0; i < ColumnName.Count; i++)
            {
                comm_database.Parameters.AddWithValue("?", ColumnName[i].ToString());
                comm_database.Parameters.AddWithValue("?", EnteredFields[i].ToString());

                adap_database.Fill(data_database);

            }

            BindingSource bind_database = new BindingSource();
            bind_database.DataSource = data_database;

            dataGridView1.DataSource = bind_database;

Upvotes: 0

Views: 1747

Answers (3)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

Column names can't be bound parameters. The...

comm_database.Parameters.AddWithValue("?", ColumnName[i].ToString());

...is interpreted as a value, not as a column name.

  • If that value is different from the value of the second parameter, no rows will ever be returned (since WHERE ? = ? evaluates to false, no matter what is actually in the database).
  • If they're equal, all rows will be returned (again, irrespective of what is actually in the database).

This is one of the situations where dynamic SQL is appropriate: construct the SQL text at run-time, once all search criteria is known (you should still use parameters for search values), something like this:

"SELECT * FROM [Car Info] WHERE COLUMN1 = ? AND COLUMN2 = ?" (etc...)

Be sure COLUMN1, COLUMN2 etc. are white-listed, or otherwise "sanitized" before being inserted into the SQL text.

There are ways to use static SQL even in the face of such changing search criteria, but this can have unintended consequences performance-wise.

Upvotes: 0

Jean.R
Jean.R

Reputation: 526

Two things are strange to me:

comm_database.CommandText = "SELECT * FROM [Car Info] WHERE ? = ?";
  1. the field name should be real and not "?" (but "?" is correct for the value).
  2. in your query you only specify one couple file=value but below you loop and add parameters to your dbcommand. as they are not related to anything in your sql text, it wont work as expected.

comm_database.CommandText = "SELECT * FROM [Car Info] WHERE ";

for (int i = 0; i < ColumnName.Count; i++)
{

    comm_database.CommandText += (i>0 ? " AND " : "") ColumnName[i].ToString() + " = ?";

    comm_database.Parameters.AddWithValue("?", EnteredFields[i].ToString());

}

Upvotes: 1

MUG4N
MUG4N

Reputation: 19717

I guess your problem is your select statement. You have to name your parameters explicitly in your commandtext. The statement you are using:

"SELECT * FROM [Car Info] WHERE ? = ?"

should therefore changed to something like this (depending on your parameter)

"SELECT * FROM [Car Info] WHERE BRAND = ? AND ..."

see also: OleDbCommand parameters order and priority

Upvotes: 0

Related Questions