Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 7th, 2008, 02:58 AM
Authorized User
 
Join Date: Jan 2008
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default not all code paths return a value

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.

 
Old January 7th, 2008, 03:13 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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.
 
Old January 7th, 2008, 03:29 AM
Authorized User
 
Join Date: Jan 2008
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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();
        }

};

 
Old January 7th, 2008, 05:48 AM
Authorized User
 
Join Date: Jan 2008
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Quote:
quote: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.
and by
Quote:
quote:
http://forums.microsoft.com/MSDN/Sho...84061&SiteID=1

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.

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();

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

 
Old January 7th, 2008, 06:01 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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.
 
Old January 7th, 2008, 06:18 PM
Authorized User
 
Join Date: Jan 2008
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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'.
 
Old January 7th, 2008, 10:52 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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
================================================== =========
 
Old January 8th, 2008, 12:04 AM
Authorized User
 
Join Date: Jan 2008
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
-------------------

 
Old January 8th, 2008, 07:57 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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
================================================== =========
 
Old January 8th, 2008, 11:18 AM
Authorized User
 
Join Date: Jan 2008
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
v urgent..Error: not all code paths return a value prv299 .NET Web Services 1 May 20th, 2008 06:17 AM
not all code paths return a value c# awaisfaisal C# 5 May 10th, 2007 08:02 AM
Hard coded paths pauliehaha Classic ASP Professional 8 February 6th, 2007 06:48 PM
paths table FT BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 1 December 12th, 2006 11:50 AM
Code to Remove Carriage Return From Spreadsheet Jersey Eric Excel VBA 2 May 12th, 2005 08:35 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.