Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • World
  • Users
  • Groups
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor
  • Default (No Skin)
  • No Skin
Collapse
Code Project
  1. Home
  2. General Programming
  3. Windows Forms
  4. Not able to call the procedure.

Not able to call the procedure.

Scheduled Pinned Locked Moved Windows Forms
mysqlhelpquestion
17 Posts 7 Posters 0 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • P phil o

    If I were you, I would try to initialize my OdbcCommand object 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 new and see what happens.

    No memory stick has been harmed during establishment of this signature.

    N Offline
    N Offline
    nitish_07
    wrote on last edited by
    #7

    I have done it this way but nothing happened...same error still exist...

    1 Reply Last reply
    0
    • N nitish_07

      I have shown the whole code in my first post.....What else do u want??

      L Offline
      L Offline
      Luc Pattyn
      wrote on last edited by
      #8

      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

      1 Reply Last reply
      0
      • N nitish_07

        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

        L Offline
        L Offline
        Lost User
        wrote on last edited by
        #9

        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

        N 1 Reply Last reply
        0
        • L Lost User

          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

          N Offline
          N Offline
          nitish_07
          wrote on last edited by
          #10

          I have corrected it but it didnt solve my problem..

          1 Reply Last reply
          0
          • N nitish_07

            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

            D Offline
            D Offline
            Dave Kreskowiak
            wrote on last edited by
            #11

            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

            N 1 Reply Last reply
            0
            • D 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 Kreskowiak

              N Offline
              N Offline
              nitish_07
              wrote on last edited by
              #12

              Actually 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.....

              1 Reply Last reply
              0
              • N nitish_07

                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

                B Offline
                B Offline
                Bernhard Hiller
                wrote on last edited by
                #13

                Some small changes:

                cmd.CommandText = "add_emp(@fname, @lname, @bday)";

                Then use the parameters prefixed with @ instead of ?:

                cmd.Parameters.AddWithValue("@lname", "Jones");

                N 1 Reply Last reply
                0
                • B Bernhard Hiller

                  Some small changes:

                  cmd.CommandText = "add_emp(@fname, @lname, @bday)";

                  Then use the parameters prefixed with @ instead of ?:

                  cmd.Parameters.AddWithValue("@lname", "Jones");

                  N Offline
                  N Offline
                  nitish_07
                  wrote on last edited by
                  #14

                  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

                  1 Reply Last reply
                  0
                  • N nitish_07

                    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

                    L Offline
                    L Offline
                    Luc Pattyn
                    wrote on last edited by
                    #15

                    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

                    N 1 Reply Last reply
                    0
                    • L Luc Pattyn

                      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

                      N Offline
                      N Offline
                      nitish_07
                      wrote on last edited by
                      #16

                      I have deleted the bday option but again same issue.....and con.open() is not an issue.... and i have checked sp for its existence....no issue....

                      1 Reply Last reply
                      0
                      • N nitish_07

                        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

                        K Offline
                        K Offline
                        karthiDebug
                        wrote on last edited by
                        #17

                        i think as keyword missing. First exexcute the block in sqlserver then try into fron tend. But best practice is using sp

                        1 Reply Last reply
                        0
                        Reply
                        • Reply as topic
                        Log in to reply
                        • Oldest to Newest
                        • Newest to Oldest
                        • Most Votes

                        • Login
                        • Don't have an account? Register
                        • Login or register to search.
                        • First post
                          Last post
                        0
                        • Categories
                        • Recent
                        • Tags
                        • Popular
                        • World
                        • Users
                        • Groups