472,146 Members | 1,401 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,146 software developers and data experts.

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 3531

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 Saiars | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.