none
DataView - Filtering via IsNumeric & sorting via Val() - Migrating from SQL to DataTables/DataViews RRS feed

  • Question

  • Our application originally submitted SQL to perform some analysis:

    sSQL = "SELECT TOP " & highestCount & " samplevalue, id " & vbCrLf
    sSQL = sSQL & "FROM SAMPLEDATA " & vbCrLf
    sSQL = sSQL & "WHERE lmof_id=@P_ID " & vbCrLf
    sSQL = sSQL & "AND isnumeric(samplevalue) " & vbCrLf
    sSQL = sSQL & "ORDER BY val(samplevalue) DESC " & vbCrLf

    I'm currently using a TableAdapter to fill a DataTable with a parameterized query:

    dt = ta.GetDataByLMOFID(lmof_id)

    Now with the raw data I need to filter out the non-numeric values.  The DataView RowFilter parameter fails on "isnumeric(samplevalue)" with "System.Data.EvaluateException: The expression contains undefined function call isnumeric()..".

    Additionally, the orderby of "val(samplevalue)" doesn't work. 

    Given the end result required, can someone provide me with a little guidance?

    Thanks for reading,
    CK

    Monday, January 4, 2010 2:03 PM

Answers

  • Hello CK,

     

    Glad to see you again!

     

    Based on your description, I think you are trying to translate the IsNumeric and Val functions in Access into DataView.RowFilter and DataView.Sort properties.  The exception that you received is thrown because IsNumeric function is invalid for DataView RowFilter. 

     

    As far as I know, it is really hard to realize the IsNumeric and Val functions only with the help of RowFilter and Sort properties.  The CONVERT function of the RowFilter syntax may be helpful, but if we try to convert a non-numeric value into some numeric CLR types, we will receive an exception.  For more detailed information about the RowFilter syntax, please see

    http://www.csharp-examples.net/dataview-rowfilter/

    http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx

     

     

    LINQ to DataSet would be a great workaround if you are using VS2008 and .NET 3.5 (or SP1).   Here are some VB.NET sample codes to filter and order by the column “Value”:

    =============================================================================================

            Dim value As Decimal

            Dim rows = From row In table _

                       Where Decimal.TryParse(row.Field(Of String)("Value"), value) _

                       Order By Val(row.Field(Of String)("Value")) Descending

    =============================================================================================

     

    If your project is targeted to .NET 2.0, here is a workaround to add a new column so that we can sort the value according to this new column:

     

    We first clone the table and then add or the numeric rows into the new tables.  After that, we add a new column named “SortValue” and set its Expression using the CONVERT function.

    =============================================================================================

            Dim table2 = table.Clone()

     

            For Each row As DataRow In table.Rows

                If (Decimal.TryParse(row("Value").ToString(), value)) Then

                    table2.ImportRow(row)

                End If

            Next

     

            table2.Columns.Add("SortValue", GetType(Decimal))

            table2.Columns("SortValue").Expression = "Convert(Value, System.Decimal)"

            table2.DefaultView.Sort = "SortValue DESC"
    =============================================================================================

     

    If you have any questions, please feel free to let me know.

     

    Have a great day!

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by ckelker Tuesday, January 5, 2010 9:28 PM
    Tuesday, January 5, 2010 3:03 AM
    Moderator

All replies

  • Hello CK,

     

    Glad to see you again!

     

    Based on your description, I think you are trying to translate the IsNumeric and Val functions in Access into DataView.RowFilter and DataView.Sort properties.  The exception that you received is thrown because IsNumeric function is invalid for DataView RowFilter. 

     

    As far as I know, it is really hard to realize the IsNumeric and Val functions only with the help of RowFilter and Sort properties.  The CONVERT function of the RowFilter syntax may be helpful, but if we try to convert a non-numeric value into some numeric CLR types, we will receive an exception.  For more detailed information about the RowFilter syntax, please see

    http://www.csharp-examples.net/dataview-rowfilter/

    http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx

     

     

    LINQ to DataSet would be a great workaround if you are using VS2008 and .NET 3.5 (or SP1).   Here are some VB.NET sample codes to filter and order by the column “Value”:

    =============================================================================================

            Dim value As Decimal

            Dim rows = From row In table _

                       Where Decimal.TryParse(row.Field(Of String)("Value"), value) _

                       Order By Val(row.Field(Of String)("Value")) Descending

    =============================================================================================

     

    If your project is targeted to .NET 2.0, here is a workaround to add a new column so that we can sort the value according to this new column:

     

    We first clone the table and then add or the numeric rows into the new tables.  After that, we add a new column named “SortValue” and set its Expression using the CONVERT function.

    =============================================================================================

            Dim table2 = table.Clone()

     

            For Each row As DataRow In table.Rows

                If (Decimal.TryParse(row("Value").ToString(), value)) Then

                    table2.ImportRow(row)

                End If

            Next

     

            table2.Columns.Add("SortValue", GetType(Decimal))

            table2.Columns("SortValue").Expression = "Convert(Value, System.Decimal)"

            table2.DefaultView.Sort = "SortValue DESC"
    =============================================================================================

     

    If you have any questions, please feel free to let me know.

     

    Have a great day!

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by ckelker Tuesday, January 5, 2010 9:28 PM
    Tuesday, January 5, 2010 3:03 AM
    Moderator
  • Sadly, we are stuck with 2.0. 

    I guess that makes sense.  I was hoping for some more powerful expressions.  At least, I can proceed!

    Thanks!
    CK
    Tuesday, January 5, 2010 10:01 PM
  • You are welcome!

     

    Have a great day!

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, January 6, 2010 12:44 AM
    Moderator