36

Good day to all, I'm using Visual C# 2010 and MySQL Version 5.1.48-community. I hope you can help me with this code. I don't find it working on me. What am I missing?

string connString = ConfigurationManager.ConnectionStrings["default"].ConnectionString;
MySqlConnection conn = new MySqlConnection(connString);
conn.Open();
MySqlCommand comm = conn.CreateCommand();
comm.CommandText = "INSERT INTO room(person,address) VALUES(@person, @address)";
comm.Parameters.Add("@person", "Myname");
comm.Parameters.Add("@address", "Myaddress");
comm.ExecuteNonQuery();
conn.Close();

And when I try to compile it. It says:

Person column cannot be null

EDITED:

But when I try this code. 

comm.CommandText = "INSERT INTO room(person,address) VALUES('Myname', 'Myaddress')";

But this code is prone to sql injection attack but it works, doesn't gives me an error.

EDITED:

I tried to use this. I found it here so I thought It would work but gives me this error

Index (zero based) must be greater than or equal to zero and less than the size of the argument list.

Any idea?

    string a = "myname";
    MySqlCommand cmd = new MySqlCommand();
    cmd.Connection = conn;
    cmd.CommandText = "INSERT INTO room(person,address) VALUES(?,?)";
    //cmd.Prepare();

    cmd.Parameters.Add("person", MySqlDbType.VarChar).Value = a;
    cmd.Parameters.Add("address", MySqlDbType.VarChar).Value = "myaddress";
    cmd.ExecuteNonQuery(); // HERE I GOT AN EXCEPTION IN THIS LINE

Any help would be much appreciated.

EDITED: SOLVED I used this code:

cmd.CommandText = "INSERT INTO room(person,address) VALUES(?person,?address)";
cmd.Parameters.Add("?person", MySqlDbType.VarChar).Value = "myname";
cmd.Parameters.Add("?address", MySqlDbType.VarChar).Value = "myaddress";
cmd.ExecuteNonQuery();

Thanks SO!

Improve this question
4
  • could you post the exact exception you are getting? Normaly you should use AddWithValue as every one else stated. Add is depreciated so you shouldn't use it Commented Apr 23, 2013 at 14:01
  • There. I got an exception in cmd.ExecuteNonQuery(); Yeah I know. You see I'm using Visual C# 2010 and I tried to use AddWithValue but It seems 2010 doesn't support it. Don't know if I'm right though. Commented Apr 23, 2013 at 14:07
  • I curently work with vs2008 and I use it. At my college we had vs2010 and we were using AddWithValue Commented Apr 23, 2013 at 14:16
  • Really? This comes out when I used AddWithValue. MySql.Data.MySqlClient.MySqlParameterCollection' does not contain a definition for 'AddWithValue' and no extension method 'AddWithValue' accepting a first argument of type 'MySql.Data.MySqlClient.MySqlParameterCollection' could be found (are you missing a using directive or an assembly reference?) Commented Apr 23, 2013 at 14:21

9 Answers 9

Reset to default
29

You may use AddWithValue method like:

string connString = ConfigurationManager.ConnectionStrings["default"].ConnectionString;
MySqlConnection conn = new MySqlConnection(connString);
conn.Open();
MySqlCommand comm = conn.CreateCommand();
comm.CommandText = "INSERT INTO room(person,address) VALUES(@person, @address)";
comm.Parameters.AddWithValue("@person", "Myname");
comm.Parameters.AddWithValue("@address", "Myaddress");
comm.ExecuteNonQuery();
conn.Close();

OR

Try with ? instead of @, like:

string connString = ConfigurationManager.ConnectionStrings["default"].ConnectionString;
MySqlConnection conn = new MySqlConnection(connString);
conn.Open();
MySqlCommand comm = conn.CreateCommand();
comm.CommandText = "INSERT INTO room(person,address) VALUES(?person, ?address)";
comm.Parameters.Add("?person", "Myname");
comm.Parameters.Add("?address", "Myaddress");
comm.ExecuteNonQuery();
conn.Close();

Hope it helps...

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

2 Comments

I'm using Visual C# 2010 Express. The first one won't let me use AddWithValue.. And I already tried the second one.. Won't give me errors but won't insert to my database.
this is so confusing... official doc says use @ - dev.mysql.com/doc/connector-net/en/… - but it did not work for me, and only ? did.
6

Use the AddWithValue method:

comm.Parameters.AddWithValue("@person", "Myname");
comm.Parameters.AddWithValue("@address", "Myaddress");
Improve this answer

3 Comments

I cannot do that. I gives me an error. It only let me use comm.Parameters.Add("@person", <Value>)
@BoyKarton, may be with your version of MySql connector, you need to remove @ sign and replace it with ?.
I tried to change all @ to ? and yes.. It doesn't give me errors but it won't insert to my database.
5

