The best approach to getting a data subset

[pachjo]

I have a listview that has a context menu that facilitates the selecting of multiple rows and stores the selected id in an array which is then passed to a function to work on.

No once in the function I want to access only the items in my table that has a matching id in the array but cannot figure out the best approach.

Are any or all or none of these the way to do it?

call with a sql statement?

me.MyFinanceDataSet1.Transactions.Select("an sql statement goes here")

create a string with the ids as a parameter to the CustomFill method to be used in an IN clause?

me.TransactionsTableAdpater.CustomFill(strIDList)

But I cannot see how to create the sql in the tableadapter that allows something like:
SELECT ..... WHERE ID IN @IDList

Create a temporary dataset/tableadapter and work with this?

Thanks

 

[cjard]

Are any or all or none of these the way to do it?

call with a sql statement?

me.MyFinanceDataSet1.Transactions.Select("an sql statement goes here")

An SQL statement does not go there. A string that looks a bit like the WHERE clause of an MS SQL server statement goes there. For a full list of syntax, see the DataColumn.Expression property documentation in msdn

create a string with the ids as a parameter to the CustomFill method to be used in an IN clause?

Heck no.

Create a temporary dataset/tableadapter and work with this?

Erm, that's pretty good actually. You cant create a temporary set because the rows in it already belong to another table.. Take a look at your listview.SelectedItems property; it should be a collection/array of DataRowView.. just pass the whole collection to your function with a cast, and use a foreach loop to dig out the .Row of each entry. Cast it into the typed form of the row and it should work.

Sub Button_Click() Handles Button1.Click

  MyFunc(DirectCast(ListView.SelectedItems, DataRowView() ))

End Sub

Fucntion MyFunc(rowvs() as DataRowView) As Whatever
  Dim tro as MyDataSet.TransactionsRow

  For Each drv as DataRowView in rowvs
    tro = DirectCast(drv.Row, MyDataSet.TransactionsRow)

    tro.X = tro.A + tro.B
  Next drv
End Func

Double check this. I wrote it blind

 

[pachjo]

Phew! if only people would stop giving me things to do I could get round to doing the fun things in life....like programming!

Anyway, Thanks for the suggestion, have tried various permatations but without success due to not being able to cast to 1 dimentional array

 

[cjard]

Phew! if only people would stop giving me things to do I could get round to doing the fun things in life....like programming!

Anyway, Thanks for the suggestion, have tried various permatations but without success due to not being able to cast to 1 dimentional array

Use the debugger to find out what type the .SelectedItems actually is, then.. and then work out how to dig the rows out of it.. I cant help you much with that because I dont have the code to your app

 

[pachjo]

This does the job a treat

' call the void/unvoid function

If Not VoidTransactions(objSelectedItems, blnVoid) Then

  .........

End If

' this does the job, error trap and other guff omitted for clarity

Private Function VoidTransactions(ByVal objSelectedItems As ListView.SelectedListViewItemCollection, ByVal blnVoid As Boolean) As Boolean

        Dim x As Integer

        For x = 0 To objSelectedItems.Count - 1

            Me.MyFinanceDataSet1.Transactions.Rows.Find(objSelectedItems(x).SubItems(4).Text()).Item("Void") = blnVoid

        Next

        Me.TransactionsTableAdapter.Update(Me.MyFinanceDataSet1.Transactions)

VoidTransactions=True

 

[cjard]

Oh.. Heh.. I had no idea that your ListView wasnt databound...

 

[pachjo]

Hi well cjard I would say it is almost, well it is, down to you that I have restarted my application with the same interface but the engine, guts or whatever you want to call it is now taking shape in amuch more streamlined and easier way to code and understand.

This of course being down to the use of datasets, tableadapters and bindingsources.

The more I do it this way and see how much 'dross' I remove and replace with far fewer lines the more I am insensed with the SAMS books for showing me the crap way.

However in response to your last comment the reason the listview is not databound is this:

I now have a single transaction table as follows;

TransactionID (primary)
TransactionDate
TransactionAmount
DescriptionID (foreign key to the transactiondescription table)
Void

I also have a transaction description table as follows;

DescriptionID (primary)
Description

Now when I .Fill the transaction table I do so using just the transaction table as at this point that is all that is needed and it allows me to have the update method created by the designer.

When I call the function to populate the listview to show the user the transactions I have a method called .FillListView that pulls through the description that matches the DescriptionID thus displaying to the user the description text rather than a meaningless id number.

