Add parameters in query on c#

Question

In my code behind of aspx page I have problem to passed values on Parameters in sql query.

I use MySql database.

Step 1:

I add in List the output of an query:

while (reader.Read())
{
    idcolor = reader["idcolor"].ToString();
    colorList.Add(idcolor.ToString());
}

ns = string.Join("','", colorList.ToArray());

In debug the output is:

ns = red','green

Step 2:

I need use the values of string ns on a sql query.

And pass the values of string ns in parameters:

str = null;
str = ns == null ? "" : ns.ToString();

sql = @" SELECT * FROM Experience WHERE Colors IN (?); ";

    DataSet dsColors = new DataSet();

    using (OdbcConnection cn =
      new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnMySQL"].ConnectionString))
    {
        cn.Open();

        using (OdbcCommand cmd = new OdbcCommand(sql, cn))
        {

            cmd.Parameters.AddWithValue("param1", Server.UrlDecode(str.ToString()));

            OdbcDataAdapter adapter = new OdbcDataAdapter(cmd);
            adapter.Fill(dsColors);
        }
    }

    return dsColors;

Step 3:

If used in query :

sql = @" SELECT * FROM Experience WHERE Colors IN (?); ";

The output in dataset is empty.

If used in query :

sql = @" SELECT * FROM Experience WHERE Colors IN ( '" + Server.UrlDecode(str.ToString()) + "' ); ";

The output in dataset is right.

Anybody know how can I resolve do this?

Can you suggest?

Can you help me?

Thank you in advance.

apomene · Accepted Answer · 2015-08-04 13:09:46Z

1

you have to use MySql.Data.MySqlClient; to connect to Mysql:

sql = @" SELECT * FROM Experience WHERE Colors IN (@param1,@param2) ";

    DataSet dsColors = new DataSet();

    using ( MySqlConnection cn =
      new MySqlConnection(ConfigurationManager.ConnectionStrings["ConnMySQL"].ConnectionString))
    {
        cn.Open();

        using (MySqlCommand cmd = new MySqlCommand(sql, cn))
        {

            cmd.Parameters.Add("@param1", colorList[0]/ToString());
            cmd.Parameters.Add("@param2",colorList[1].ToString());    
            MySqlDataAdapter adapter = new MySqlaAdapter(cmd);
            adapter.Fill(dsColors);
        }
    }

edited Aug 4, 2015 at 13:09

answered Aug 4, 2015 at 12:54

apomene

14.4k10 gold badges51 silver badges76 bronze badges

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

4 Comments

CodeCaster Over a year ago

Explain what problem you perceived, what you changed and how that fixes the problem.

apomene Over a year ago

use MySqlClient instead of Oldedb

C4d Over a year ago

Dont know why this got downvoted. I just can repeate apomene: Use MySqlClient. Upped.

Antonio Mailtraq Over a year ago

the server is in hosting I don't use MySqlClient

fubo · Accepted Answer · 2015-08-04 13:17:35Z

1

if you dont want to add a parameter for each color, you can go with

MySql.Data.MySqlClient.MySqlHelper.EscapeString()

that's not pretty but it's internal used by parameters and you can add a dynamic number of values and you're safe against injection

while (reader.Read())
{
    idcolor = reader["idcolor"].ToString();
    colorList.Add(MySql.Data.MySqlClient.MySqlHelper.EscapeString(idcolor));
}

ns = string.Join("','", colorList.ToArray());

answered Aug 4, 2015 at 13:17

fubo

46.2k20 gold badges109 silver badges139 bronze badges

Comments

DotNetHitMan · Accepted Answer · 2015-08-04 13:21:14Z

You need to add a parameter and place holder for each item you want in your in clause. For example

sql = @" SELECT * FROM Experience WHERE Colors IN (?,?,?); ";

Then add the params for each one.

cmd.Parameters.AddWithValue("param1", Server.UrlDecode(str.ToString()));

Example

        List<string> colours = new List<string>();

        colours.Add("black");
        colours.Add("red");

        var placeHolders = string.Join(",",(from colour in colours select "?").ToList());

        var sql = @String.Format(" SELECT * FROM Experience WHERE Colors IN ({0}); ",placeHolders);

        DataSet dsColors = new DataSet();

        using (OdbcConnection cn = new OdbcConnection(ConnectionString))
        {
            cn.Open();

            using (OdbcCommand cmd = new OdbcCommand(sql, cn))
            {
                foreach(var colour in colours)
                {
                    cmd.Parameters.AddWithValue(colour, colour);
                }

                OdbcDataAdapter adapter = new OdbcDataAdapter(cmd);
                adapter.Fill(dsColors);
            }
        }

DRapp · Accepted Answer · 2015-08-04 13:24:17Z

You appear close with the context, but try getting the results for one color at a time and just keep changing the VALUE of the parameter. By calling the FILL, it will just keep adding records to the table each time it is called. However, set your FILL to point to a DataTable instead of a DataSet. So it doesn't keep putting TABLES into your data set, but instead uses the one continues to append to it. This would work if you had 1 color or 1000 colors...

... rest of previous code BEFORE the OdbcCommand
... and ensure clean values for your colors as others have noted.
using (OdbcCommand cmd = new OdbcCommand(sql, cn))
{
   // Just to add the parameter "place-holder" for your query
   cmd.Parameters.AddWithValue("param1", "");

   // DataTable ONCE to receive all the colors being queried
   DataTable tblAllColors = new DataTable();

   // build the adapter ONCE no matter how many colors you will be querying
   OdbcDataAdapter adapter = new OdbcDataAdapter(cmd);

   // so for this loop, you are just getting the colors one at a time.
   foreach( string s in colorList )
   {
      // next color you are trying to get... just overwrite the 
      // single parameter with the new color.
      adapter.SelectCommand.Parameters[0].Value = s;
      adapter.Fill(tblAllColors);
   }

   // you would otherwise have to build your query dynamically and keep 
   // adding parameter-placeholders "?" for each color in a comma list 
   // as you were attempting... which would be a slightly different query.
}

dsColors.Tables.Add( tblAllColors );

Collectives™ on Stack Overflow

Add parameters in query on c#

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related