Call MySQL procedure with OUT parameter

Question

I want to get the id of the last inserted row from mysql, but i get somekind of Exception unhandled. The visual studio gives me this error :

MySql.Data.MySqlClient.MySqlException: 'OUT or INOUT argument 9 for routine test.pr_VendegFelvetele is not a variable or NEW pseudo-variable in BEFORE trigger'.

mysql code:

CREATE PROCEDURE pr_VendegFelvetele(
    IN uvezNev VARCHAR(75), 
    IN ukerNev VARCHAR(75), 
    IN uemail VARCHAR(255), 
    IN utel INT(15), 
    IN ucim VARCHAR(75), 
    IN uiranyito INT(5), 
    IN uvaros VARCHAR(55), 
    IN uorszag VARCHAR(45),
    OUT uvendegID INT(11)
)
BEGIN
INSERT INTO `vendeg`(`vezNev`, `kerNev`, `email`, `tel`, `cim`, `iranyito`, `varos`, `orszag`) 
VALUES (uvezNev, ukerNev, uemail, utel, ucim, uiranyito, uvaros, uorszag);
SET uvendegID = LAST_INSERT_ID();
END

By the way mysql procedure works perfectly.

c# code:

String query = "CALL pr_VendegFelvetele(@uvezNev, @ukerNev, @uemail, @utel, @ucim, @uiranyito, @uvaros, @uorszag, @uvendegID);";
                MySqlCommand cmd = new MySqlCommand(query, Con);

                cmd.Parameters.AddWithValue("@uvezNev", uvezNev);
                cmd.Parameters["@uvezNev"].Direction = ParameterDirection.Input;
                cmd.Parameters.AddWithValue("@ukerNev", ukerNev);
                cmd.Parameters["@ukerNev"].Direction = ParameterDirection.Input;
                cmd.Parameters.AddWithValue("@uemail", uemail);
                cmd.Parameters["@uemail"].Direction = ParameterDirection.Input;
                cmd.Parameters.AddWithValue("@utel", utel);
                cmd.Parameters["@utel"].Direction = ParameterDirection.Input;
                cmd.Parameters.AddWithValue("@ucim", ucim);
                cmd.Parameters["@ucim"].Direction = ParameterDirection.Input;
                cmd.Parameters.AddWithValue("@uiranyito", uiranyito);
                cmd.Parameters["@uiranyito"].Direction = ParameterDirection.Input;
                cmd.Parameters.AddWithValue("@uvaros", uvaros);
                cmd.Parameters["@uvaros"].Direction = ParameterDirection.Input;
                cmd.Parameters.AddWithValue("@uorszag", uorszag);
                cmd.Parameters["@uorszag"].Direction = ParameterDirection.Input;
                cmd.Parameters.AddWithValue("@uvendegID", MySqlDbType.Int32);
                cmd.Parameters["@uvendegID"].Direction = ParameterDirection.Output;

                Con.Open();

                cmd.ExecuteNonQuery();
                MessageBox.Show(cmd.Parameters["@uvendegID"].Value.ToString());

I get it on cmd.ExecuteNonQuery();

https://dev.mysql.com/doc/connector-net/en/connector-net-programming-stored-using.html , https://www.codeproject.com/Articles/36484/Working-C-code-for-MySql-Stored-Procedures-IN-OUT - see CommandType usage. — user2864740, Jul 18 '20 at 19:10
Thanks! I needed to add these lines, and now works like a charm. ``` cmd.CommandText = "pr_VendegFelvetele"; cmd.CommandType = CommandType.StoredProcedure; ``` — silverstorm, Jul 18 '20 at 20:03
Cool, consider adding it as an answer - including links and such! It’s okay to answer your own questions (especially if nobody else steps up) :D — user2864740, Jul 18 '20 at 20:07

silverstorm · Accepted Answer · 2020-07-18T20:15:49.373

1

I added these lines and now it works.

cmd.CommandText = "pr_VendegFelvetele";
cmd.CommandType = CommandType.StoredProcedure;

This link helped: https://dev.mysql.com/doc/connector-net/en/connector-net-programming-stored-using.html

edited Jul 18 '20 at 20:15

answered Jul 18 '20 at 20:06

silverstorm

53
6

Maybe you can mark your solution as the answer, so that can help others find the solution quickly. – 大陸北方網友 Jul 20 '20 at 01:45

Call MySQL procedure with OUT parameter

1 Answers1