This project has moved. For the latest updates, please go here.

Decimal and Date query error on dataset

Topics: Developer Forum, User Forum
Aug 13, 2008 at 1:56 PM
I am running a query against a VB .NET dataset with multiple tables.  I get two errors when I run the query.  One is that I cannot compare a double to a decimal and the other a string to a date.  Here are the two samples of how the query looks.

SELECT * FROM Customer, Payments
WHERE C_ID = P_C_ID and Pay_Offset = 500.50 and Pay_Date = '5/13/2008 12:00:00 PM'

Pay_Offset is a decimal(18, 2) type column in the table and Pay_date is a datetime type column.  The dataset is filled with data from a database that declares these types.

I have tried using quotes and no quotes around the dollar amount and it will change from can't compare decimal to double to can't compare decimal to string.  The date value I have tried with and without the PM and I still receive the error.  Am I putting the values in wrong in the query?  Is this something with how NQuery handles values that it auto converts the dollar amount to a double even though the field is decimal?
Coordinator
Aug 18, 2008 at 9:18 AM
Hi langc334,

sorry for the late answer but this weekend I had to ship Clone Detective for Visual Studio.

NQuery shares many characteristics with C# when it comes to implicit conversions and overload resolution. So if you want to compare a decimal with a double you have to convert one of the operands, e.g.
Pay_Offset = TO_DECIMAL(500.50)
If you want to use date literals you have to use pound signs as a delimiter, e.g.
Pay_Date = #5/13/2008 12:00:00 PM#
Here is the full repro source:
public sealed class Customer
{
    public int C_ID;

    public Customer(int c_ID)
    {
        C_ID = c_ID;
    }
}

public sealed class Payment
{
    public int P_C_ID;
    public decimal Pay_Offset;
    public DateTime Pay_Date;

    public Payment(int p_C_ID, decimal pay_Offset, DateTime pay_Date)
    {
        P_C_ID = p_C_ID;
        Pay_Offset = pay_Offset;
        Pay_Date = pay_Date;
    }
}

private static void Main()
{
    List<Customer> customers = new List<Customer>();
    customers.Add(new Customer(1));
    customers.Add(new Customer(2));

    List<Payment> payments = new List<Payment>();
    payments.Add(new Payment(1, 500.50m, new DateTime(2008, 5, 13, 12, 0, 0)));
    payments.Add(new Payment(2, 600.50m, DateTime.Now));

    DataContext dataContext = new DataContext();
    dataContext.Tables.Add(customers, "Customer");
    dataContext.Tables.Add(payments, "Payments");

    Query query = new Query();
    query.DataContext = dataContext;
    query.Text = @"
                    SELECT    *
                    FROM    Customer,
                            Payments
                    WHERE    C_ID = P_C_ID
                    AND        Pay_Offset = TO_DECIMAL(500.50)
                    AND        Pay_Date = #5/13/2008 12:00:00 PM#
                ";

    DataTable table = query.ExecuteDataTable();
    table.TableName = "Result";
    table.WriteXml(Console.Out);

    // Output:
    //<DocumentElement>
    //  <Result>
    //    <C_ID>1</C_ID>
    //    <P_C_ID>1</P_C_ID>
    //    <Pay_Offset>500.50</Pay_Offset>
    //    <Pay_Date>2008-05-13T12:00:00+02:00</Pay_Date>
    //  </Result>
    //</DocumentElement>
}
Kind Regards,
Immo Landwerth
Aug 19, 2008 at 1:26 PM
terrajobst,

Thanks for the response.  I will insert those 2 adjustments to my query and give it a run.