OLEDB Provider Eror

[Zachary Burns]

I am getting the following error with my code:

Code:
System.Data.DataSet ds = new DataSet();
//string dbConnectionString = @"Provider=VFPOLEDB.1;Data
Source=I:\MidasPfe;Mode=Share Deny None";
string dbConnectionString = @"Provider=VFPOLEDB;Data
Source=I:\MidasPfe;Mode=Share Deny None";
System.Data.OleDb.OleDbConnection dbConnection;
System.Data.OleDb.OleDbDataAdapter dbAdapter;

dbConnection = new
System.Data.OleDb.OleDbConnection(dbConnectionString);
dbConnection.Open();
dbAdapter = new System.Data.OleDb.OleDbDataAdapter("SELECT
payhist.clock as clock, payhist.shopno as shop, ALLTRIM(fname)+'
'+ALLTRIM(lname) as name, sum(payhist.all_sales) as sales,
sum(payhist.hours) as hours, (sum(payhist.all_sales)/sum(payhist.hours)) as
eph FROM payhist where
weekend>={"+DateTime.Now.AddDays(-7).ToShortDateString()+"} AND hours>0 AND
left(jobcode,1)<>'M' AND left(jobcode,1)<>'C' AND left(jobcode,2)<>'12' AND
left(jobcode,2)<>'AS' AND shopno<>'120' GROUP BY clock, shopno, name ORDER
BY eph DESC",dbConnection);
dbAdapter.Fill(ds,"Top25Technicians");
dbAdapter.Dispose();
dbAdapter=null;
dbConnection.Close();
dbConnection.Dispose();
return ds;



Error:

An unhandled exception of type 'System.InvalidOperationException' occurred
in system.data.dll

Additional information: The provider could not determine the Decimal value.
For example, the row was just created, the default for the Decimal column
was not available, and the consumer had not yet set a new Decimal value.





I'm using the VFPOLEDB (8.0a) of the driver and my foxpro app is working
correctly, just not when using C#. Any ideas?

Zack

 

[W.G. Ryan eMVP]

A few things...

first, It'd help to know which line is causing the problem. You have a few
things that you may want to change. First, you don't need to open and close
your connections yourself in this instance, ultimately I'd let the adapter
handle it. For now though, I'd leave it as is just for debugging. Wrap it
in an try catch block and the exception's details. Next, for those values
in the where clause, if they are dynamic I'd recommend using
OleDbParameters. Assuming that the connection is good then it's something
w/ the query in all likelihood. Have you verified the actual string? What
does the output of it look like?