MySql 查询 - 选择位置、别名和名称范围

Question

我在使用使用别名和参数的 MySQL SelectWhere 语句时遇到一些问题。我的问题出在声明的Where 部分。就目前情况而言，当我尝试使用任何参数时，我不会返回任何结果。

有问题的语句是：

SELECT postcode, suburb, streetname, categorycode, DATE_FORMAT(dateRecorded, '%d/%m/%Y') AS Expr1, DATE_FORMAT(dateLastModified, '%d/%m/%Y') AS Expr2, status FROM incidentdetails WHERE (postcode = @postcode) AND (suburb = @suburb) AND (categorycode = @categorycode) AND (status = @status)

使用正确的参数执行此查询会返回正确的列，但不会返回任何数据。

完全删除 where 子句，我将得到预期的完整表。

将 where 子句从 WHERE (postcode = @postcode) AND (suburb = @suburb) AND (categorycode = @categorycode) AND (status = @status) 更改为 WHERE (postcode = 4020)确实有效——正如预期的那样。

将 WHERE 子句替换为 (postcode = @postcode) 并传递参数（如下所示）不起作用。

sqlFillRelated.Parameters.AddWithValue("@postcode", int.Parse(PostcodeTxtBox.Text.ToString()))

从服务器资源管理器 SQL 命令运行时，完整查询（带参数）可以成功运行。

        string sqlFILL = "SELECT postcode, suburb, streetname, categorycode, DATE_FORMAT(dateRecorded, '%d/%m/%Y') AS Expr1, DATE_FORMAT(dateLastModified, '%d/%m/%Y') AS Expr2, status FROM incidentdetails WHERE (postcode = @postcode) AND (suburb = @suburb) AND (categorycode = @categorycode) AND (status = @status)";
    string sql = "SELECT COUNT(*) FROM incidentdetails WHERE (postcode = @postcode) AND (suburb = @suburb) AND (categorycode = @categorycode) AND (status = @status)";
    MySqlConnection mycon = new MySqlConnection(sqlconnection);
    mycon.Open();
    MySqlCommand selectRelatedCmd = new MySqlCommand(sql, mycon);
    MySqlCommand sqlFillRelated = new MySqlCommand(sqlFILL, mycon);
    int matches = 0;
    selectRelatedCmd.Parameters.AddWithValue("@postcode", int.Parse(PostcodeTxtBox.Text.ToString()));
    selectRelatedCmd.Parameters.AddWithValue("@suburb", SuburbTxtBox.Text.ToString());
    selectRelatedCmd.Parameters.AddWithValue("@categorycode",IncidentTypeDropList.Text.ToString());
    selectRelatedCmd.Parameters.AddWithValue("@status", "Open");
    sqlFillRelated.Parameters.AddWithValue("@postcode", int.Parse(PostcodeTxtBox.Text.ToString()));
    sqlFillRelated.Parameters.AddWithValue("@suburb", SuburbTxtBox.Text.ToString());
    sqlFillRelated.Parameters.AddWithValue("@categorycode", IncidentTypeDropList.Text.ToString());
    sqlFillRelated.Parameters.AddWithValue("@status", "Open");

    matches = int.Parse(selectRelatedCmd.ExecuteScalar().ToString());
    if (matches == 0)
    {
        matchingIncidentPanel.Visible = false;  
    }
    else if (matches >= 1)
    {
        matchingIncidentPanel.Visible = true;
    }

    MySqlDataAdapter da = new MySqlDataAdapter(sqlFILL, mycon);
    DataTable table = new DataTable();
    table.Locale = System.Globalization.CultureInfo.InvariantCulture;
    da.Fill(table);
    g.DataSource = table;
    g.DataBind();

    mycon.Close();

Answer 1

好吧，这是一个很长的尝试，因为我不确定你使用的是什么语言（是 C# 吗？），但让我感到奇怪的是你在设置参数时使用 @。

通常，您使用一些魔术字符来表示 SQL 查询中的参数，但是当您以编程方式设置它时，您会忽略该字符。

你尝试

sqlFillRelated.Parameters.AddWithValue("status", "Open")

过而不是吗

sqlFillRelated.Parameters.AddWithValue("@status", "Open")

？

Answer 2

它是C#。放弃 C# 代码中参数的“@”前缀。