Use parameters for MySqlCommand query

Question

I'm trying to insert into mysql table using this code:

// open connection here
this.conn.Open();

// fields from post request
var email = Request.Form["email"];  
var password = Request.Form["password"];

string sqlInsert = "INSERT INTO mytable(col1, col2) values(?email, ?password)";
MySqlCommand cmd1 = new MySqlCommand(sqlInsert, this.conn);
cmd1.Parameters.Add("?email", email);
cmd1.Parameters.Add("?password", password);
cmd1.ExecuteNonQuery();

here: cmd1.Parameters.Add("?email", email); I am getting error:

cannot convert from 'string' to 'MySql.Data.MySqlClient.mysqlDBType'

What I'm doing wrong?

Zohar Peled · Accepted Answer · 2019-04-04 06:46:15Z

5

You are using the Add method the wrong way.

It should be cmd1.Parameters.Add("?email", MySqlDbType.VarString).Value = email;
(You can choose another overload if you want to, but this is the easiest to use most of the times.)

Note: I'm guessing the data type of email is VarString.

edited Apr 4, 2019 at 6:46

answered Apr 4, 2019 at 6:44

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

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

4 Comments

styx Over a year ago

Or he can use AddWitheValue variant

Zohar Peled Over a year ago

@styx AddWithValue means that the MySql driver must infer the actual data type of the parameter supplied - which could be dangerous as it may lead to exceptions or even worst - wrong data. For more information, read this.

Mrinal Kamboj Over a year ago

@ZoharPeled here the client is SqlClient, which I have seen works fine while it tries to figure out the underlying type especially common data type like string, infact micro orm like Dapper also does it without an issue

Zohar Peled Over a year ago

@MrinalKamboj AddWithValue works find on many ADO.Net client implementations... until it doesn't. I wouldn't count on it "working fine" when I know for a fact that sometimes it doesn't, and there's a perfectly easy and safer way to send parameters to the database. The couple of extra key strokes are totally worth it.

Zeeshan Adil · Accepted Answer · 2019-04-04 06:50:18Z

Hi you need to convert your string parameter to MySQL Db type parameter, I usually create a function in code like this:

protected DbParameter GetParameter(string parameterName, object parameterValue)
        {
            DbParameter parameterObject = new MySqlParameter(parameterName, 
            parameterValue ?? DBNull.Value);

            //this defines if it is an input or output parameter
            // you can also have output paramters
            parameterObject.Direction = ParameterDirection.Input;

            return parameterObject;
        }

and call it int our code like this:

       var somevariable = "somevalue";

        var parameters = new List<DbParameter>
        {
            base.GetParameter("parametermnamehere", somevariable),
        };

Simple and Clean, hope it helps

Collectives™ on Stack Overflow

Use parameters for MySqlCommand query

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related