View Single Post
  #1 (permalink)  
Old January 7th, 2008, 07:47 PM
galua galua is offline
Authorized User
 
Join Date: Jan 2008
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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????!!!!