Not able to call the procedure.
-
OdbcCommand cmd = new OdbcCommand();
try
{
// cn.Open();
cmd.Connection = cn;
cmd.CommandText = "use userdb";
cmd.ExecuteNonQuery();
cmd.CommandText = "DROP PROCEDURE IF EXISTS add_emp";
cmd.ExecuteNonQuery();cmd.CommandText = "CREATE TABLE emp (empno INT UNSIGNED NOT NULL AUTO\_INCREMENT PRIMARY KEY, first\_name VARCHAR(20), last\_name VARCHAR(20), birthdate DATE)"; cmd.ExecuteNonQuery(); cmd.CommandText = "CREATE PROCEDURE add\_emp(" + "IN fname VARCHAR(20), IN lname VARCHAR(20), IN bday DATETIME)" + "BEGIN INSERT INTO emp(first\_name, last\_name, birthdate) " + "VALUES(fname, lname, DATE(bday)); END"; cmd.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex.Message); } cn.Close(); Console.WriteLine("Connection closed."); try { Console.WriteLine("Connecting to MySQL..."); cn.Open(); cmd.Connection = cn; cmd.CommandText = "add\_emp;"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("?lname", "Jones"); cmd.Parameters\["?lname"\].Direction = ParameterDirection.Input; cmd.Parameters.AddWithValue("?fname", "Tom"); cmd.Parameters\["?fname"\].Direction = ParameterDirection.Input; cmd.Parameters.AddWithValue("?bday", "1940-06-07"); cmd.Parameters\["?bday"\].Direction = ParameterDirection.Input; //cmd.Parameters.AddWithValue("@empno", MySqlDbType.Int32); //cmd.Parameters\["@empno"\].Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex.Message); } cn.Close(); Console.WriteLine("Done."); }By this code procedure is created successfully bt when it comes to calling that procedure it throws an error
System.Data.Odbc.OdbcException: ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.0.77-community-nt]You have an
If I were you, I would try to initialize my
OdbcCommandobject instead of just changing the command text :OdbcCommand cmd;
string commandText;try
{
cn.Open();commandText = "use userdb";
cmd = new OdbcCommand(commandText, cn);
cmd.ExecuteNonQuery();commandText = "DROP PROCEDURE IF EXISTS add_emp";
cmd = new OdbcCommand(commandText, cn);
cmd.ExecuteNonQuery();commandText = "CREATE TABLE emp (empno INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(20), last_name VARCHAR(20), birthdate DATE)";
cmd = new OdbcCommand(commandText, cn);
cmd.ExecuteNonQuery();commandText = "CREATE PROCEDURE add_emp(" +
"IN fname VARCHAR(20), IN lname VARCHAR(20), IN bday DATETIME) " +
"BEGIN INSERT INTO emp(first_name, last_name, birthdate) " +
"VALUES(fname, lname, DATE(bday)); END";
cmd = new OdbcCommand(commandText, cn);
cmd.ExecuteNonQuery();
}... and so on in your second try block. Everytime I tried to reuse a command object to do anything else than its original goal, I ran into troubles. Initiliaze it with
newand see what happens.No memory stick has been harmed during establishment of this signature.
-
OdbcCommand cmd = new OdbcCommand();
try
{
// cn.Open();
cmd.Connection = cn;
cmd.CommandText = "use userdb";
cmd.ExecuteNonQuery();
cmd.CommandText = "DROP PROCEDURE IF EXISTS add_emp";
cmd.ExecuteNonQuery();cmd.CommandText = "CREATE TABLE emp (empno INT UNSIGNED NOT NULL AUTO\_INCREMENT PRIMARY KEY, first\_name VARCHAR(20), last\_name VARCHAR(20), birthdate DATE)"; cmd.ExecuteNonQuery(); cmd.CommandText = "CREATE PROCEDURE add\_emp(" + "IN fname VARCHAR(20), IN lname VARCHAR(20), IN bday DATETIME)" + "BEGIN INSERT INTO emp(first\_name, last\_name, birthdate) " + "VALUES(fname, lname, DATE(bday)); END"; cmd.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex.Message); } cn.Close(); Console.WriteLine("Connection closed."); try { Console.WriteLine("Connecting to MySQL..."); cn.Open(); cmd.Connection = cn; cmd.CommandText = "add\_emp;"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("?lname", "Jones"); cmd.Parameters\["?lname"\].Direction = ParameterDirection.Input; cmd.Parameters.AddWithValue("?fname", "Tom"); cmd.Parameters\["?fname"\].Direction = ParameterDirection.Input; cmd.Parameters.AddWithValue("?bday", "1940-06-07"); cmd.Parameters\["?bday"\].Direction = ParameterDirection.Input; //cmd.Parameters.AddWithValue("@empno", MySqlDbType.Int32); //cmd.Parameters\["@empno"\].Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex.Message); } cn.Close(); Console.WriteLine("Done."); }By this code procedure is created successfully bt when it comes to calling that procedure it throws an error
System.Data.Odbc.OdbcException: ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.0.77-community-nt]You have an
nitish_07 wrote:
when it comes to calling that procedure it throws an error
you have shown the SP and the exception, that is fine; yet you didn't show the calling code, where the problem could well be... :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
nitish_07 wrote:
when it comes to calling that procedure it throws an error
you have shown the SP and the exception, that is fine; yet you didn't show the calling code, where the problem could well be... :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
nitish_07 wrote:
it throws an error
which suggests it is wrong, so show the code. :wtf:
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
nitish_07 wrote:
it throws an error
which suggests it is wrong, so show the code. :wtf:
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
If I were you, I would try to initialize my
OdbcCommandobject instead of just changing the command text :OdbcCommand cmd;
string commandText;try
{
cn.Open();commandText = "use userdb";
cmd = new OdbcCommand(commandText, cn);
cmd.ExecuteNonQuery();commandText = "DROP PROCEDURE IF EXISTS add_emp";
cmd = new OdbcCommand(commandText, cn);
cmd.ExecuteNonQuery();commandText = "CREATE TABLE emp (empno INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(20), last_name VARCHAR(20), birthdate DATE)";
cmd = new OdbcCommand(commandText, cn);
cmd.ExecuteNonQuery();commandText = "CREATE PROCEDURE add_emp(" +
"IN fname VARCHAR(20), IN lname VARCHAR(20), IN bday DATETIME) " +
"BEGIN INSERT INTO emp(first_name, last_name, birthdate) " +
"VALUES(fname, lname, DATE(bday)); END";
cmd = new OdbcCommand(commandText, cn);
cmd.ExecuteNonQuery();
}... and so on in your second try block. Everytime I tried to reuse a command object to do anything else than its original goal, I ran into troubles. Initiliaze it with
newand see what happens.No memory stick has been harmed during establishment of this signature.
-
sorry I looked over it. your parameters have identifiers staring with ?, your SQL doesn't use '?' at all. I doubt that is correct. BTW: always look at (and show us) the full exception (i.e. exception.ToString), not just the one-line message. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
OdbcCommand cmd = new OdbcCommand();
try
{
// cn.Open();
cmd.Connection = cn;
cmd.CommandText = "use userdb";
cmd.ExecuteNonQuery();
cmd.CommandText = "DROP PROCEDURE IF EXISTS add_emp";
cmd.ExecuteNonQuery();cmd.CommandText = "CREATE TABLE emp (empno INT UNSIGNED NOT NULL AUTO\_INCREMENT PRIMARY KEY, first\_name VARCHAR(20), last\_name VARCHAR(20), birthdate DATE)"; cmd.ExecuteNonQuery(); cmd.CommandText = "CREATE PROCEDURE add\_emp(" + "IN fname VARCHAR(20), IN lname VARCHAR(20), IN bday DATETIME)" + "BEGIN INSERT INTO emp(first\_name, last\_name, birthdate) " + "VALUES(fname, lname, DATE(bday)); END"; cmd.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex.Message); } cn.Close(); Console.WriteLine("Connection closed."); try { Console.WriteLine("Connecting to MySQL..."); cn.Open(); cmd.Connection = cn; cmd.CommandText = "add\_emp;"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("?lname", "Jones"); cmd.Parameters\["?lname"\].Direction = ParameterDirection.Input; cmd.Parameters.AddWithValue("?fname", "Tom"); cmd.Parameters\["?fname"\].Direction = ParameterDirection.Input; cmd.Parameters.AddWithValue("?bday", "1940-06-07"); cmd.Parameters\["?bday"\].Direction = ParameterDirection.Input; //cmd.Parameters.AddWithValue("@empno", MySqlDbType.Int32); //cmd.Parameters\["@empno"\].Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex.Message); } cn.Close(); Console.WriteLine("Done."); }By this code procedure is created successfully bt when it comes to calling that procedure it throws an error
System.Data.Odbc.OdbcException: ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.0.77-community-nt]You have an
nitish_07 wrote:
cmd.CommandText = "add_emp;";
I do not think this is right. Should it not be
nitish_07 wrote:
cmd.CommandText = "add_emp";
Extra semicolon.
Why is common sense not common? Never argue with an idiot. They will drag you down to their level where they are an expert. Sometimes it takes a lot of work to be lazy Please stand in front of my pistol, smile and wait for the flash - JSOP 2012
-
nitish_07 wrote:
cmd.CommandText = "add_emp;";
I do not think this is right. Should it not be
nitish_07 wrote:
cmd.CommandText = "add_emp";
Extra semicolon.
Why is common sense not common? Never argue with an idiot. They will drag you down to their level where they are an expert. Sometimes it takes a lot of work to be lazy Please stand in front of my pistol, smile and wait for the flash - JSOP 2012
-
OdbcCommand cmd = new OdbcCommand();
try
{
// cn.Open();
cmd.Connection = cn;
cmd.CommandText = "use userdb";
cmd.ExecuteNonQuery();
cmd.CommandText = "DROP PROCEDURE IF EXISTS add_emp";
cmd.ExecuteNonQuery();cmd.CommandText = "CREATE TABLE emp (empno INT UNSIGNED NOT NULL AUTO\_INCREMENT PRIMARY KEY, first\_name VARCHAR(20), last\_name VARCHAR(20), birthdate DATE)"; cmd.ExecuteNonQuery(); cmd.CommandText = "CREATE PROCEDURE add\_emp(" + "IN fname VARCHAR(20), IN lname VARCHAR(20), IN bday DATETIME)" + "BEGIN INSERT INTO emp(first\_name, last\_name, birthdate) " + "VALUES(fname, lname, DATE(bday)); END"; cmd.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex.Message); } cn.Close(); Console.WriteLine("Connection closed."); try { Console.WriteLine("Connecting to MySQL..."); cn.Open(); cmd.Connection = cn; cmd.CommandText = "add\_emp;"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("?lname", "Jones"); cmd.Parameters\["?lname"\].Direction = ParameterDirection.Input; cmd.Parameters.AddWithValue("?fname", "Tom"); cmd.Parameters\["?fname"\].Direction = ParameterDirection.Input; cmd.Parameters.AddWithValue("?bday", "1940-06-07"); cmd.Parameters\["?bday"\].Direction = ParameterDirection.Input; //cmd.Parameters.AddWithValue("@empno", MySqlDbType.Int32); //cmd.Parameters\["@empno"\].Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex.Message); } cn.Close(); Console.WriteLine("Done."); }By this code procedure is created successfully bt when it comes to calling that procedure it throws an error
System.Data.Odbc.OdbcException: ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.0.77-community-nt]You have an
Sooo, overall, your creating a table to hold employee data, which is usually a permanent table in a database, and creating the stored procedure to add an employee to this table, AND calling the procedure to add an employee to this table, ALL IN ONE METHOD?? Your database should be created at install-time, not runtime. Your code should only be calling the stored procedure. I have to ask why you're doing it this way??
A guide to posting questions on CodeProject[^]
Dave Kreskowiak -
Sooo, overall, your creating a table to hold employee data, which is usually a permanent table in a database, and creating the stored procedure to add an employee to this table, AND calling the procedure to add an employee to this table, ALL IN ONE METHOD?? Your database should be created at install-time, not runtime. Your code should only be calling the stored procedure. I have to ask why you're doing it this way??
A guide to posting questions on CodeProject[^]
Dave KreskowiakActually the thing is I am testing it...I am developing mysql query browser. so when i am calling sp at run time then also i m getting same error...so i have searched on net c# code for calling sp..and i have got this code but when i implemented this then it throws the same error.....
-
OdbcCommand cmd = new OdbcCommand();
try
{
// cn.Open();
cmd.Connection = cn;
cmd.CommandText = "use userdb";
cmd.ExecuteNonQuery();
cmd.CommandText = "DROP PROCEDURE IF EXISTS add_emp";
cmd.ExecuteNonQuery();cmd.CommandText = "CREATE TABLE emp (empno INT UNSIGNED NOT NULL AUTO\_INCREMENT PRIMARY KEY, first\_name VARCHAR(20), last\_name VARCHAR(20), birthdate DATE)"; cmd.ExecuteNonQuery(); cmd.CommandText = "CREATE PROCEDURE add\_emp(" + "IN fname VARCHAR(20), IN lname VARCHAR(20), IN bday DATETIME)" + "BEGIN INSERT INTO emp(first\_name, last\_name, birthdate) " + "VALUES(fname, lname, DATE(bday)); END"; cmd.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex.Message); } cn.Close(); Console.WriteLine("Connection closed."); try { Console.WriteLine("Connecting to MySQL..."); cn.Open(); cmd.Connection = cn; cmd.CommandText = "add\_emp;"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("?lname", "Jones"); cmd.Parameters\["?lname"\].Direction = ParameterDirection.Input; cmd.Parameters.AddWithValue("?fname", "Tom"); cmd.Parameters\["?fname"\].Direction = ParameterDirection.Input; cmd.Parameters.AddWithValue("?bday", "1940-06-07"); cmd.Parameters\["?bday"\].Direction = ParameterDirection.Input; //cmd.Parameters.AddWithValue("@empno", MySqlDbType.Int32); //cmd.Parameters\["@empno"\].Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex.Message); } cn.Close(); Console.WriteLine("Done."); }By this code procedure is created successfully bt when it comes to calling that procedure it throws an error
System.Data.Odbc.OdbcException: ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.0.77-community-nt]You have an
Some small changes:
cmd.CommandText = "add_emp(@fname, @lname, @bday)";
Then use the parameters prefixed with
@instead of?:cmd.Parameters.AddWithValue("@lname", "Jones");
-
Some small changes:
cmd.CommandText = "add_emp(@fname, @lname, @bday)";
Then use the parameters prefixed with
@instead of?:cmd.Parameters.AddWithValue("@lname", "Jones");
ya I have done this....bt nothing happened...same error shows.... ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.0.77-community-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 'add_emp(@fname, @lname, @bday)' at line 1
-
OdbcCommand cmd = new OdbcCommand();
try
{
// cn.Open();
cmd.Connection = cn;
cmd.CommandText = "use userdb";
cmd.ExecuteNonQuery();
cmd.CommandText = "DROP PROCEDURE IF EXISTS add_emp";
cmd.ExecuteNonQuery();cmd.CommandText = "CREATE TABLE emp (empno INT UNSIGNED NOT NULL AUTO\_INCREMENT PRIMARY KEY, first\_name VARCHAR(20), last\_name VARCHAR(20), birthdate DATE)"; cmd.ExecuteNonQuery(); cmd.CommandText = "CREATE PROCEDURE add\_emp(" + "IN fname VARCHAR(20), IN lname VARCHAR(20), IN bday DATETIME)" + "BEGIN INSERT INTO emp(first\_name, last\_name, birthdate) " + "VALUES(fname, lname, DATE(bday)); END"; cmd.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex.Message); } cn.Close(); Console.WriteLine("Connection closed."); try { Console.WriteLine("Connecting to MySQL..."); cn.Open(); cmd.Connection = cn; cmd.CommandText = "add\_emp;"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("?lname", "Jones"); cmd.Parameters\["?lname"\].Direction = ParameterDirection.Input; cmd.Parameters.AddWithValue("?fname", "Tom"); cmd.Parameters\["?fname"\].Direction = ParameterDirection.Input; cmd.Parameters.AddWithValue("?bday", "1940-06-07"); cmd.Parameters\["?bday"\].Direction = ParameterDirection.Input; //cmd.Parameters.AddWithValue("@empno", MySqlDbType.Int32); //cmd.Parameters\["@empno"\].Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex.Message); } cn.Close(); Console.WriteLine("Done."); }By this code procedure is created successfully bt when it comes to calling that procedure it throws an error
System.Data.Odbc.OdbcException: ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.0.77-community-nt]You have an
I looked again at your code, and now I'm wondering maybe something is wrong about the bday type (DATETIME,DATE) and the DATE function; so I'd recommend you leave it out till you get the SP working, then add it back in and fix it if necessary. Note 1: I also saw your first Con.Open() was commented... Note 2: and you do check the SP for existence before creating one, but not the table itself. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
I looked again at your code, and now I'm wondering maybe something is wrong about the bday type (DATETIME,DATE) and the DATE function; so I'd recommend you leave it out till you get the SP working, then add it back in and fix it if necessary. Note 1: I also saw your first Con.Open() was commented... Note 2: and you do check the SP for existence before creating one, but not the table itself. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
OdbcCommand cmd = new OdbcCommand();
try
{
// cn.Open();
cmd.Connection = cn;
cmd.CommandText = "use userdb";
cmd.ExecuteNonQuery();
cmd.CommandText = "DROP PROCEDURE IF EXISTS add_emp";
cmd.ExecuteNonQuery();cmd.CommandText = "CREATE TABLE emp (empno INT UNSIGNED NOT NULL AUTO\_INCREMENT PRIMARY KEY, first\_name VARCHAR(20), last\_name VARCHAR(20), birthdate DATE)"; cmd.ExecuteNonQuery(); cmd.CommandText = "CREATE PROCEDURE add\_emp(" + "IN fname VARCHAR(20), IN lname VARCHAR(20), IN bday DATETIME)" + "BEGIN INSERT INTO emp(first\_name, last\_name, birthdate) " + "VALUES(fname, lname, DATE(bday)); END"; cmd.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex.Message); } cn.Close(); Console.WriteLine("Connection closed."); try { Console.WriteLine("Connecting to MySQL..."); cn.Open(); cmd.Connection = cn; cmd.CommandText = "add\_emp;"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("?lname", "Jones"); cmd.Parameters\["?lname"\].Direction = ParameterDirection.Input; cmd.Parameters.AddWithValue("?fname", "Tom"); cmd.Parameters\["?fname"\].Direction = ParameterDirection.Input; cmd.Parameters.AddWithValue("?bday", "1940-06-07"); cmd.Parameters\["?bday"\].Direction = ParameterDirection.Input; //cmd.Parameters.AddWithValue("@empno", MySqlDbType.Int32); //cmd.Parameters\["@empno"\].Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex.Message); } cn.Close(); Console.WriteLine("Done."); }By this code procedure is created successfully bt when it comes to calling that procedure it throws an error
System.Data.Odbc.OdbcException: ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.0.77-community-nt]You have an
i think as keyword missing. First exexcute the block in sqlserver then try into fron tend. But best practice is using sp