Oscar,
Your procedure does not return anything. Instead it generates
a result-set. You probably want to look into "select into", declare
a variable and do select into variable with a return variable.
Martin
Oscar Claros wrote:
> I am new to using mysql. I like it so far, but I have been having
> issues when trying to use C#, odbc.net and mysql.
>
> Here is the stored procedure that I wrote in sqlyog:
>
> DELIMITER $$;
>
> DROP PROCEDURE IF EXISTS
> `webcustomers`.`P_CUSTOMER_GET_CUSTOMER_REC_BY_NAME`$$
>
> CREATE PROCEDURE `P_CUSTOMER_GET_CUSTOMER_REC_BY_NAME`(IN cLastName
> varchar(50))
> BEGIN
> SELECT * FROM customer WHERE lastName = cLastName;
> END$$
>
> DELIMITER ;$$
>
> Here the first C# code snippet that does not work:
> public DataTable ExecuteDataTableTest()
> {
> string strLastName = "Simpson";
> string strSP = "{ ? = CALL
> P_CUSTOMER_GET_CUSTOMER_REC_BY_NAME (?)}";
> string strConn =
> ConfigurationManager.ConnectionStrings["MySQL"].ToString();
> DataTable dt = new DataTable();
> OdbcConnection myOdbcConn = new OdbcConnection(strConn);
> OdbcDataAdapter myOdbcDa = new OdbcDataAdapter();
>
> try
> {
> myOdbcDa = new OdbcDataAdapter(strSP, myOdbcConn);
> myOdbcDa.SelectCommand.Parameters.Add("@cLastName",
> OdbcType.VarChar, 50);
> myOdbcDa.SelectCommand.Parameters[0].Value = strLastName;
> myOdbcDa.SelectCommand.CommandType =
> CommandType.StoredProcedure;
>
> myOdbcDa.Fill(dt);
> }
> catch (Exception ex)
> {
> CreateLog(ex.Message);
> }
> finally
> {
> myOdbcDa.Dispose();
> myOdbcConn.Close();
> myOdbcConn.Dispose();
> }
>
> return dt;
>
> }
>
> Here is the error code that I get:
> ERROR [07002] [MySQL][ODBC 3.51
> Driver][mysqld-5.0.18-nt]SQLBindParameter not used for all parameters
>
> So I tried again, but I could not figure out what the type of
> "RETURN_VALUE" was. I did some searching on the web and it looks like
> the type is an int.
>
> public DataTable ExecuteDataTableTest()
> {
> string strLastName = "Simpson";
> string strSP = "{ ? = CALL
> P_CUSTOMER_GET_CUSTOMER_REC_BY_NAME (?)}";
> string strConn =
> ConfigurationManager.ConnectionStrings["MySQL"].ToString();
>
> DataTable dt = new DataTable();
> OdbcConnection myOdbcConn = new OdbcConnection(strConn);
> OdbcDataAdapter myOdbcDa = new OdbcDataAdapter();
>
> try
> {
> myOdbcDa = new OdbcDataAdapter(strSP, myOdbcConn);
> myOdbcDa.SelectCommand.Parameters.Add("RETURN_TYPE",
> OdbcType.Int, 2);
> myOdbcDa.SelectCommand.Parameters.Add("@cLastName",
> OdbcType.VarChar, 50);
> myOdbcDa.SelectCommand.Parameters[1].Value = strLastName;
> myOdbcDa.SelectCommand.CommandType =
> CommandType.StoredProcedure;
>
> myOdbcDa.Fill(dt);
> }
> catch (Exception ex)
> {
> CreateLog(ex.Message);
> }
> finally
> {
> myOdbcDa.Dispose();
> myOdbcConn.Close();
> myOdbcConn.Dispose();
> }
>
> return dt;
>
> }
>
> Here is the error that was return:
>
> ERROR [23000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.18-nt]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 '{ 0 = CALL
> P_CUSTOMER_GET_CUSTOMER_REC_BY_NAME ('Simpson')}' at line 1
>
>
> Here is the work around that I did:
>
> public DataTable ExecuteDataTable()
> {
> string strLastName = "Simpson";
> string strSQL = "SELECT * FROM customer WHERE lastName =" +
> strLastName;
> string strConn =
> ConfigurationManager.ConnectionStrings["MySQL"].ToString();
> DataTable dt = new DataTable();
> OdbcConnection myOdbcConn = new OdbcConnection(strConn);
> OdbcDataAdapter myOdbcDa = new OdbcDataAdapter();
>
> try
> {
> myOdbcConn.Open();
> try
> {
>
> myOdbcDa = new OdbcDataAdapter(strSQL, myOdbcConn);
> myOdbcDa.Fill(dt);
> }
> catch (Exception ex)
> {
> CreateLog(ex.Message);
> }
> finally
> {
> myOdbcDa.Dispose();
> }
> }
>
> catch (OdbcException odbcEx)
> {
> CreateLog(odbcEx.Message);
>
> }
> finally
> {
> myOdbcConn.Close();
> myOdbcConn.Dispose();
> }
> return dt;
> }
> public Customer[] GetACustomer()
> {
> DALHelper myDALHelper = new DALHelper();
> DataTable dtCur = new DataTable();
> dtCur = myDALHelper.ExecuteDataTable(strSQL);
>
> if (dtCur != null)
> {
> if (dtCur.Rows.Count != 0)
> {
> Customer[] cust = new Customer[dtCur.Rows.Count];
> for (int i = 0; i <= dtCur.Rows.Count - 1; i++)
> {
> {
> cust[i] = new Customer();
> cust[i] =
> ConvertDbDataToCustomerObject(dtCur.Rows[i]);
> }
> }
> return cust;
> }
> else
> {
> return null;
> }
> }
> else
> {
> return null;
> }
> }
>
> _________________________________________________________________
> Is your PC infected? Get a FREE online computer virus scan from McAfee®
> Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
>
>
