Unable to cast object of type
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(FillRowMeth odName="Obj_Row",
IsDeterministic=true,
IsPrecise=true,
TableDefinition = "ObjID int,ObjCategoryID int,ObjValue nvarchar(400)",
DataAccess= DataAccessKind.Read)]
public static IEnumerable Obj_IDs(SqlInt32 Data_1, SqlInt32 Data_2, SqlInt32 Data_3)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
try
{
string sql;
switch(Data_1.Value)
{
case 1:
sql= @"Select Obj_ID, Obj_Categoy_ID, Obj_Value
from (select ObjA_ID as Obj_ID, Obj_Category_ID, Obj_Value from dbo.function_A(12,23))A,
(select ObjA_ID as Obj_ID, Obj_Category_ID, Obj_Value from dbo.function_A(15,27))B
Where A.ObjA_ID= B.ObjA_ID
and Obj_ID in (Select ObjB_ID from tbl_Object where Obj_CategoryID=10)";
break;
default:
sql = @"Select Obj_ID, Obj_Categoy_ID, Obj_Value from tbl_Object";
break;
}
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
conn.Open();
return dt.Rows;
}
catch (Exception ex)
{
ex.Message.ToString();
return "";
}
finally
{
conn.Close();
}
}
}
public static void Obj_Row(Object item, out int ObjID, out int ObjCategoryID, out string ObjValue)
{
DataRow row = (DataRow)item;
ObjID = Convert.ToInt32(row["Obj_ID"]);
ObjCategoryID = Convert.ToInt32(row["Obj_Categoy_ID"]);
ObjValue = row["Obj_Value"].ToString();
}
};
=================
compile with no error. call function in SQL studio the error come
Msg 6260, Level 16, State 1, Line 1
An error occurred while getting new row from user defined Table Valued Function :
System.InvalidCastException: Unable to cast object of type 'System.Char' to type 'System.Data.DataRow'.
System.InvalidCastException:
at UserDefinedFunctions.Object_Row(Object item, Int32& ObjID, Int32& ObjCategoryID, String& ObjValue)
.
when I change SQL script to simple not "nested select" it work fine. How to get out of this kind of trouble????!!!!
|