Crash while select query via OleDB with parameters to Access DB

Hi all.

I have a problem with using of OleDB query with parameters.
There is an application with two working databases - MS SQL and Access
We change all queries to DB by using parameters instead of creating
query by code.
The application work correctly with MS SQL, but often throws an
exception "Data type mismatch in criteria expression."

The query is

Select TOP 5 ROOTNODENAME AS DESCRIPTION, COUNT(*) AS REPORTCOUNTER,
SUM(EVENTCOUNT) AS EVENTCOUNT1, SUM(SUMDURATION) / SUM(EVENTCOUNT) /
1000 AS AVERAGEDURATION, MAX(MAXID) AS MAXEVENTID
From (
SELECT B.ROOTNODENAME, B.USERNAME, COUNT(*) AS EVENTCOUNT,
SUM(EVENTDURATION) AS SUMDURATION, MAX(EVENTID) AS MAXID
FROM V_EVENT B
WHERE EVENTCLASSTYPE='performance' AND UTCEVENTDATE>=? AND
UTCEVENTDATE<=? AND
(( SOURCE IN (
SELECT SRC.SOURCE
FROM APPLICATIONSOURCE APP1, APPLICATION APP2, SOURCE SRC
WHERE SRC.SOURCEID=APP1.SOURCEID AND APP1.APPLICATIONID =
APP2.APPLICATIONID
AND (APP2.APPLICATION IN (?,?)))))
and ((SOURCE IN (?,?)))
GROUP BY ROOTNODENAME, USERNAME)
A
Group By ROOTNODENAME Order By 2 DESC

i.e. contains 6 parameters - 2 as data and 4 as string
the code is

String connectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=test3.mdb";
OleDbConnection myConnection = new OleDbConnection(
connectionString );
OleDbCommand myCommand = new OleDbCommand( select,
myConnection );
OleDbParameter[] myParameter = new OleDbParameter[6];
OleDbDataAdapter da = new OleDbDataAdapter( myCommand );
DataSet ds = new DataSet();

myParameter[0] = new OleDbParameter( "@d1", new DateTime(
2006, 2, 1 ));
myParameter[0].OleDbType = OleDbType.Date;
myParameter[1] = new OleDbParameter( "@d2", DateTime.Now );
myParameter[1].OleDbType = OleDbType.Date;
myParameter[2] = new OleDbParameter( "@A1", "ALL" );
myParameter[2].OleDbType = OleDbType.VarChar;
myParameter[3] = new OleDbParameter( "@A2", "TEST" );
myParameter[3].OleDbType = OleDbType.VarChar;
myParameter[4] = new OleDbParameter( "@S1", "BlaBla" );
myParameter[4].OleDbType = OleDbType.VarChar;
myParameter[5] = new OleDbParameter( "@S2", "BlaBlaBla" );
myParameter[5].OleDbType = OleDbType.VarChar;

for( int index=0; index<100; index++ )
{
if( myParameter[index] != null )
myCommand.Parameters.Add( myParameter[index] );
}

myConnection.Open();
da.Fill( ds );
dgTest.DataSource = ds.Tables[0];

If I remove the Data parameters - all is worked OK.
If I remove the first two String parameters - all is worked OK too.
But it doesn't work together :(

Is somebody occures this trouble?
I will be happy to see any decision of this one...
Thanks

.

Relevant Pages

  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
  • Re: Populating a list -- table structure?
    ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... I have one report complete, ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
Loading