[RESOLVED] Filtering

[Quasar6]

Hi again everyone.

I'm using Microsoft Access (2003), Visual C# .Net, and an OleDbConnection.

I've been filtering a data grid view by means of a Fill Command:

SELECT     ID, [Column 2], [Column 3], [Column 4]
FROM         [Table 3]
WHERE     ([Column 2] = ?)

By simply calling this every time Column 2 changes, the datagrid view continually displays only the records that are relevant to the current object (which is denoted in colmn 2, obviously).

I now need to make it possible to select (and display) several objects at once (still filtering by column 2, but where Column 2 is equal to any item in the specified List<>. The list can be any size).

Is there a way to retrieve this? Or a better way of doing things?

Thanks in advance.
Qu.

[wild_bill]

See if access allows you to use 'in'

SELECT     ID, [Column 2], [Column 3], [Column 4]
FROM         [Table 3]
WHERE     ([Column 2] in ('thing1','thing2'))

[jmcilhinney]

See the link in my signature about using parameters with an IN clause.

[Quasar6]

Thanks guys: the IN statement is what I need, but I've come across another problem. I've been googling various SQL references all morning, and haven't been able to solve it even though it should be really simple.

I'm using the designer to generate my Fill statement, and have eventually come up with this:

SELECT     ID, [RFI #], [Area/Seq], [Requested By], etc...
FROM         RFI
WHERE     (ID IN (?))

ID is an autonumber field.

The method generated is:

TableAdapter.FillByIDNum(DataTable Table, int Param1);

Having gathered a list of integers, I need to display only those whose ID matches an integer in the list. Unfortionately, I can't feed in a list of integers into a field that takes only 1 integer, and I can't change the type to string because then I get a dataTypeMismatch.

Does anyone know the way around this? I'm at my wits end.

[jmcilhinney]

You can't use the designer if your query changes at run time. Did you read the link I mentioned?

You cannot use a single parameter for the whole list, but if you don't know how many values will be in the list then you can't add the parameters easily.

[Quasar6]

Yes, I did read your link, and at the time it gave me what I thought I needed: the ability to use the IN statement.

I don't really want to have to rebuild the entire system, because I've already done a lot of work in the designer. Hmmm... can I define an SqlCommand (as you do in your link) and somehow plug it straight into a TableAdapter?

Dealing with access makes me feel so ignorant.

[jmcilhinney]

A TableAdapter is a wrapper for a DataAdapter and a connection. The TableAdapter has a SelectCommand property, which is a pass-through for the internal DataAdapter's SelectCommand property. You could, if you really wanted to, set the CommandText of that Command at run time. I've never tried before but I'm fairly sure it's public as you can access it in the Properties window.

That said, it's easy enough to create a DataAdapter and use it to Fill the same DataTable you would have with your TableAdapter.

[Quasar6]

Apparently those variables are not public, so I tried to create a DataAdapter. Never having done it before, I must have screwed somthing up

It doesn't seem to make any difference to the Datagridview.

Here's my code:

string MainSQLFilter = @"SELECT     ID, [RFI #], [Area/Seq] //etc...
FROM         RFI
WHERE     (ID IN (";
            //Builds the ID List using string concatenation. I know it's bad practice, but this is only for testing.
            for (i = 0; i < idNumbers.Count; i++ )
            {
               MainSQLFilter += idNumbers[i].ToString();
                if (i < idNumbers.Count - 1)
                {
                    MainSQLFilter += ", ";
                }
                else
                {
                    MainSQLFilter += "))";
                }
            }
            //Both of these seem to return correctly
            MessageBox.Show(MainSQLFilter + "\r\n" + connectionString);
            //And here I make a DataAdapter
            OleDbDataAdapter da = new OleDbDataAdapter(MainSQLFilter, connectionString);
            //And use it to fill the data table
            da.Fill(virtualModelDataSet.RFI);

But my DataGridView doesn't change. Any idea why?

[jmcilhinney]

What does Fill return? Is that DataTable bound to your grid?

[Quasar6]

Fill returns a 2, which I believe is correct: the list I'm feeding in consists of 5,6,28 and 30, and only 5 and 6 appear in the ID column.

The DatagridView's DataSource is a Binding Source, which has the Table as it's DataMember property and the DataSet as it's DataSource property.
This is the setup automatically generated by the designer.

[Quasar6]

Bump.

[jmcilhinney]

If your DataGridView is bound to a BindingSource then the grid will reflect whatever data is exposed by that BindingSource. If the BindingSource is bound to a DataTable then the BindingSource will expose whatever data is in the DataTable, depending on its Sort and Filter properties. If you are adding new rows to a DataTable when you call Fill and no new rows show up in your grdi then there are only two possibilities that I can think of:

1. The table is not bound to the grid.
2. The Filter property of the BindingSource prevents those new records being displayed.

First up, I think you'll find that, by default, calling Fill on a DataAdapter will not clear the existing data from the DataTable. Have you tested the number of rows in the DataTable before and after you call Fill to be sure the number is changing? Are you using the debugger to view the data in the DataTable before and after calling Fill? Can you confirm that the BindingSource isn't filtering the data out?

[Quasar6]

Really REALLY big thanks! This:

First up, I think you'll find that, by default, calling Fill on a DataAdapter will not clear the existing data from the DataTable.

Made me realise all I needed to do was call Clear() on my dataTable before filtering!

Again, big thanks! Thread resolved.