I had the same issue -- Finally tried the ? sigil instead of @, and it worked.

According to the docs:

Note. Prior versions of the provider used the '@' symbol to mark parameters in SQL. This is incompatible with MySQL user variables, so the provider now uses the '?' symbol to locate parameters in SQL. To support older code, you can set 'old syntax=yes' on your connection string. If you do this, please be aware that an exception will not be throw if you fail to define a parameter that you intended to use in your SQL.

Really? Why don't you just throw an exception if someone tries to use the so called old syntax? A few hours down the drain for a 20 line program...

MySQL::MySQLCommand

Improve this answer

Comments

4

Three things: use the using statement, use AddWithValue and prefix parameters with ? and add Allow User Variables=True to the connection string.

 string connString = ConfigurationManager.ConnectionStrings["default"].ConnectionString;
 using (var conn = new MySqlConnection(connString))
 {
      conn.Open();
      var comm = conn.CreateCommand();
      comm.CommandText = "INSERT INTO room(person,address) VALUES(@person, @address)";
      comm.Parameters.AddWithValue("?person", "Myname");
      comm.Parameters.AddWithValue("?address", "Myaddress");
      comm.ExecuteNonQuery();
  }

Also see http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx for more information about the command usage, and http://dev.mysql.com/doc/refman/5.1/en/connector-net-connection-options.html for information about the Allow User Variables option (only supported in version 5.2.2 and above).

Improve this answer

7 Comments

did you add AllowUserVariables=true to your connection string?
Yes but it's not supported.
did you do it like: ConnectionString = "DataSource=xx;Database=test;UserID=xx;Password=xx;PORT=xx;Allow Zero Datetime=True;allow user variables = true"; ? Maybe show your connection string... (it's supported as from version 5.2.2, which version do you have?)
Here. I added Allow user Variables but it says not supported. connectionString="Server=localhost;Database=super;uid=root;password=root;Allow User Variables=true;"
what mysql version are you using?
|
4

I was facing very similar problem while trying to insert data using mysql-connector-net-5.1.7-noinstall and Visual Studio(2015) in Windows Form Application. I am not a C# guru. So, it takes around 2 hours to resolve everything.

The following code works lately:

string connetionString = null;
connetionString = "server=localhost;database=device_db;uid=root;pwd=123;";

using (MySqlConnection cn = new MySqlConnection(connetionString))
{
    try
    {
        string query = "INSERT INTO test_table(user_id, user_name) VALUES (?user_id,?user_name);";
        cn.Open();
        using (MySqlCommand cmd = new MySqlCommand(query, cn))
        {
            cmd.Parameters.Add("?user_id", MySqlDbType.Int32).Value = 123;
            cmd.Parameters.Add("?user_name", MySqlDbType.VarChar).Value = "Test username";
            cmd.ExecuteNonQuery();
        }
    }
    catch (MySqlException ex)
    {
        MessageBox.Show("Error in adding mysql row. Error: "+ex.Message);
    }
}
Improve this answer

Comments

0 
comm.Parameters.Add("person", "Myname");
Improve this answer

Comments

0

What I did is like this.

String person;
String address;
person = your value;
address =your value;
string connString = ConfigurationManager.ConnectionStrings["default"].ConnectionString;
    MySqlConnection conn = new MySqlConnection(connString);
    conn.Open();
    MySqlCommand comm = conn.CreateCommand();
    comm.CommandText = "INSERT INTO room(person,address) VALUES('"+person+"','"+address+"')";
    comm.ExecuteNonQuery();
    conn.Close();
Improve this answer

1 Comment

Can you add what your answer does differently please? Explaining what you did helps future readers understand faster and gets you more rep.
-1

Try adjusting the code at "SqlDbType" to match your DB type if necessary and use this code:

comm.Parameters.Add("@person",SqlDbType.VarChar).Value=MyName;

or:

comm.Parameters.AddWithValue("@person", Myname);

That should work but remember with Command.Parameters.Add(), you can define the specific SqlDbType and with Command.Parameters.AddWithValue(), it will try get the SqlDbType based on parameter value implicitly which can break sometimes if it can not implicitly convert the datatype.

Hope this helps.

Improve this answer

3 Comments

I've tried doing this cmd.Parameters.Add("@person", MySqlDbType.VarChar).Value = "myname"; and still same error. Don't know where I went wrong
comm.Parameters.Add("@person",SqlDbType.VarChar).Value = TextBox1.Text; //See if the error still occurs like that.
Still doesn't work. I even tried to retyping all of it. Still the same error. Don't know where I went wrong.
-2

try this it is working

 MySqlCommand dbcmd = _conn.CreateCommand();
    dbcmd.CommandText = sqlCommandString;
    dbcmd.ExecuteNonQuery();
    long imageId = dbcmd.LastInsertedId;
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.