Cannot add stored procedure with parameterized query
am 26.07.2006 22:42:31 von JP2006I'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!