Inserting record in to MySQL Database using C#

Question

I am currently developing an application using C# WPF. I am trying to store data into a MySQL Database. Below is the code that I have.

 MySqlCommand cmd = new MySqlCommand("", conn);

                cmd.CommandText = "INSERT INTO BUG_REPORTS (bug_softwareID, bug_firstName, bug_lastName, bug_email, bug_description, bug_ip_addr, bug_dateReported) "
                    + "VALUES (@softwareID, @firstName, @lastName, @email, @description, @ip_addr, @dateReported)";

                cmd.Parameters.Add("@softwareID");
                cmd.Parameters.Add("@firstName");
                cmd.Parameters.Add("@lastName");
                cmd.Parameters.Add("@email");
                cmd.Parameters.Add("@description");
                cmd.Parameters.Add("@ip_addr");
                cmd.Parameters.Add("@dateReported");

                cmd.Parameters["@softwareID"].Value = softwareID;
                cmd.Parameters["@firstName"].Value = getFirstName();
                cmd.Parameters["@lastName"].Value = getLastName();
                cmd.Parameters["@email"].Value = getEmail();
                cmd.Parameters["@description"].Value = getDescription();
                cmd.Parameters["@ip_addr"].Value = ip_addr;
                cmd.Parameters["@dateReported"].Value = date;

                cmd.ExecuteNonQuery();

Everytime I try to insert a record it comes up with the error 'Only MySQLParameter objects may be stored. What am I doing wrong. I found article and everything appears to be OK.

Thanks for any help you can provide

Patrick · Accepted Answer · 2011-02-16 20:07:35Z

2

In your paramters try:

cmd.Parameters.Add(new OdbcParameter("@softwareID", softwareID));

And so on and so forth with the rest of your parameters.

And in all honesty it might be just as simple just to build your sql inline and execute the command without parameters unless your not validating text and are concerned about injection attacks.

edited Feb 16, 2011 at 20:07

answered Feb 16, 2011 at 20:01

Patrick

7,7227 gold badges42 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Boardy Over a year ago

Hi thanks for the help.I have got it working but not quite in the way you suggested but answer did help me. I'm not using Odbc so I left out the OdbcParameter which it now came up with deprecated and suggested something different. So I used cmd.Parameters.AddWithValues("@softwareID", softwareID));

newuser · Accepted Answer · 2011-02-16 20:01:36Z

0

I'm not sure if it translates over directly, but when I want to INSERT or UPDATE a table with T-SQL I use .ExecuteScalar().

answered Feb 16, 2011 at 20:01

newuser

1731 silver badge16 bronze badges

1 Comment

womp Over a year ago

ExecuteNonQuery() is the correct method to call for INSERT or UPDATE. ExecuteScalar() is for returning a single value (like a count) or for reading the first column of the first row that is returned.

JohnFx · Accepted Answer · 2011-02-16 20:05:41Z

0

Have you tried it using this style. It is a lot more compact and might possibly solve your problem.

cmd.Parameters.Add("@softwareID",softwareID);
cmd.Parameters.Add("@firstName",getFirstName());
cmd.Parameters.Add("@lastName",getLastName());
cmd.Parameters.Add("@email",getEmail());
cmd.Parameters.Add("@description",getDescription());
cmd.Parameters.Add("@ip_addr",ip_addr);
cmd.Parameters.Add("@dateReported",date);
cmd.ExecuteNonQuery();

answered Feb 16, 2011 at 20:05

JohnFx

35k19 gold badges108 silver badges169 bronze badges

1 Comment

Boardy Over a year ago

Yea I did try that but Visual Studio said add was deprecated and to use addwithvalues instead

Francis B. · Accepted Answer · 2011-02-16 20:54:04Z

The parameter identifier with the MySql .NET connector is '?'.

So you must use the following code:

cmd.CommandText = "INSERT INTO BUG_REPORTS (bug_softwareID, bug_firstName, bug_lastName, bug_email, bug_description, bug_ip_addr, bug_dateReported) "
                        + "VALUES (?softwareID, ?firstName, ?lastName, ?email, ?description, ?ip_addr, ?dateReported)";

cmd.Parameters.Add("?softwareID");
cmd.Parameters.Add("?firstName");
cmd.Parameters.Add("?lastName");
cmd.Parameters.Add("?email");
cmd.Parameters.Add("?description");
cmd.Parameters.Add("?ip_addr");
cmd.Parameters.Add("?dateReported");

cmd.Parameters["?softwareID"].Value = softwareID;
cmd.Parameters["?firstName"].Value = getFirstName();
cmd.Parameters["?lastName"].Value = getLastName();
cmd.Parameters["?email"].Value = getEmail();
cmd.Parameters["?description"].Value = getDescription();
cmd.Parameters["?ip_addr"].Value = ip_addr;
cmd.Parameters["?dateReported"].Value = date;

Collectives™ on Stack Overflow

Inserting record in to MySQL Database using C#

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related