I'm trying to retrieve information from joined tables in the way
suggested in the docs about dealing with 1+N selects. I keep getting
the following error:
=======================================================================================
{"Object type cannot be converted to target type." }
[System.ArgumentException]: {"Object type cannot be converted to
target type." }
System.Object: {System.ArgumentException}
_className: null
_COMPlusExceptionCode: -532459699
_exceptionMethod: <undefined value>
_exceptionMethodString: null
_helpURL: null
_HResult: -2147024809
_innerException: { }
_message: "Object type cannot be converted to target type."
_remoteStackIndex: 0
_remoteStackTraceString: null
_source: null
_stackTrace: {System.Array}
_stackTraceString: null
_xcode: -532459699
_xptrs: 0
HelpLink: null
HResult: -2147024809
InnerException: { }
Message: "Object type cannot be converted to target type."
Source: "mscorlib"
StackTrace: " at
System.Reflection.RuntimeMethodInfo.InternalInvoke(Object obj,
BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo
culture, Boolean isBinderDefault, Assembly caller, Boolean
verifyAccess)\r\n at
System.Reflection.RuntimeMethodInfo.InternalInvoke(Object obj,
BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo
culture, Boolean verifyAccess)\r\n at
System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags
invokeAttr, Binder binder, Object[] parameters, CultureInfo
culture)\r\n at System.Reflection.RuntimePropertyInfo.SetValue(Object
obj, Object value, Object[] index)\r\n at
IBatisNet.DataMapper.MappedStatements.MappedStatement.SetValueOfProperty(Object&
target, ResultProperty property, Object dataBaseValue)\r\n at
IBatisNet.DataMapper.MappedStatements.MappedStatement.SetObjectProperty(ResultMap
resultMap, ResultProperty mapping, Object& target, IDataReader
reader)\r\n at IBatisNet.DataMapper.MappedStatements.MappedStatement.App
lyResultMap(RequestScope request, IDataReader reader, Object
resultObject)\r\n at
IBatisNet.DataMapper.MappedStatements.MappedStatement.RunQueryForList(RequestScope
request, IDalSession session, Object parameterObject, Int32 skipResults,
Int32 maxResults, RowDelegate rowDelegate)\r\n at
IBatisNet.DataMapper.MappedStatements.MappedStatement.ExecuteQueryForList(IDalSession
session, Object parameterObject, Int32 skipResults, Int32
maxResults)\r\n at
IBatisNet.DataMapper.MappedStatements.MappedStatement.ExecuteQueryForList(IDalSession
session, Object parameterObject)\r\n at
IBatisNet.DataMapper.SqlMapper.QueryForList(String statementName, Object
parameterObject)\r\n at
CandidateManager.Persistence.MapperDao.BaseSqlMapDao.ExecuteQueryForList(String
statementName, Object parameterObject) in
c:\\inetpub\\wwwroot\\dur_candidatemanager_dev2\\candidatemanager.persistence\\mapperdao\\basesqlmapdao.cs:line
48"
TargetSite: {System.Reflection.RuntimeMethodInfo}
=======================================================================================
This is my map file:
=======================================================================================
<?xml version="1.0" encoding="utf-8" ?>
<sqlMap namespace="Category"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
xsi:noNamespaceSchemaLocation="SqlMap.xsd">
<resultMaps>
<resultMap id="KeywordResult" class="Keyword">
<result property="Id" column="Keyword_Id"/>
<result property="Name" column="Keyword_Name"/>
<result property="Description" column="Keyword_Description"/>
<result property="Selected" column="Keyword_Selected"/>
</resultMap>
<resultMap id="CategoryList" class="Category">
<result property="Id" column="Category_Id"/>
<result property="Name" column="Category_Name"/>
<result property="Description"
column="Category_Description"/>
<result property="Keywords"
resultMapping="Category.KeywordResult" />
</resultMap>
</resultMaps>
<!--
=============================================
MAPPED STATEMENTS
=============================================
-->
<statements>
<select id="GetCategorizeKeywords" resultMap="CategoryList"
parameterClass="string">
<![CDATA[
SELECT
*, CAST(ISNULL(ck.CandidateID, 0) AS Bit) as
Keyword_Selected
FROM
tblKeywords k
LEFT JOIN
tblCandidateKeywords ck
ON ck.KeywordID = k.Keyword_Id
AND ck.CandidateID = #value#
INNER JOIN
tblCategories c
ON k.Keyword_CategoryID = c.Category_ID
ORDER BY
c.Category_Name, k.Keyword_Id
]]>
</select>
</statements>
</sqlMap>
=======================================================================================
And lastly, here are the object interfaces:
=======================================================================================
public interface ICategory
{
int Id { get; set; }
string Name { get; set; }
string Description { get; set; }
IList Keywords { get; set; }
}
public interface IKeyword
{
int Id { get; set; }
string Name { get; set; }
string Description { get; set; }
bool Selected { get; set; }
Category Category { get; set; }
}
=======================================================================================
When I run the query in Query Analyzer, replacing #value# with valid
data, I get records:
=======================================================================================
Keyword_Id Keyword_Name Keyword_Description Keyword_CategoryID
CandidateID KeywordID Category_Id Category_Name Category_Description
Keyword_Selected
---------------------------------------------------------------------------------------------------------------------------
143 Level 1 NULL 5 NULL NULL 5 Apprentice
Registered Apprentice? 0
144 Level 2 Includes: Level 1 5 NULL NULL 5
Apprentice Registered Apprentice? 0
145 Level 3 Includes: Level 2, Level 1 5 NULL NULL
5 Apprentice Registered Apprentice? 0
179 IRS NULL 12 NULL NULL 12 Assistance Eligible
for assistance? 0
=======================================================================================
Can anyone tell me why this wouldn't be working?
Thanks!
Brian
