Select doesn't return result

Question

I’ve got a problem with this SELECT. When I execute it on MS Access, the result is returning right and fully. When I try to execute it via C#, the same query returns nothing. The previous variant of the SELECT was realized by adapter to show the result of select in dataGridView. But when I execute it, I get only an empty table with column names.

private void getByObjectAndPollutant(string Object, string Pollutant, 
    int firstYear, int lastYear) 
{
    List<string> result = new List<string>();
    // line breaks added without concatenation, for readability.
    cmd = new OleDbCommand("SELECT object_name, p_name, mpc, emission_concentration, 
        [year] FROM Pollutants INNER JOIN (Objects INNER JOIN Emissions 
        ON Objects.o_id = Emissions.e_o_id) ON Pollutants.p_id = Emissions.e_p_id 
        WHERE (object_name = '" + Object + "' AND p_name = '" + Pollutant + "' AND 
        [year] BETWEEN " + firstYear + " AND " + lastYear + ") ;", con);          
    con.Open();
    reader = cmd.ExecuteReader();
    while (reader.Read()) {
        var myString = reader.GetString(0);
        result.Add(myString);
        richTextBox1.Text += myString;
    }
    con.Close();
}

This is how I call it in program:

private void button5_Click(object sender, EventArgs e) {
    string localObject = comboBox5.Text.ToString();
    string localPollutant = comboBox4.Text.ToString();
    int localFirstYear = Convert.ToInt32(comboBox6.Text);
    int localLastYear = Convert.ToInt32(comboBox7.Text);
    getByObjectAndPollutant(localObject,localPollutant,localFirstYear,localLastYear);
}

This is SELECT query in MS Access:

SELECT object_name, p_name, mpc, emission_concentration, year  
FROM Pollutants 
INNER JOIN (Objects 
    INNER JOIN Emissions ON Objects.o_id=Emissions.e_o_id) 
ON Pollutants.p_id=Emissions.e_p_id  
WHERE object_name="some name" And p_name="some name" And year Between 2011 And 2015;

And this was the previous code of method:

private void getByObjectAndPollutant(string Object, string Pollutant, int firstYear, 
    int lastYear) 
{
    con.Open(); 
    DataTable dt = new DataTable(); 
    adapt = new OleDbDataAdapter(this long select); 
    adapt.Fill(dt); 
    dataGridView2.DataSource = dt; 
    con.Close();
}

Main reason I use reader or adapter is to get the result of SELECT query. Is this SELECT returning something? When this works I will then code method to draw chart based on SELECT.

One problem I notice is that you only use dataReader.GetString(0). The rest of the columns are not accessed at all. I also highly suggest the string indexer datareader[“colName”]] — Alexandru Clonțea, Mar 03 '18 at 09:34
Maybe string.Join(reader.GetValues(), “.”) just as a quick check instead of getstring(0) — Alexandru Clonțea, Mar 03 '18 at 09:39
Possible duplicate of [How do I loop through rows with a data reader in C#?](https://stackoverflow.com/questions/8370927/how-do-i-loop-through-rows-with-a-data-reader-in-c) — Alexandru Clonțea, Mar 03 '18 at 09:46
@Ysadoriel Don't write the source code in a comment, in belongs to the question. Edit your question to include the source code in the question. — Progman, Mar 03 '18 at 13:13
@wazz sorry, that's my fault. It happens when someone adds `>` all over the place without any reason. — Erik A, Mar 05 '18 at 13:58

score 0 · Answer 1 · answered Mar 04 '18 at 17:00

0

Could be the combos/dropdownlists. Weirdly, comboBox5.Text returns the selected value, not the text.

Try changing to

string localObject = comboBox5.SelectedItem.Text;
string localPollutant = comboBox4.SelectedItem.Text;

answered Mar 04 '18 at 17:00

wazz

4,953
5
20
34

Unfortunately this answer don’t change anything. But it’s much better than just taking text from comboBoxes. – Ysadoriel Mar 05 '18 at 07:37
1

And I tested before is my comboBox texts returns the right values and they are returning right values. – Ysadoriel Mar 05 '18 at 07:39

score 0 · Accepted Answer · answered Mar 05 '18 at 08:36

Hello! Finally I find the solution. The main aim of SELECT was to get some data from DataBase, but while I getting it I use not ID of an Item, I use Name of an Item and it return no result. So i think let me try to make condition with ids.

So the code of working function:

private void getByObjectAndPollutant(int Object, int Pollutant, int firstYear, int lastYear)
            {          
                con.Open();
                DataTable dt = new DataTable();
                adapt = new OleDbDataAdapter("SELECT mpc, emission_concentration FROM Pollutants INNER JOIN (Objects INNER JOIN Emissions ON Objects.o_id = Emissions.e_o_id) ON Pollutants.p_id = Emissions.e_p_id WHERE Objects.o_id=" + Object + " AND Pollutants.p_id=" + Pollutant + " AND Emissions.[year] BETWEEN " + firstYear + " AND " + lastYear + ";", con);
                adapt.Fill(dt);
                dataGridView2.DataSource = dt;
                con.Close();
            }

The code of working function call:

private void button5_Click(object sender, EventArgs e)
        {
            int localObject = Convert.ToInt32(comboBox4.SelectedValue.ToString());
            int localPollutant = Convert.ToInt32(comboBox5.SelectedValue.ToString());
            int localFirstYear = Convert.ToInt32(comboBox6.SelectedItem.ToString());
            int localLastYear = Convert.ToInt32(comboBox7.SelectedItem.ToString());

            getByObjectAndPollutant(localObject,localPollutant,localFirstYear,localLastYear);
        }

And in result DataGridView I get the right data I`m happy so much!!! Thanks to ALL of You who was trying to help me!!!

Select doesn't return result

2 Answers2