Access Query OleDbParameter problem

S

Steve

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
 
S

Scott M.

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 said:
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
Click to expand...
 
S

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. said:
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 said:
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
Click to expand...
Click to expand...
 
Top