CODEWITHSUNDEEP
c#asp.net
Oto Shavadze
Oto Shavadze

Reputation: 42773

Use parameters for MySqlCommand query

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?

Upvotes: 1

Views: 660

Answers (2)

Zeeshan Adil
Zeeshan Adil

Reputation: 2125

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

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82484

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.

Upvotes: 5

Related Questions