I have an update routine that fills a OleDbCommand.Parameters collection
with 16 values. The last one is the criteria for the update. This worked
fine until I added the 14th and 15th parameters and now I get an exception:
"Data type mismatch in criteria expression."

There is no additional descriptions in the exception object. I have checked
the code, checked the order of parameters(Because Access seems to lame to
handle them in any other order than the order of the query) and I don't see
the problem.

Here is the Access Query Sql:

<code>
UPDATE Tbl_CustomerConfigs
SET Tbl_CustomerConfigs.ConfigName = [_configName],
Tbl_CustomerConfigs.Description = [_description],
Tbl_CustomerConfigs.IsDemo = [_isDemo],
Tbl_CustomerConfigs.BaseFrequency = [_baseFrequency],
Tbl_CustomerConfigs.QuartetLCDTopLine = [_quartetTopLine],
Tbl_CustomerConfigs.QuartetLCDBottomLine = [_quartetBottomLine],
Tbl_CustomerConfigs.DuetPlusLCDTopLine = [_duetPlusTopLine],
Tbl_CustomerConfigs.DuetPlusLCDBottomLine = [_duetPlusBottomLine],
Tbl_CustomerConfigs.DuetLCDTopLine = [_duetTopLine],
Tbl_CustomerConfigs.DuetLCDBottomLine = [_duetBottomLine],
Tbl_CustomerConfigs.LCDScrollSpeed = [_scrollSpeed],
Tbl_CustomerConfigs.LCDScrollInitHold = [_initHold],
Tbl_CustomerConfigs.LCDLoopCount = [_loopCount],
Tbl_CustomerConfigs.DateCreated = [_dateCreated],
Tbl_CustomerConfigs.DateModified = [_dateModified]
WHERE (((Tbl_CustomerConfigs.ConfigurationID)=[_configID]));
</code>


And here is my ADO DAL code:

<DAL code>
m_command.CommandText = GetAccessSprocString(m_spUpdateCustConfig);
m_command.Parameters.Clear();

OleDbParameter param = new OleDbParameter("_configName", config.Name);
m_command.Parameters.Add(param);

param = new OleDbParameter("_description", config.Description);
m_command.Parameters.Add(param);

param = new OleDbParameter("_isDemo", config.IsDemo);
m_command.Parameters.Add(param);

param = new OleDbParameter("_baseFrequency", config.BaseFrequency);
m_command.Parameters.Add(param);

param = new OleDbParameter("_quartetTopLine", config.QuartetLCDTopLine);
m_command.Parameters.Add(param);

param = new OleDbParameter("_quartetBottomLine",
config.QuartetLCDBottomLine);
m_command.Parameters.Add(param);

param = new OleDbParameter("_duetPlusTopLine", config.DuetPlusLCDTopLine);
m_command.Parameters.Add(param);

param = new OleDbParameter("_duetPlusBottomLine",
config.DuetPlusLCDBottomLine);
m_command.Parameters.Add(param);

param = new OleDbParameter("_duetTopLine", config.DuetLCDTopLine);
m_command.Parameters.Add(param);

param = new OleDbParameter("_duetPlusBottomLine", config.DuetLCDBottomLine);
m_command.Parameters.Add(param);

param = new OleDbParameter("_scrollSpeed", config.LCDScrollSpeed);
m_command.Parameters.Add(param);

param = new OleDbParameter("_initHold", config.LCDScrollInitHold);
m_command.Parameters.Add(param);

param = new OleDbParameter("_loopCount", config.LCDLoopCount);
m_command.Parameters.Add(param);

param = new OleDbParameter("_dateCreated", config.DateCreated);
m_command.Parameters.Add(param);

param = new OleDbParameter("_dateModified", config.DateModified);
m_command.Parameters.Add(param);

param = new OleDbParameter("_configID", config.ConfigID);
m_command.Parameters.Add(param);
</DAL code>



Does anyone see the problem? I sure don't. Hopefully someone can clue me
in.

Thanks for taking a look,
Steve

Well, it looks like the 14th and 15th parameters are dates, yet you haven't
set them up as such. You should set the dataTpe property of the parameter
(actually, you should be doing this on all your parameters) and make sure
that the actual data being passed to that parameter matches the data type
specified.

BTW: Yes, you are right, an Access DB must have the parameters added to the
OleDBCommand's Parameters collection in the exact same sequence as they are
specified in the SQL statement.


