Querying a dataset

Posted on 2006-05-14
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
    LVL 17

    Expert Comment

    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).
    LVL 4

    Author Comment

    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

    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.

    LVL 4

    Author Comment

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

    Author Comment

    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

    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
    Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now