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,OjbDataID int,ObjDataValue 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
                    {
                         if (!Data_2.IsNull)
                        {
                            string sql = @"Select Obj_ID, Obj_Data_ID, Obj_Data_Value from tbl_Obj_2";
                            SqlCommand cmd = new SqlCommand(sql, conn);
                            SqlDataAdapter da = new SqlDataAdapter(cmd);
                            DataTable dt = new DataTable();
                            da.Fill(dt);
                            conn.Open();
                            return dt.Rows;
                        }
                         if (!Data_3.IsNull)
                        {
                            string sql = @"Select Obj_ID, Obj_Data_ID, Obj_Data_Value from tbl_Obj_3";
                            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();
                    }
                    finally
                    {
                        conn.Close();
                    }

                }

        }
        public static void Obj_Row(Object item, out int ObjID, out int ObjDataID, out string ObjDataValue)
        {
            DataRow row = (DataRow)item;
            ObjID = Convert.ToInt32(row["Obj_ID"]);
            ObjDataID = Convert.ToInt32(row["Obj_Data_ID"]);
            ObjDataValue = row["Obj_Data_Value"].ToString();
        }

};

I'm newbie. Please, show me how to correct the problem. Thank you.

Hi there,

You can correct the problem as follows:

1. Go to www.google.com

2. Search for the term "not all code paths return a value"

3. Read the top 5 or 10 articles that Google gives you.

This will not only fix your problem, but also give you some insight in why it occurs, how to prevent it and how to fix it.

Hint: you are returning something if accessing the database succeeds. However, in case of an error (the try/catch block) nothing is returned which is something the compiler doesn't like.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

Thank you. I have work aground with this problem for 1 day.

the code below will be work find.

I thought I need to set variable to be value some where before "If" the problem is I don't know the syntax. :) I'm looking for the syntax . BTW thank you.



===================
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,OjbDataID int,ObjDataValue 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
                    {
                    // if (!Data_2.IsNull)
                    // {
                            string sql = @"Select Obj_ID, Obj_Data_ID, Obj_Data_Value from tbl_Obj_2";
                            SqlCommand cmd = new SqlCommand(sql, conn);
                            SqlDataAdapter da = new SqlDataAdapter(cmd);
                            DataTable dt = new DataTable();
                            da.Fill(dt);
                            conn.Open();
                            return dt.Rows;
                        //}
                        // if (!Data_3.IsNull)
                        //{
                        // string sql = @"Select Obj_ID, Obj_Data_ID, Obj_Data_Value from tbl_Obj_3";
                        // 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();
                    }
                    finally
                    {
                        conn.Close();
                    }

                }

        }
        public static void Obj_Row(Object item, out int ObjID, out int ObjDataID, out string ObjDataValue)
        {
            DataRow row = (DataRow)item;
            ObjID = Convert.ToInt32(row["Obj_ID"]);
            ObjDataID = Convert.ToInt32(row["Obj_Data_ID"]);
            ObjDataValue = row["Obj_Data_Value"].ToString();
        }

};

I have to use Else to make this work? It's look like some kind of.... not normal.

I solve this problem with "Else". by Imar. Thank you, Imar and by

Now I deeply understand the problem. It cost me straight 24 hours

=====================================
Hi,
that error means that this function has a way that when it is executed it won't return a value. Example of one simple case of this:

public int A(int b)
{
if(b == 1)
{
return 2;
}
else
{
}
}

This code would produce the same error as yours because there's, at least, one way that the function doesn't return a value. In this case try calling the function A with b different than 1, it won't return any value since it doesn't enter in the if code block.

In your case, if an error occurs in the try block it will jump to the catch block and there's no return there (or after it), so since an error ocurred and you don't have values to return, a return null; would be one possible solution to your problem. Other would be return new ArrayList();

==========================================

Exactly. Funny thing is, once you understand the problem, the error message is much clearer.... ;)

Hopefully, new problems won't take another 24 hours to solve. But I hope this exercise helped improve your problem solving skills so the 24 hours is time well spent....

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

Yub! when I understand the error message is much clearer ;-P

I got an other problem.

Hope this time do not cost me 24 hours to get out.

When I use nested select look like the system don't like it.

I don't know what wrong. SQL scrip work fine in SQL studio.
================================
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();
        }

};

//Error 1 System.InvalidCastException: Unable to cast object of type 'System.Char' to type 'System.Data.DataRow'.

well.. which line throw the error??

Usually the enviroment advice you the line it has the problem..

the error is in the return "".. that function only return Ienumerable collections.. you have to return something like nothing (or is null is C#, or void??)

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

Hi Gonzalo,

compile not error at all.

return "" just for not thing even with return null, it not happen at all, just because some one want me put it in there so ... I put it in so they know the problem not from that point.

error return when I call it from SQL studio query.

this is full error message from SQL server:

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& ObjDataID, String& ObjDataValue).


When I change SQL scrip to simple just select some thing 3 fields from any tables with 2 fields are Int, and one field nvarchar, well it's work right.

So I thought the problem maybe some where in the "NESTED SELECT" or sql server 2005 problem.

I already check the NESTED SELECT sql script ALONE IN SQL STUDIO It's work fine.
-------------------

I don't get it.. the code works?

but it don't work when???

did you test your sql?? if that doesn't work, there is a problem with it...

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

Simple SQL it works. Nested SQL 1 level down work, Nested SQL 3 level down little complex like the sample I just post did not work.

Yes I did test SQL script, all SQL work good.

Over 24 hours I still get stuck at this point.
----------------------------------------------

If I use Table value function in SQL server SQL work good. I just want convert it to dll to easy maintains

----------------------------------------------

With SQL 2005 I got an other problem is: SQL 2005 auto convert my variable from Nvarchar type to varchar type when my variable travel from: store procedure <==> function, over 3 times.