Prev: Using layers won't return value from stored procedure
Next: System.Web.IHttpHandler, System.Web.SessionState.IRequiresSess
From: Alan T on 19 Jul 2010 23:57
Hi,
I am using MySQL 5 and installed MySQl ODBC driver.

I have a stored procedure, AddEmployee stored procedure:
DELIMITER $$
CREATE
PROCEDURE `myCompany`.`AddEmployee`(EmployeeID VARCHAR(36),
EmployeeGroup VARCHAR(10), EmployeeName VARCHAR(50))
BEGIN
INSERT INTO EmployeeTable (ID, Group, Name) VALUES ( EmployeeID ,
EmployeeGroup , EmployeeName );
END$$
DELIMITER ;

I tried to call for testing in a MySQL management tools.
call AddEmployee('12345', 'Sales', 'Helen Smith')

It works to insert a new record into the table.


I got my method to insert a record to a table:

protected void ExecuteNonQuery()
{
OdbcConnection cnx = null;
cmd = null; //Avoids "Use of unassigned variable" compiler error
try
{
// GetConnectionString returns MySQL connection string
cnx = new OdbcConnection(GetConnectionString());
cnx.Open();
cmd = new OdbcCommand("AddEmployee");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = cnx;
cmd.Parameters.Add(new OdbcParameter("EmployeeID", "99999"));
cmd.Parameters.Add(new OdbcParameter("EmployeeGroup", "Sales"));
cmd.Parameters.Add(new OdbcParameter("EmployeeName", "John
Smith"));
cmd.ExecuteNonQuery();
}
catch
{
throw;
}
finally
{
cnx.Dispose();
}
if (cmd != null)
cmd.Dispose();
}
}

However, it throws an exception:
ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.31-community]
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 'AddEmployee' at
line 1

Any idea?

Thanks


From: Alan T on 20 Jul 2010 00:29
Hi,

I modified and get differnent error about it did not get my parameters:

cnx = new OdbcConnection(GetConnectionString());
cmd = new OdbcCommand("CALL AddEmployee");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = cnx;
cmd.Parameters.Add("EmployeeID", "99999"));
cmd.Parameters.Add("EmployeeGroup", "Sales"));
cmd.Parameters.Add("EmployeeName", "John Smith");
cnx.Open();
cmd.ExecuteNonQuery();


The error now is:
+ $exception {"ERROR [HY000] [MySQL][ODBC 5.1
Driver][mysqld-5.1.31-community]Incorrect number of arguments for PROCEDURE
myCompany.AddEmployee; expected 3, got 0"} System.Exception
{System.Data.Odbc.OdbcException}


 | 
Pages: 1
Prev: Using layers won't return value from stored procedure
Next: System.Web.IHttpHandler, System.Web.SessionState.IRequiresSess