"Steve" <(E-Mail Removed)> wrote in message
news:O8bdvQ$(E-Mail Removed)...
>I have an update routine that fills a OleDbCommand.Parameters collection
> with 16 values. The last one is the criteria for the update. This worked
> fine until I added the 14th and 15th parameters and now I get an
> exception:
> "Data type mismatch in criteria expression."
>
> There is no additional descriptions in the exception object. I have
> checked
> the code, checked the order of parameters(Because Access seems to lame to
> handle them in any other order than the order of the query) and I don't
> see
> the problem.
>
> Here is the Access Query Sql:
>
> <code>
> UPDATE Tbl_CustomerConfigs
> SET Tbl_CustomerConfigs.ConfigName = [_configName],
> Tbl_CustomerConfigs.Description = [_description],
> Tbl_CustomerConfigs.IsDemo = [_isDemo],
> Tbl_CustomerConfigs.BaseFrequency = [_baseFrequency],
> Tbl_CustomerConfigs.QuartetLCDTopLine = [_quartetTopLine],
> Tbl_CustomerConfigs.QuartetLCDBottomLine = [_quartetBottomLine],
> Tbl_CustomerConfigs.DuetPlusLCDTopLine = [_duetPlusTopLine],
> Tbl_CustomerConfigs.DuetPlusLCDBottomLine = [_duetPlusBottomLine],
> Tbl_CustomerConfigs.DuetLCDTopLine = [_duetTopLine],
> Tbl_CustomerConfigs.DuetLCDBottomLine = [_duetBottomLine],
> Tbl_CustomerConfigs.LCDScrollSpeed = [_scrollSpeed],
> Tbl_CustomerConfigs.LCDScrollInitHold = [_initHold],
> Tbl_CustomerConfigs.LCDLoopCount = [_loopCount],
> Tbl_CustomerConfigs.DateCreated = [_dateCreated],
> Tbl_CustomerConfigs.DateModified = [_dateModified]
> WHERE (((Tbl_CustomerConfigs.ConfigurationID)=[_configID]));
> </code>
>
>
> And here is my ADO DAL code:
>
> <DAL code>
> m_command.CommandText = GetAccessSprocString(m_spUpdateCustConfig);
> m_command.Parameters.Clear();
>
> OleDbParameter param = new OleDbParameter("_configName", config.Name);
> m_command.Parameters.Add(param);
>
> param = new OleDbParameter("_description", config.Description);
> m_command.Parameters.Add(param);
>
> param = new OleDbParameter("_isDemo", config.IsDemo);
> m_command.Parameters.Add(param);
>
> param = new OleDbParameter("_baseFrequency", config.BaseFrequency);
> m_command.Parameters.Add(param);
>
> param = new OleDbParameter("_quartetTopLine", config.QuartetLCDTopLine);
> m_command.Parameters.Add(param);
>
> param = new OleDbParameter("_quartetBottomLine",
> config.QuartetLCDBottomLine);
> m_command.Parameters.Add(param);
>
> param = new OleDbParameter("_duetPlusTopLine", config.DuetPlusLCDTopLine);
> m_command.Parameters.Add(param);
>
> param = new OleDbParameter("_duetPlusBottomLine",
> config.DuetPlusLCDBottomLine);
> m_command.Parameters.Add(param);
>
> param = new OleDbParameter("_duetTopLine", config.DuetLCDTopLine);
> m_command.Parameters.Add(param);
>
> param = new OleDbParameter("_duetPlusBottomLine",
> config.DuetLCDBottomLine);
> m_command.Parameters.Add(param);
>
> param = new OleDbParameter("_scrollSpeed", config.LCDScrollSpeed);
> m_command.Parameters.Add(param);
>
> param = new OleDbParameter("_initHold", config.LCDScrollInitHold);
> m_command.Parameters.Add(param);
>
> param = new OleDbParameter("_loopCount", config.LCDLoopCount);
> m_command.Parameters.Add(param);
>
> param = new OleDbParameter("_dateCreated", config.DateCreated);
> m_command.Parameters.Add(param);
>
> param = new OleDbParameter("_dateModified", config.DateModified);
> m_command.Parameters.Add(param);
>
> param = new OleDbParameter("_configID", config.ConfigID);
> m_command.Parameters.Add(param);
> </DAL code>
>
>
>
> Does anyone see the problem? I sure don't. Hopefully someone can clue me
> in.
>
> Thanks for taking a look,
> Steve
>
>

Hi Scott, thanks for the reply.
I sort of stumbled across the relaxed Parameter construction lacking type
specifier, size, etc. I kept using them because things seemed to magically
resolved to the correct type.
However, when I tried to set the DBType to a date type, I quickly found some
problems, namely WHICH date/time type was correct for .NET DateTime class
and then there is the question of the size parameter... I wasn't sure what
it was.

So in my messing around, I tried passing DateTime.ToString() instead of just
the DateTime and it solved the problem. It looks like passing just the
DateTime object would call ToString() with a formatter that would only send
the date. When I explicitly called ToString() it passed the full date and
time string.

You are right though, I should be specifying my types and sizes. I will be
jumping to SqlServer soon, so I will change that for my SQL DAL
implementation

Thanks again for the help,
Steve


