• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7049
  • Last Modified:

DS.Tables(0).Select

Experts,
I am trying to filter rows by using the .select method and assigning the result to table. The following code gives an error on the "table.Rows.Add(row(i))" line. The full code below:

            Dim row As DataRow() = DS.Tables(0).Select("SectorID >=2 AND SectorID <=9")
            Dim table As DataTable

            For i = 0 To row.Length
                table.Rows.Add(row(i))
            Next
0
feesu
Asked:
feesu
  • 6
  • 5
  • 3
  • +1
1 Solution
 
TSmoothCommented:
Usually it helps if you tell us what the exact error is. However, looking at your code, you are never assigning a value to the table variable, you are simply declaring it. Therefore, table is simply a null reference. Try changing your table declaration to:

Dim table As New DataTable

The new keyword will allocate the actual table and then your variable should now actually be referencing something.
0
 
Bob LearnedCommented:
I would bind to the DataTable's DefaultView, and apply the filter this way:

 Me.GridView1.DataSource = DS.Tables(0).DefaultView
 Me.GridView1.DataBind()

...

 DS.Tables(0).DefaultView.RowFilter = "SectorID >=2 AND SectorID <=9"
 Me.GridView1.DataBind()

Then, you don't have to do anything else.

Bob
0
 
feesuAuthor Commented:
Hi,
I used the "new" keyword, and got this error:

System.ArgumentException was unhandled by user code
  Message="This row already belongs to another table."
  Source="System.Data"
  StackTrace:
       at System.Data.DataTable.InsertRow(DataRow row, Int32 proposedID, Int32 pos, Boolean fireEvent)
       at System.Data.DataTable.InsertRow(DataRow row, Int32 proposedID, Int32 pos)
       at System.Data.DataTable.AddRow(DataRow row, Int32 proposedID)
       at System.Data.DataRowCollection.Add(DataRow row)
       at Broker_SectorsChart.Page_Load(Object sender, EventArgs e) in C:\Inetpub\wwwroot\AlSeefBroker\DesktopModules\Feed\Broker_SectorsChart.ascx.vb:line 65
       at System.Web.UI.Control.OnLoad(EventArgs e)
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
feesuAuthor Commented:
Bob,
I'm not filling a grid. It's a chart control. It accepts a datatable. When i used dataView for that, and filtered it, the chart just ignored the filter and showed everything!
0
 
Bob LearnedCommented:
>>the chart just ignored the filter and showed everything!
Can you explain how you did that?

Bob
0
 
feesuAuthor Commented:
That's my full code:


            Dim DB As New DB_Client_Online_Indices_ForBroker
            Dim row As DataRow() = DB.Client_Online_Indices_GetRecent.Tables(0).Select("SectorID >=2 AND SectorID <=9")
            Dim table As New DataTable

            For i = 0 To row.Length
                table.Rows.Add(row(i))
            Next

            Dim s As ChartSeries = New ChartSeries()
            s.Type = ChartSeriesType.Bar

            s.ShowLabels = True

            Dim iMax As Double = Me.RadChart1.YAxis.MaxValue, iMin As Double = Me.RadChart1.YAxis.MinValue
            For i = 0 To table.Rows.Count - 1 Step i + 1
                Dim item As ChartSeriesItem = New ChartSeriesItem()

                item.Label = table.Rows(i)("SectorName_A").ToString()
                item.YValue = Double.Parse(table.Rows(i)("Volume").ToString())

                iMax = IIf(item.YValue > iMax, item.YValue, iMax)
                iMin = IIf(item.YValue < iMin, item.YValue, iMin)

                s.Items.Add(item)
            Next
            RadChart1.AddChartSeries(s)

            '------------- Dynamic Min, Max, and Steps for Y Axis
            If iMax < iMin Then iMax = iMin
            Me.RadChart1.YAxis.MinValue = iMin
            Me.RadChart1.YAxis.MaxValue = iMax
            Dim iStep As Integer = (iMax - iMin) / 10 'iMax / iMin
            If Not iStep = 0 Then Me.RadChart1.YAxis.Step = iStep
0
 
TSmoothCommented:
Try modifying your original code to this:

Dim row As DataRow()
DS.Tables(0).Select("SectorID >=2 AND SectorID <=9").CopyTo(row, 0)
Dim table As New DataTable

For i = 0 To row.Length - 1
   table.Rows.Add(row(i))
Next
0
 
