c# - [MySQL][ODBC 5.1 驱动程序][mysqld-5.1.51-community]SQLBindParameter 未用于所有参数

Question

我在 MySQL 数据库上的查询遇到问题:

SELECT * FROM dotable WHERE CO IN ('AAA','BBB','CCC')

如果在 MySQL 中启动此查询，则该查询有效。

如果在 C# 中尝试，我会遇到此错误:

ERROR [07001] [MySQL][ODBC 5.1 Driver][mysqld-5.1.51-community]SQLBindParameter not used for all parameters

你能帮我解决这个问题吗？

提前致谢。

下面是我的代码。

private DataSet RetrieveProducts()
{
    if (Request.Cookies["username"] != null)
    {
        sql = string.Format("SELECT * FROM dotable WHERE CO IN ({0}); ", ns);

        DataSet dsProducts = new DataSet();
        dt1 = new DataTable();

        DataTable dtProducts = new DataTable();
        dsProducts = new DataSet();

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

            using (OdbcCommand cmd = new OdbcCommand(sql, cn))
            {
                foreach (var co in colorList)
                {
                    cmd.Parameters.AddWithValue(co, co);
                    OdbcDataAdapter adapter = new OdbcDataAdapter(cmd);
                    adapter.Fill(dsProducts);

                    if (dsProducts.Tables.Count > 0)
                    {
                        dt1 = dsProducts.Tables[0];
                    }

                    dtProducts = dt1;
                }
            }
        }
        return dsProducts;
    }
    else
    {
        return null;
    }
}


protected void Username()
{
    using (OdbcConnection myConnectionString =
        new OdbcConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString))
    {
        string sql_aut = @" SELECT * FROM WHERE UPPER(username) = ? AND aut = 1; ";

        using (OdbcCommand command =
            new OdbcCommand(sql_aut, myConnectionString))
        {
            try
            {
                if (Request.Cookies["username"] != null)
                {
                    command.Parameters.AddWithValue("param1", Server.UrlDecode(Request.Cookies["username"].Value.ToUpper()));
                    command.Connection.Open();

                    using (OdbcDataReader reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                CO = reader["CO"].ToString();
                                aut = Convert.ToInt32(reader["aut"].ToString());
                                colorList.Add(idDTES.ToString());
                            }

                            ns = "";
                            ns = string.Join(",", (from co in colorList select "?").ToList());

                        }
                        else
                        {
                            Page.ClientScript.RegisterStartupScript(this.GetType(), "Msg", "alert('KO1.');window.location='http://...';", true);
                        }
                    }
                }
                else
                {
                    Page.ClientScript.RegisterStartupScript(this.GetType(), "Msg", "alert('KO2.');window.location='http://...';", true);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("operation failed!", ex);
            }
            finally
            {
                command.Connection.Close();
            }
        }
    }
}

Answer 1

请更改此:

using (OdbcCommand cmd = new OdbcCommand(sql, cn))
            {
                foreach (var co in colorList)
                {
                    cmd.Parameters.AddWithValue(co, co);
                    OdbcDataAdapter adapter = new OdbcDataAdapter(cmd);
                    adapter.Fill(dsProducts);

                    if (dsProducts.Tables.Count > 0)
                    {
                        dt1 = dsProducts.Tables[0];
                    }

                    dtProducts = dt1;
                }
            }

至:

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

                    OdbcDataAdapter adapter = new OdbcDataAdapter(cmd);
                    adapter.Fill(dsProducts);

                    if (dsProducts.Tables.Count > 0)
                    {
                        dt1 = dsProducts.Tables[0];
                    }

                    dtProducts = dt1;

            }