MySql Query - Select Where, Alias & parameter

I'm ha开发者_如何学JAVAving some trouble with a MySQL Select Where statement that uses aliases and parameters. My problem lies with the Where part of the statement. As it stands, I'm not returning any results when I try to use any parameters.

The statement in question is:

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)

Executing this query with the correct parameters returns the right columns, but no data.

Removing the where clause entirely I get the full table as expected.

Altering the where clause from WHERE (postcode = @postcode) AND (suburb = @suburb) AND (categorycode = @categorycode) AND (status = @status) to WHERE (postcode = 4020)does work -- as expected.

Replacing the WHERE clause with (postcode = @postcode) and passing the parameter (as below) does not work .

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

The full query (with parameters) works successfully when run from the server explorer SQL command.

        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();

Ok, this is a long shot because I am not sure what language are you using (is it C#?) but it strikes me odd that you use @ when setting the parameters.

Typically, you use some magic character to denote parameter in SQL query, but when you are setting it programatically you omit this character.

Did you try

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

instead of

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

?

It is C#. Ditch the '@' prefixes to the params in the C# code.