Cannot add stored procedure with parameterized query

Cannot add stored procedure with parameterized query

am 26.07.2006 22:42:31 von JP2006

I'm fairly new to this so hopefully it is a dumb mistake...

I need to create a simple stored procedure along the lines of:

delimiter //
CREATE DEFINER='root'@'localhost' PROCEDURE Insertuser()
BEGIN
insert into user (firstname,lastname) values (@fn, @ln);
END;
//

that I can then use with the code:

MySqlConnection oConn = new
MySqlConnection(ConfigurationManager.AppSettings["MySqlConn" ].ToString());
oConn.Open();
MySqlCommand oCommand = oConn.CreateCommand();
oCommand.CommandType = CommandType.Text;
oCommand.CommandText = "INSERT into user (firstname,lastname)
VALUES (@fn,@ln)";

MySqlParameter p0 = new MySqlParameter("@fn", TextBox2.Text);
oCommand.Parameters.Add(p0);
MySqlParameter p1 = new MySqlParameter("@ln", TextBox3.Text);
oCommand.Parameters.Add(p1);

oCommand.ExecuteNonQuery();
oConn.Close();

However running this sproc and code with MySQL just puts in a blank
record into the table :-(

So...I read that with MySQL you need to use the ? character in place of
the @ character for parameterized queries.
I did this replacement and the code ran fine but ONLY if I had the
query set up as CommandType text (as in the example above).

The PROBLEM is that when I try to create the sproc using the MySQL
command line tool with:

delimiter //
CREATE DEFINER='root'@'localhost' PROCEDURE Insertuser()
BEGIN
insert into user (firstname,lastname) values (?fn, ?ln);
END;
//

It gives me a syntax error - presumably due to the ? characters!

Any advice on where I am going wrong gladly received!