Now if I included a join in the .Fill method to pull through the description text at that point the degigner does not create an update method. I also could not get an update method created manually either.

So my work around was to use the .Fill for loading the data and updating using the created .Update method. And use a seperate .FillListView for display purposes.

Hope I explained myself ok?

But the upshot is it works as expected....

 

[cjard]

The more I do it this way and see how much 'dross' I remove and replace with far fewer lines the more I am insensed with the SAMS books for showing me the crap way.

ROFLMAO

However in response to your last comment the reason the listview is not databound is this:

When I call the function to populate the listview to show the user the transactions I have a method called .FillListView that pulls through the description that matches the DescriptionID thus displaying to the user the description text rather than a meaningless id number.


Hope I explained myself ok?

OK, I hope the following advice doesnt sicken you too much. Here is what you would do:

Have a datatable that has 2 columns: DescriptionID, DescriptionText
Fill it with all of them (there shouldnt be more than 1000 hopefully )
Fill your Transactions table
Databind it to a DatagridView
In the TransactionID column of the DGV, set its type to DataGridViewComboBoxCOlumn
In the properties of this combo column:
DataSource = Descriptions datatable
ValueMember = DescrID
DisplayMember = DescrText
DataPropertyName should be already bound to DescrID in the transactions table

That's it.. When your Tran table says:

DescID = 1
Your combo looks up ValueMember 1 in Descriptions.DescrID, and Shows the related text from Descriptions.DescrText = "Authorised"
If your user picks "Declined" from the combo, and that is related to value 2, then a 2 will be written back into the Transactions table

No datarelation used for this..

You can hide the combo downarrow and make it not editable if all you want is a decode

 

[pachjo]

Mmm, not sickened at all as it is all about learning new and better ways of doing things

I will have a play with your approach which from what I can see removes a nice chunk of code re an context menu and supporting code etc which is good.

However, this in effect restricts the user to authorise/decline 1 row at a time whereas the method I use at present allows multirow selection actioning.

But in saying this I might find that after implementing your approach it works in a more stremlined fashion.

Cheers ears

 

[cjard]

However, this in effect restricts the user to authorise/decline 1 row at a time whereas the method I use at present allows multirow selection actioning.

You can have a context menu with the same combo embedded, the user selects all the records to change, right clicks them, chooses the combo, and your code will set them.. Here is the code I use to do this with strings:

    private void setSelectedToToolStripMenuItem_Click(object sender, EventArgs e)
    {
      string s = InputBox.Show("What value?");
      if(s == null) 
        return;

      List<DataRow> ldr = new List<DataRow>();
      List<string> ls = new List<string>();
      foreach(DataGridViewCell c in zEB_ROW_LABELSDataGridView.SelectedCells) {
        ldr.Add((c.OwningRow.DataBoundItem as DataRowView).Row);
        if(!ls.Contains(c.OwningColumn.DataPropertyName))
          ls.Add(c.OwningColumn.DataPropertyName);
      }

      zEB_ROW_CONFIGDataGridView.SuspendLayout();
      foreach(string st in ls) {
        foreach(DataRow ro in ldr) {
          ro[st] = s;
        }
      }
      zEB_ROW_CONFIGDataGridView.ResumeLayout();
      

    }

I do this roundabout way (get a list of all the underlying rows, get a list of each column selected) because I found that jsut saying For/Each in SelectedCells was problematic if I was altering a column that was sorted;; the alterations changed the sort order, but NOT the selection.. So an edit would re-sort the grid, and wrong cells moved into the selection area

 

[pachjo]

Hi, well I am finding this a very interesting approach ... and so easy comapred to all the code I was doing .... Yoinks!!

However one option I cannot find for the datgridview is how to turn off the row selection column than appears to the left of the first headed column.

Call me picky but I don't want this displayed, can it be disabled and if so how as I can't see where this is possible?

Thanks

 

[pachjo]

Aha .. RowHeaderWidth = 4 does the trick

Cjard once again you have made suggestions that have had a major impact and improvement on how I do things

Hats off to you ... thanks

 

[cjard]

Its a property of the DataGridView: RowHeadersVisible. I'm amazed you missed it; it's right above the RowHeadersWidth you have just used

 

[pachjo]

oh crap!!!! i may as well become a gardener!!!!!

 

[pachjo]

Just back from holiday and in my lame defence I must say that RowHeadersVisible is not above the RowHeadersWidth on my properties page propbably due to my ordering being different from yours but hey....I still missed it