Query dataTable with .Compute

[mebjen]

Hi All, Can't figure this out . . . . I am trying to run a .Compute against a dataTable but am getting an 'EvaluateExecption was unhandled' error: Cannot perform '<' operation on System.String and System.Double. Here is my code: Dim salesTable as DataTable salesTable = New DataTable With salesTable .Columns.Add("invNo") .Columns.Add("custId") .Columns.Add("custName") .Columns.Add("shipToId") .Columns.Add("shipToName") .Columns.Add("shipToCity") .Columns.Add("shipToState") .Columns.Add("invDate") .Columns.Add("category") .Columns.Add("itemId") .Columns.Add("desc") .Columns.Add("shipQty") .Columns.Add("extPrice") End With Public Sub getYrSales(ByVal stn As String) Dim yrSales = salesTable Dim x0 As Object = yrSales.Compute("SUM(extPrice)", "shipToName = '" & stn & "'" & "AND invDate >" & begDate & "AND invDate <" & endDate) End Sub

Thanks, MB

[Dave Kreskowiak]

Read the error message again. In your Compute, you've got "AND invDate < endDate". Apparently, your invDate column datatype is either String or Double and the endDate variable you're passing in contains data of the other type. Compute will NOT do automatic type conversion to coerce the compare to work, so YOU have to make sure you're storing data appropriately, like NOT storing dates in the database as strings or non-date types. It's impossible to say for sure because you haven't shown the column definition for invDate nor do we know what type and the contents of the endDate variable.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak

[mebjen]

Hi Dave, Thanks for the feedback - I have made the following changes: Dim salesTable As DataTable salesTable = New DataTable

Dim col_invNo As DataColumn = New DataColumn("invNo")
col_invNo.DataType = System.Type.GetType("System.String")
salesTable.Columns.Add(col_invNo)

    Dim col\_custId As DataColumn = New DataColumn("custId")
    col\_custId.DataType = System.Type.GetType("System.String")
    salesTable.Columns.Add(col\_custId)

    Dim col\_custName As DataColumn = New DataColumn("custName")
    col\_custName.DataType = System.Type.GetType("System.String")
    salesTable.Columns.Add(col\_custName)

    Dim col\_shipToId As DataColumn = New DataColumn("shipToId")
    col\_shipToId.DataType = System.Type.GetType("System.String")
    salesTable.Columns.Add(col\_shipToId)

    Dim col\_shipToName As DataColumn = New DataColumn("shipToName")
    col\_shipToName.DataType = System.Type.GetType("System.String")
    salesTable.Columns.Add(col\_shipToName)

    Dim col\_shipToCity As DataColumn = New DataColumn("shipToCity")
    col\_shipToCity.DataType = System.Type.GetType("System.String")
    salesTable.Columns.Add(col\_shipToCity)

    Dim col\_shipToState As DataColumn = New DataColumn("shipToState")
    col\_shipToState.DataType = System.Type.GetType("System.String")
    salesTable.Columns.Add(col\_shipToState)

    Dim col\_invDate As DataColumn = New DataColumn("invDate")
    col\_invDate.DataType = System.Type.GetType("System.DateTime")
    salesTable.Columns.Add(col\_invDate)

    Dim col\_category As DataColumn = New DataColumn("category")
    col\_category.DataType = System.Type.GetType("System.String")
    salesTable.Columns.Add(col\_category)

    Dim col\_itemId As DataColumn = New DataColumn("itemId")
    col\_itemId.DataType = System.Type.GetType("System.String")
    salesTable.Columns.Add(col\_itemId)

    Dim col\_desc As DataColumn = New DataColumn("desc")
    col\_desc.DataType = System.Type.GetType("System.String")
    salesTable.Columns.Add(col\_desc)

    Dim col\_shipQty As DataColumn = New DataColumn("shipQty")
    col\_shipQty.DataType = System.Type.GetType("System.Int32")

[Dave Kreskowiak]

You don't need to specify System. The System namespace is automatically imported in VB.NET. You also don't need to specify System.Type.GetType... GetType alone is sufficient.

Dim col\_invDate As DataColumn = New DataColumn("invDate")
col\_invDate.DataType = System.Type.**GetType("System.DateTime")**
salesTable.Columns.Add(col\_invDate)

should become this:

Dim col\_invDate As DataColumn = New DataColumn("invDate")
col\_invDate.DataType = **GetType(DateTime)**
salesTable.Columns.Add(col\_invDate)

Next, according to the documentation on DataTable.Compute and DataColumn.Expression, a DateTime value should be enclosed in single quotes or the # sign, depending on the underlying data provider. ...and to make things MUCH easier to read, don't use string concatentation:

x0 = yrSales.Compute("SUM(extPrice)", String.Format("shipToName = '{0}' AND invDate > #{1}# AND invDate < #{2}#", stn, begDate, endDate))

BTW: x0 is a terrible variable name. By looking at the variable name alone, what does it contain?? There is no way to tell.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak