Visual Basic Discussion Boards

mebjen30-Jan-13 9:05

30-Jan-13 9:05

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

Re: Query dataTable with .Compute

Dave Kreskowiak30-Jan-13 10:20

Dave Kreskowiak

30-Jan-13 10:20

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

Re: Query dataTable with .Compute

mebjen30-Jan-13 10:45

mebjen

30-Jan-13 10:45

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")
        salesTable.Columns.Add(col_shipQty)

        Dim col_extPrice As DataColumn = New DataColumn("extPrice")
        col_extPrice.DataType = System.Type.GetType("System.Decimal")
        salesTable.Columns.Add(col_extPrice)

Public getYrSales(ByVal stn as String)
Dim begDate as Date
Dim endDate as Date
Dim yrSales = salesTable
Dim x0 As Object
begDate = "01/01/2009"
endDate = "12/31/2009"

x0 = yrSales.Compute("SUM(extPrice)", "shipToName = '" & stn & "'" & "AND invDate >" & begDate & "AND invDate <" & endDate)
End Sub

Unforturnately - I'm still getting the error / what am I missing?

Thanks,

MB

Re: Query dataTable with .Compute

Dave Kreskowiak30-Jan-13 14:28

Dave Kreskowiak

30-Jan-13 14:28

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

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.