MySQL / ASP.NET Stored Procedures

Question

Hopefully this is not a ServerFault question...

I'm working forward on migrating a project from storing data in XML Serialization to a MySQL database. I'm using the example provided me from a previous question answered yesterday.

Connecting using phpMyAdmin and MySQL Workbench I've created a Stored Procedure called 'sprocOrderSelectSingleItem'. It seems to work well with MySQL for all I can tell. When I run the SHOW CREATE PROCEDURE sprocOrderSelectSingleItem it returns the following:

CREATE DEFINER=username@% PROCEDURE sprocOrderSelectSingleItem(IN orderID INTEGER) BEGIN SELECT * FROM tblOrders WHERE ID=orderID; END

My cooperative ASP.NET code goes something like this:

public static Order GetItem(int ID)
{
    Order objOrder = null;

    using (OdbcConnection objConnection = new OdbcConnection(Utils.ApplicationConfiguration.ConnectionString))
    {
        OdbcCommand objCommand = new OdbcCommand("sprocOrderSelectSingleItem", objConnection);
        objCommand.CommandType = CommandType.StoredProcedure;
        objCommand.Parameters.AddWithValue("orderID", ID);
        objConnection.Open();

        using (OdbcDataReader objReader = objCommand.ExecuteReader())
        {
            if (objReader.Read())
            {
                objOrder = FillDataRecord(objReader);
            }

            objReader.Close();
        }

        objConnection.Close();
    }

    return objOrder;
}

When I view the page I get the following error message:

ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.0.77]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sprocOrderSelectSingleItem' at line 1

Really not catching on to what could be missing or going wrong. Are there any additional tests I should/could be running to confirm things are working on the MySQL side? Am I missing a step to pass the Stored Procedure call correctly in ASP.NET? The code breaks at the line of:

using (OdbcDataReader objReader = objCommand.ExecuteReader())

Replacing the line of

OdbcCommand objCommand = new OdbcCommand("sprocOrderSelectSingleItem", objConnection);

with this instead

OdbcCommand objCommand = new OdbcCommand("SELECT * FROM tblOrders WHERE ID=" + ID + ";", objConnection);

and everything works as expected.

Thanks for any help you guys can provide.

Answer 1

Can you try something like below:

OdbcCommand cmd = new OdbcCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "{call LoadCustCliOrders(?,?,?,?)}";

    cmd.Parameters.Add("CUST_ID",OdbcType.Int);
    cmd.Parameters.Add("CLIENT_ID",OdbcType.Int);
    cmd.Parameters.Add("DATE_FROM",OdbcType.Date);
    cmd.Parameters.Add("DATE_TO",OdbcType.Date);

...

    cmd.Parameters["CUST_ID"].Value = _CustId;
    cmd.Parameters["CLIENT_ID"].Value =  _ClientId;
    cmd.Parameters["DATE_FROM"].Value = _DateFrom;
    cmd.Parameters["DATE_TO"].Value = _DateTo;

    cmd.ExecuteReader 

Answer 2

Your can run an execute on sprocOrderSelectSingleItem in Mysql directly with the ID parameter. It will show that your StoredProc run correctly.

Here is a sample code in C# that call a stored proc.

OdbcCommand salesCMD = new OdbcCommand("{ CALL SalesByCategory(?) }", nwindConn);
salesCMD.CommandType = CommandType.StoredProcedure;

OdbcParameter myParm = salesCMD.Parameters.Add("@CategoryName", OdbcType.VarChar, 15);
myParm.Value = "Beverages";

OdbcDataReader myReader = salesCMD.ExecuteReader();

Look at the "Call" in the OdbcCommand and the "?" for the parameter that is later supplied with a value.

Answer 3

Are you sure that you are using the same username or user with the same access privileges. I think you need to add the word "CALL" before the stored proc. It should be CALL sprocOrderSelectSingleItem and try.