469,628 Members | 1,221 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,628 developers. It's quick & easy.

Cannot add stored procedure with parameterized query

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!

Jul 26 '06 #1
0 3473

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Andrew J Durstewitz | last post: by
10 posts views Thread by Thomas R. Hummel | last post: by
1 post views Thread by pankajit09 | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.