Bob LearnedCommented:
You would have to change from the DataTable/DataRow reference:

            Dim DB As New DB_Client_Online_Indices_ForBroker
 
            DB.Client_Online_Indices_GetRecent.Tables(0).DefaultView.RowFilter = "SectorID >=2 AND SectorID <=9"

            Dim s As ChartSeries = New ChartSeries()
            s.Type = ChartSeriesType.Bar

            s.ShowLabels = True

            Dim iMax As Double = Me.RadChart1.YAxis.MaxValue, iMin As Double = Me.RadChart1.YAxis.MinValue

            Dim dv As DataView = DB.Client_Online_Indices_GetRecent.Tables(0).DefaultView

            For Each drv As DataRowView In  dv
               Dim item As ChartSeriesItem = New ChartSeriesItem()

                item.Label = drv("SectorName_A").ToString()
                item.YValue = Double.Parse(drv("Volume").ToString())

                iMax = IIf(item.YValue > iMax, item.YValue, iMax)
                iMin = IIf(item.YValue < iMin, item.YValue, iMin)

                s.Items.Add(item)
            Next
            RadChart1.AddChartSeries(s)

            '------------- Dynamic Min, Max, and Steps for Y Axis
            If iMax < iMin Then iMax = iMin
            Me.RadChart1.YAxis.MinValue = iMin
            Me.RadChart1.YAxis.MaxValue = iMax
            Dim iStep As Integer = (iMax - iMin) / 10 'iMax / iMin
            If Not iStep = 0 Then Me.RadChart1.YAxis.Step = iStep

Bob
0
 
feesuAuthor Commented:
TSmooth,
What you sent didn't work. Gave this error:

System.IndexOutOfRangeException was unhandled by user code
  Message="Cannot find table 0."
  Source="System.Data"
  StackTrace:
       at System.Data.DataTableCollection.get_Item(Int32 index)
       at Broker_SectorsChart.Page_Load(Object sender, EventArgs e) in C:\Inetpub\wwwroot\AlSeefBroker\DesktopModules\Feed\Broker_SectorsChart.ascx.vb:line 50
       at System.Web.UI.Control.OnLoad(EventArgs e)
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)




Bob,
In my code, DB_Client_Online_Indices_ForBroker is a class that returns a Databset. The code you wrote doesn't apply.

0
 
Bob LearnedCommented:
I used your code as a template.

You had DataSet (DB) and DataTable (DB.Client_Online_Indices_GetRecent.Tables(0))

Tell me how I got that wrong.

Bob
0
 
feesuAuthor Commented:
DB is not a dataset, it is a class that returns a dataset. Now if you call any of its methods that return a dataset, you cannot filter it's default view then reuse it assuming that it is still filtered. Everytime you call a method, a new dataset is created.

The line that doesn't apply:

DB.Client_Online_Indices_GetRecent.Tables(0).DefaultView.RowFilter = "SectorID >=2 AND SectorID <=9"
0
 
Bob LearnedCommented:
Fine, then do it this way:

Dim DB As New DB_Client_Online_Indices_ForBroker
 
            Dim dv As DataTable = DB.Client_Online_Indices_GetRecent.Tables(0).DefaultView
            dv.RowFilter = "SectorID >=2 AND SectorID <=9"

            Dim s As ChartSeries = New ChartSeries()
            s.Type = ChartSeriesType.Bar

            s.ShowLabels = True

            Dim iMax As Double = Me.RadChart1.YAxis.MaxValue, iMin As Double = Me.RadChart1.YAxis.MinValue

            For Each drv As DataRowView In  dv
               Dim item As ChartSeriesItem = New ChartSeriesItem()

                item.Label = drv("SectorName_A").ToString()
                item.YValue = Double.Parse(drv("Volume").ToString())

                iMax = IIf(item.YValue > iMax, item.YValue, iMax)
                iMin = IIf(item.YValue < iMin, item.YValue, iMin)

                s.Items.Add(item)
            Next
            RadChart1.AddChartSeries(s)

            '------------- Dynamic Min, Max, and Steps for Y Axis
            If iMax < iMin Then iMax = iMin
            Me.RadChart1.YAxis.MinValue = iMin
            Me.RadChart1.YAxis.MaxValue = iMax
            Dim iStep As Integer = (iMax - iMin) / 10 'iMax / iMin
            If Not iStep = 0 Then Me.RadChart1.YAxis.Step = iStep

Bob
0
 
TSmoothCommented:
The error you got from my code means that your dataset didn't get any data into a table and you hadn't posted any code before that which showed how you were filling the dataset. Replace the first couple lines of my last code with:

Dim row As DataRow()
DB.Client_Online_Indices_GetRecent.Tables(0).Select("SectorID >=2 AND SectorID <=9").CopyTo(row, 0)

0
 
feesuAuthor Commented:
TSmooth,
The dataset returns records, i've tested it.

Bob,
"Dim dv As DataTable" in what you sent last should be "DataView". I pasted your code, changed this and it worked fine for me.

Thanks to you all!
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
If you want to do what you are trying to do in your original question, you need to copy the rows, you cannot add a row that is in another DataTable to another DataTable because of ownership and tracking.

So you could do it this way.
You can clone the table then go through the rows and delete the rows that do not fit your criteria and use that table
Dim dt as DataTable = DS.Tables(0).Clone()
For i = 0 to dt.Rows.Count-1
  If dt.Rows(i)("SectorID") >= 2 AND dt.Rows(i)("SectorID") <= 9 then
    ' do nothing because you have a good row
  Else
     dt.Rows(i).Delete()
  End If
Next
dt.AcceptChanges()
then use this table to pass to your chart.

Ben.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now