Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Querying a dataset

Posted on 2006-05-14
Medium Priority
Last Modified: 2010-04-23
Hi Guys

I am using a dataset to hold a whole lot of entries for a competition then I use a randon number to select which row won what I then want to do is update the dataset by removing all instances of the winners phone number as I need to choose more than one winner and don't want to return the same user more than once. I could loop thorugh all the rows deleting it if it has that number but that seems very time consuming. Is there a way to remove rows with a query?

Also I am populating the dataset using a SqlDataAdapter if I do remove rows from the dataset that will not delete anything from the original tables in the database correct?

Question by:peter58
  • 3
  • 2
LVL 17

Expert Comment

ID: 16678650
Assuming that you have your original dataset (dsEntries) with a column called "PhoneNumber", you can create a DataView to filter the winning phone numbers.  Your row filter can be a string that you continually build upon, so for your first winner, your string might look like:  
     strFilter = "PhoneNumber <> " & strWinnerPhone

and to filter out your second winner (along with the first), it would like like:  
     strFilter &= " and PhoneNumber <> " & strWinnerPhone2

It seems to be more efficient to filter out a couple rows, instead of removing them all together, unless your going to be filtering out more than 50% of your entrants.

As an example --

     Dim strWinnerPhone As String = "5551234"
     Dim strWinnerPhone2 As String = "5555678"
     Dim dvNonWinners As New DataView(dsEntries.Tables(0))

     dvNonWinners.RowFilter = "PhoneNumber <> " & strWinnerPhone & " and PhoneNumber <> " & strWinnerPhone2
     DataGrid2.DataSource = dvNonWinners

To answer your second question, the DataAdapter will not cause any rows to be removed from the actual database until you fire the .Update method on the DataAdapter (assuming it has a Delete Command present).

Author Comment

ID: 16678735
I am not displaying the results, I need the resulting data to be in something where I can reference a row by number, ie ds.Tables("Golf").Rows(1).Item("PhoneNumber") That way I can use a random number to get the row I want. Can I use a datagrid to select a row by number or can I put the results of the DataView in a temp dataset.
LVL 11

Accepted Solution

anyoneis earned 1000 total points
ID: 16679103
Why not add a column to the table, say "Eligible", that defaults to true. When you load the table, every row will get the default value. When they are chosen, you set "Eligible" to false.

' After you have loaded the data, asdd the column
Dim dc as DataColumn = new DataColumn("Eligible", System.Type.GetType("System.Boolean"))
dc.DefaultValue = true;

You want to work with a filtered view, so code something like:

dim filteredView as DataView = ds.CreateDataView(ds.Tables("Golf"))
filteredView.RowFilter = "Eligible = true"

Now, always go against the filteredView. When you generate the random number, make the high value one less than filteredView.Count. Rows that have Eligible set to false are not in the view, even though they are still in the table.


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Author Comment

ID: 16679217
Sounds good, but can I select a row using it's index in a view? eg Row(5)?

Author Comment

ID: 16679227
One more thing is it possible to update the eligible field in the dataset using a query eg "update me set eligible = 0 where phonenumber = '5551234'"
LVL 11

Expert Comment

ID: 16687325
A quick trip to the DataView docs will tell you all about accessing a row in a view. Yes, you can use an index.

In my example, you would set the eligible column value for a record with something like:

filteredView(index)("Eligible") = false


Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question