I'm creating my class for mysql in which i have difficulty dealing with the parameters of which to use with prefix '@param' or '?param' or whatever it is.
I need your help guys.


I have here my stored procedure

DELIMITER $$

DROP PROCEDURE IF EXISTS `davidssalon`.`Customer_SearchName`$$
CREATE DEFINER=`root`@`%` PROCEDURE  `davidssalon`.`Customer_SearchName`( IN _LASTNAME VARCHAR(63), IN _FIRSTNAME VARCHAR(63))
BEGIN
	SELECT LASTNAME, FIRSTNAME, ADDRESS, DATECREATED
	FROM customer
	WHERE (LASTNAME LIKE _LASTNAME) AND (FIRSTNAME LIKE _FIRSTNAME)
	ORDER BY LASTNAME;
END $$

DELIMITER ;

Here's code

public override bool Connect()
        {
            // Close any opened connections
            if (Connection.State == System.Data.ConnectionState.Open)
                Connection.Close();

            try
            {
                Connection.Open();
                return true;
            }
            catch (System.Exception)
            {
                return false;
            }
        }

        public override System.Data.DataTable QueryStoredProcedure(string StoredProcedure)
        {
            Command = new MySql.Data.MySqlClient.MySqlCommand(StoredProcedure, Connection);
            if (!Connect())
                throw new System.Exception("Could not connect to database.");

            Command.CommandType = System.Data.CommandType.StoredProcedure;
           
            DataReader = Command.ExecuteReader();

            return Fetch();
        }

        public override void AddParameter(string name, string value)
        {
            Command.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter(name, MySql.Data.MySqlClient.MySqlDbType.String));
            Command.Parameters[name].Value = value;
        }

This is where I execute my procedure

public System.Data.DataTable SearchName(string lastname, string firstname)
        {
            if (string.IsNullOrEmpty(lastname.Trim()))
                lastname = lastname.Trim() + "%";

            if (string.IsNullOrEmpty(firstname.Trim()))
                firstname = firstname.Trim() + "%";

            // Add parameter
            dbConn.ClearParameter();
            dbConn.AddParameter("_LASTNAME", lastname);
            dbConn.AddParameter("_FIRSTNAME", firstname);

            // Query
            return dbConn.QueryStoredProcedure("Customer_SearchName");
        }

I got an error in "DataReader = Command.ExecuteReader();"
which is "Parameter '@_LASTNAME' not found in the collection."

Did you test this procedure against MySQL database, and works fine?

Yes, i've directly add the procedure on MYSQL Query Browser and it was OK and also on Server Explorer -> Data Connection of visual studio.

If there's an error on my code, more likely it won't save my procedure at all. Means, my code is correct. But when i use TableAdapter Configuration Wizard, and load my stored procedure, it will automatically close. Maybe it can't interpret MYSQL stored procedure?
I have experience this many times.

Most likely you should ask this question in MySQL Forumas they are who developed this component they can help you well.

This article has been dead for over six months. Start a new discussion instead.