Thread: Data is Null. This method or property cannot be called on Null values.

hi guys, i'm trying to get a list of results from my stored procedure (MySQL)

and i'm getting this error:

Code:

 Data is Null. This method or property cannot be called on Null values.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.

Source Error:

Line 44:                 myConnection.Open()
Line 45:                 ' Using
Line 46:                 Dim myReader As MySqlDataReader = myCommand.ExecuteReader
Line 47:                 Try
Line 48:                     If myReader.HasRows Then


Source File: c:\inetpub\wwwroot\keithdesign2\App_Code\global\DAL\ProjectsDB.vb    Line: 46

Stack Trace:

[SqlNullValueException: Data is Null. This method or property cannot be called on Null values.]
   MySql.Data.MySqlClient.MySqlDataReader.GetFieldValue(Int32 index, Boolean checkNull) +384
   MySql.Data.MySqlClient.MySqlDataReader.GetString(Int32 column) +38
   MySql.Data.MySqlClient.ISSchemaProvider.GetParametersFromShowCreate(DataTable parametersTable, String[] restrictions, DataTable routines) +354

[InvalidOperationException: Unable to retrieve stored procedure metadata.  Either grant  SELECTprivilege to mysql.proc for this user or use "use procedure bodies=false" with  your connection string.]
   MySql.Data.MySqlClient.ISSchemaProvider.GetParametersFromShowCreate(DataTable parametersTable, String[] restrictions, DataTable routines) +413
   MySql.Data.MySqlClient.ISSchemaProvider.GetProcedureParameters(String[] restrictions, DataTable routines) +502
   MySql.Data.MySqlClient.ProcedureCache.GetProcData(MySqlConnection connection, String spName) +227
   MySql.Data.MySqlClient.ProcedureCache.AddNew(MySqlConnection connection, String spName) +35
   MySql.Data.MySqlClient.ProcedureCache.GetProcedure(MySqlConnection conn, String spName) +102
   MySql.Data.MySqlClient.StoredProcedure.GetParameters(String procName) +98
   MySql.Data.MySqlClient.StoredProcedure.Resolve() +148
   MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) +545
   MySql.Data.MySqlClient.MySqlCommand.ExecuteReader() +6
   Keith.ProjectsManager.Dal.ProjectsDB.GetList() in c:\inetpub\wwwroot\keithdesign2\App_Code\global\DAL\ProjectsDB.vb:46
   Keith.ProjectsManager.Bll.ProjectsManager.GetList() in c:\inetpub\wwwroot\keithdesign2\App_Code\global\BLL\ProjectsManager.vb:13

[TargetInvocationException: Exception has been thrown by the target of an invocation.]
   System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +0
   System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +72
   System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) +308
   System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +29
   System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method, Boolean disposeInstance, Object& instance) +480
   System.Web.UI.WebControls.ObjectDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1960
   System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
   System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
   System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
   System.Web.UI.WebControls.GridView.DataBind() +4
   System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
   System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
   System.Web.UI.Control.EnsureChildControls() +87
   System.Web.UI.Control.PreRenderRecursiveInternal() +50
   System.Web.UI.Control.PreRenderRecursiveInternal() +170
   System.Web.UI.Control.PreRenderRecursiveInternal() +170
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041

here is my function, line 46 in red:

Code:

Public Shared Function GetList() As ProjectsList
            Dim tempList As ProjectsList = Nothing
            ' Using
            Dim myConnection As MySqlConnection = New MySqlConnection(myConfig.myConnection)
            Try
                Dim myCommand As MySqlCommand = New MySqlCommand("sprocProjectsSelectList", myConnection)
                myCommand.CommandType = CommandType.StoredProcedure
                myConnection.Open()
                ' Using
                Dim myReader As MySqlDataReader = myCommand.ExecuteReader
                Try
                    If myReader.HasRows Then
                        tempList = New ProjectsList
                        While myReader.Read
                            tempList.Add(FillDataRecord(myReader))
                        End While
                    End If
                    myReader.Close()
                Finally
                    CType(myReader, IDisposable).Dispose()
                End Try
            Finally
                CType(myConnection, IDisposable).Dispose()
            End Try
            Return tempList
        End Function

I have tested my SP in QueryBrowser and it works?
Any ideas?

Shem

if I run the function using a straight query and not a SP, then it runs?

Originally Posted by jmurrayhead

Is that a question or statement?

Statement

Originally Posted by Wolffy

The second exception in the stack track may be the root cause of your error. It appears that you have a rights problem running the stored procedure. This would explain why it works as a query and not as a stored procedure.

the user has all permissions granted already, will google it though

GOT IT..

had to add "use procedure bodies=false" to my connection string

thanks for the help guys
Shem