Yes I meant that:) Converting would be backend free.
Cetin

>Cetin,
>Thank you for the reply. I am acctually not using either VFP or SQL as a backend, I am using Advantage Database Server. I think the safest is to use the Convert class instead of casting the value, just in case I change the backend.
>
>Einar
>>Einar,
>>Different backends might be returning as something different from a decimal. ie: If backend is VFP it'd return decimal and the cast is valid, if SQL server it always returns an int (Int32) - with SQL server you might exceed the max value but for that one there is COUNT_BIG.
>>So make it to match all of them by converting. ie:
>>

>>using System;
>>using System.Data;
>>using System.Data.OleDb;
>>
>>class test
>>{
>> static void Main()
>> {
>>  string conStr1 = @"Provider=VFPOLEDB;
>>     Data Source=C:\Program Files\Microsoft Visual FoxPro 8\Samples\Northwind\NorthWind.dbc";
>>  string conStr2 = @"Provider=SQLOLEDB;
>>     Integrated Security=SSPI;Initial Catalog=Northwind;Data Source=(local)";
>>  string sSQL =
>>  @"Select count(*)
>>    from customers
>>    where country = 'USA' and city in ('Elgin','Walla Walla')";
>>  ShowCount(conStr1,sSQL,"VFP");
>>  ShowCount(conStr2,sSQL,"MSSQL");
>> }
>>
>> private static void ShowCount(string conStr, string sql,string backend)
>> {
>>  OleDbConnection loConnection = new OleDbConnection(conStr);
>>  loConnection.Open();
>>  OleDbCommand cmd = new OleDbCommand(sql, loConnection);
>>  int iCount = Convert.ToInt32(cmd.ExecuteScalar());
>>  loConnection.Close();
>>  Console.WriteLine("Backend is {0}. Count is {1}",backend,iCount);
>> }
>>}

>>Cetin
>>
>>
>>>Cetin,
>>>Thank you for the reply. Please see my commends inserted in your reply.
>>>
>>>>1) Your SQL sounds to have an extra semicolon at the end.
>>>
>>>The extra semicolon does not make a difference. That is simply the semicolon terminator for the SQL statement. (I did notice that I forgot the start " when I assigned the sting in my initial message, but this was correct in my project).
>>>
>>>>2) Count() returns a decimal data type and you need to convert it.
>>>
>>>Well that explains things. I thought it returned an int, but I assume for insanely large tables decimal is better <s>
>>>
>>>>3) For things like this instead of creating a DS use ExecuteScalar method of OleDbCommand.
>>>>ie:
>>>>

>>>>OleDbConnection loConnection = new OleDbConnection(conStr);
>>>>loConnection.Open();
>>>>OleDbCommand cmd = new OleDbCommand(sSQL, loConnection);
>>>>decimal iCount = (decimal)cmd.ExecuteScalar();
>>>>

Cetin
>>>
>>>Yeah I suppose there is less overhead by using ExecuteScalar() and not creating a dataset and filling it. I think I will update my code.
>>>
>>>I did notice that I can not use:
>>>

>>>(decimal)cmd.ExecuteScalar()  OR (decimal)loDS.Tables[0].Rows[0][0]
>>>

>>>
>>>I have to use:
>>>

>>>Convert.ToDecimal(cmd.ExecuteScalar())  OR Convert.ToDecimal(loDS.Tables[0].Rows[0][0])
>>>

>>>
>>>Any clue why I can't cast the value?
>>>
>>>Einar