HELP! - Repost - Stored Procedures and .NET
Posted by: Jan Franken
Date: February 14, 2005 09:11AM

I have ported a Microsoft SQL database with stored procedures to MySQL (ver 5 alpha - the only one supporting stored procedures!!!!!). Using the MySQL command line interface, the stored procedures work perfectly, returning datasets etc., e.g.( Call CMMF_FileCategoryList(); )

I am running Visual Studio ASP.NET and VB.NET with the MySQL Connector 1.0.4 downloaded from the MySQL site.

If I program the select statement from the stored procedure directly into the CommandText parameter of the MySQLCommand object, the DataReader executes without errors.

However, if I use the stored procedure name in the CommandText, and set the CommandType to Stored Procedure, the DataReader exits with an error to the effect that
*******
Server Error in '/IntraNet' Application.
--------------------------------------------------------------------------------

Index was outside the bounds of the array.
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.IndexOutOfRangeException: Index was outside the bounds of the array.

Source Error:


Line 70: myConnection.Open()
Line 71:
Line 72: Dim result As MySqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
Line 73:
Line 74: ' Return the datareader result

Source File: c:\inetpub\wwwroot\IntraNet\Components\FileDB.vb Line: 72

Stack Trace:


[IndexOutOfRangeException: Index was outside the bounds of the array.]
MySql.Data.MySqlClient.StoredProcedure.Prepare(String spName)

[MySqlException: Exception trying to retrieve parameter info for CMMF_FileCategoryList: Index was outside the bounds of the array.]
MySql.Data.MySqlClient.StoredProcedure.Prepare(String spName)
MySql.Data.MySqlClient.MySqlCommand.PrepareSqlBuffers(String sql)
MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
IntraNet.ASPNET.ChironLubricants.FilingSystem.FilesDB.GetFileCategories() in c:\inetpub\wwwroot\IntraNet\Components\FileDB.vb:72
IntraNet.C_Menu.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\IntraNet\_Menu.ascx.vb:44
System.Web.UI.Control.OnLoad(EventArgs e)
System.Web.UI.Control.LoadRecursive()
System.Web.UI.Control.LoadRecursive()
System.Web.UI.Control.LoadRecursive()
System.Web.UI.Page.ProcessRequestMain()

--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.2032; ASP.NET Version:1.1.4322.2032

*********

The VB function follows:


Public Function GetFileCategories() As MySqlDataReader
' Create Instance of Connection and Command Object
Dim myConnection As New MySqlConnection()
Dim connectstr As String = (ConfigurationSettings.AppSettings("ConnectionString"))
myConnection.ConnectionString = connectstr
Dim myCommand As New MySqlCommand()
' set connection
myCommand.Connection = myConnection
' set stored procedure to execute
myCommand.CommandText = "CMMF_FileCategoryList"
' - THIS WORKS -> myCommand.CommandText = "SELECT FileCategoryID as CategoryID, FileCategoryName as CategoryName FROM CMMF_FileCategories ORDER BY CategoryName ASC"
' Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure

' Execute the command
myConnection.Open()

Dim result As MySqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

' Return the datareader result
Return result

End Function

***

This is the stored procedure

CREATE Procedure CMMF_FileCategoryList
( )

BEGIN

SELECT
FileCategoryID as CategoryID,
FileCategoryName as CategoryName

FROM
CMMF_FileCategories

ORDER BY
CategoryName ASC;
END;

Options: ReplyQuote


Subject
Written By
Posted
HELP! - Repost - Stored Procedures and .NET
February 14, 2005 09:11AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.