c# - GetCountry 查询中的 SQL 语法错误

Question

已关闭。这个问题是 not reproducible or was caused by typos 。目前不接受答案。

---

这个问题是由拼写错误或无法再重现的问题引起的。虽然类似的问题可能是 on-topic在这里，这个问题的解决方式不太可能帮助 future 的读者。

已关闭 8 年前。

Improve this question

我的代码中出现语法错误。 adapter.Fill(dt); 行出现错误就在adapter.SelectCommand.CommandText = @"SELECT c.*,(Select Initials FROM users....之后线。该错误表示，请检查与您的 MySQL 服务器版本相对应的手册，以获取在第 1 行的“237 ORDER BY c.CountryName”附近使用的正确语法，但我不确定是什么导致了此错误

public static string GetCountry(int CountryID)
{
    string Country = "";

    string sql = "proc_GetCountries";

    DataTable dt = new DataTable();

    using (MySql.Data.MySqlClient.MySqlDataAdapter adapter = new MySql.Data.MySqlClient.MySqlDataAdapter(sql, DataUtils.ConnectionStrings["TAT"]))
    {
        adapter.SelectCommand.CommandType = CommandType.Text;
        adapter.SelectCommand.CommandText = @"SELECT * FROM countries WHERE ID = ?countryID ORDER BY CountryName";
        adapter.SelectCommand.Parameters.Add(new MySqlParameter("countryID", CountryID));
        adapter.Fill(dt);
        if (dt.Rows.Count > 0)
        {
            Country = dt.Rows[0]["CountryName"].ToString();
        }
    }
    return Country;
}
public static DataSet GetCountry(int Company_ID, int ID)
{
    DataSet ds = new DataSet();

    string sql = "proc_GetCountry";

    DataTable dt = new DataTable(), dtRates = new DataTable(), dtDepots = new DataTable();

    using (MySql.Data.MySqlClient.MySqlDataAdapter adapter = new MySql.Data.MySqlClient.MySqlDataAdapter(sql, DataUtils.ConnectionStrings["TAT"]))
    {
        adapter.SelectCommand.CommandType = CommandType.Text;
        adapter.SelectCommand.CommandText = @"SELECT c.*,(Select Initials FROM users WHERE User_ID = c.CreatedByUser) AS CreatedBy, (SELECT Initials FROM users WHERE User_ID = c.ModifiedByUser) AS ModifiedBy " +
            "FROM countries c WHERE c.Company_ID = ?company_ID AND c.ID ?iD ORDER BY c.CountryName";
        adapter.SelectCommand.Parameters.Add(new MySqlParameter("company_ID", Company_ID));
        adapter.SelectCommand.Parameters.Add(new MySqlParameter("iD", ID));
        adapter.Fill(dt);
        ds.Tables.Add(dt);
    }
    //Rates
    using (MySql.Data.MySqlClient.MySqlDataAdapter adapter = new MySql.Data.MySqlClient.MySqlDataAdapter(sql, DataUtils.ConnectionStrings["TAT"]))
    {
        adapter.SelectCommand.CommandType = CommandType.Text;
        adapter.SelectCommand.CommandText = @"SELECT cc.* FROM country_rates cc WHERE cc.CountryID ?iD";
        adapter.SelectCommand.Parameters.Add(new MySqlParameter("iD", ID));
        adapter.Fill(dtRates);
        ds.Tables.Add(dtRates);
    }
    //Depots
    using (MySql.Data.MySqlClient.MySqlDataAdapter adapter = new MySql.Data.MySqlClient.MySqlDataAdapter(sql, DataUtils.ConnectionStrings["TAT"]))
    {
        adapter.SelectCommand.CommandType = CommandType.Text;
        adapter.SelectCommand.CommandText = @"SELECT cc.* FROM country_depot cc WHERE cc.CountryID = ?iD";
        adapter.SelectCommand.Parameters.Add(new MySqlParameter("iD", ID));
        adapter.Fill(dtDepots);
        ds.Tables.Add(dtDepots);
    }
    return ds;
}

Answer 1

试试这个，您在 c.ID ?iD 处缺少 =:

adapter.SelectCommand.CommandText = @"SELECT c.*,(Select Initials FROM users WHERE User_ID = c.CreatedByUser) AS CreatedBy, (SELECT Initials FROM users WHERE User_ID = c.ModifiedByUser) AS ModifiedBy " +
                   "FROM countries c WHERE c.Company_ID = ? company_ID AND c.ID = ? iD ORDER BY c.CountryName";