"Scott M." <s-(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Well, it looks like the 14th and 15th parameters are dates, yet you
haven't
> set them up as such. You should set the dataTpe property of the parameter
> (actually, you should be doing this on all your parameters) and make sure
> that the actual data being passed to that parameter matches the data type
> specified.
>
> BTW: Yes, you are right, an Access DB must have the parameters added to
the
> OleDBCommand's Parameters collection in the exact same sequence as they
are
> specified in the SQL statement.
>
>
> "Steve" <(E-Mail Removed)> wrote in message
> news:O8bdvQ$(E-Mail Removed)...
> >I have an update routine that fills a OleDbCommand.Parameters collection
> > with 16 values. The last one is the criteria for the update. This
worked
> > fine until I added the 14th and 15th parameters and now I get an
> > exception:
> > "Data type mismatch in criteria expression."
> >
> > There is no additional descriptions in the exception object. I have
> > checked
> > the code, checked the order of parameters(Because Access seems to lame
to
> > handle them in any other order than the order of the query) and I don't
> > see
> > the problem.
> >
> > Here is the Access Query Sql:
> >
> > <code>
> > UPDATE Tbl_CustomerConfigs
> > SET Tbl_CustomerConfigs.ConfigName = [_configName],
> > Tbl_CustomerConfigs.Description = [_description],
> > Tbl_CustomerConfigs.IsDemo = [_isDemo],
> > Tbl_CustomerConfigs.BaseFrequency = [_baseFrequency],
> > Tbl_CustomerConfigs.QuartetLCDTopLine = [_quartetTopLine],
> > Tbl_CustomerConfigs.QuartetLCDBottomLine = [_quartetBottomLine],
> > Tbl_CustomerConfigs.DuetPlusLCDTopLine = [_duetPlusTopLine],
> > Tbl_CustomerConfigs.DuetPlusLCDBottomLine = [_duetPlusBottomLine],
> > Tbl_CustomerConfigs.DuetLCDTopLine = [_duetTopLine],
> > Tbl_CustomerConfigs.DuetLCDBottomLine = [_duetBottomLine],
> > Tbl_CustomerConfigs.LCDScrollSpeed = [_scrollSpeed],
> > Tbl_CustomerConfigs.LCDScrollInitHold = [_initHold],
> > Tbl_CustomerConfigs.LCDLoopCount = [_loopCount],
> > Tbl_CustomerConfigs.DateCreated = [_dateCreated],
> > Tbl_CustomerConfigs.DateModified = [_dateModified]
> > WHERE (((Tbl_CustomerConfigs.ConfigurationID)=[_configID]));
> > </code>
> >
> >
> > And here is my ADO DAL code:
> >
> > <DAL code>
> > m_command.CommandText = GetAccessSprocString(m_spUpdateCustConfig);
> > m_command.Parameters.Clear();
> >
> > OleDbParameter param = new OleDbParameter("_configName", config.Name);
> > m_command.Parameters.Add(param);
> >
> > param = new OleDbParameter("_description", config.Description);
> > m_command.Parameters.Add(param);
> >
> > param = new OleDbParameter("_isDemo", config.IsDemo);
> > m_command.Parameters.Add(param);
> >
> > param = new OleDbParameter("_baseFrequency", config.BaseFrequency);
> > m_command.Parameters.Add(param);
> >
> > param = new OleDbParameter("_quartetTopLine", config.QuartetLCDTopLine);
> > m_command.Parameters.Add(param);
> >
> > param = new OleDbParameter("_quartetBottomLine",
> > config.QuartetLCDBottomLine);
> > m_command.Parameters.Add(param);
> >
> > param = new OleDbParameter("_duetPlusTopLine",
config.DuetPlusLCDTopLine);
> > m_command.Parameters.Add(param);
> >
> > param = new OleDbParameter("_duetPlusBottomLine",
> > config.DuetPlusLCDBottomLine);
> > m_command.Parameters.Add(param);
> >
> > param = new OleDbParameter("_duetTopLine", config.DuetLCDTopLine);
> > m_command.Parameters.Add(param);
> >
> > param = new OleDbParameter("_duetPlusBottomLine",
> > config.DuetLCDBottomLine);
> > m_command.Parameters.Add(param);
> >
> > param = new OleDbParameter("_scrollSpeed", config.LCDScrollSpeed);
> > m_command.Parameters.Add(param);
> >
> > param = new OleDbParameter("_initHold", config.LCDScrollInitHold);
> > m_command.Parameters.Add(param);
> >
> > param = new OleDbParameter("_loopCount", config.LCDLoopCount);
> > m_command.Parameters.Add(param);
> >
> > param = new OleDbParameter("_dateCreated", config.DateCreated);
> > m_command.Parameters.Add(param);
> >
> > param = new OleDbParameter("_dateModified", config.DateModified);
> > m_command.Parameters.Add(param);
> >
> > param = new OleDbParameter("_configID", config.ConfigID);
> > m_command.Parameters.Add(param);
> > </DAL code>
> >
> >
> >
> > Does anyone see the problem? I sure don't. Hopefully someone can clue
me
> > in.
> >
> > Thanks for taking a look,
> > Steve
> >
> >
>
>