asp.netsql-serverdatatableparameterssqlexception

Troubles when fetching data from table with a query with a parameter

I'm working with ASP.net. I'm trying to fetch data from a table "Pret" and display them in view. The following code is working properly:

public ActionResult Details(int id)
{
    StringBuilder errorMessages = new StringBuilder();

    using (SqlConnection con = new SqlConnection(chaineConnexion))
    {
        DataTable tabRetard = new DataTable();
        con.Open();

        SqlDataAdapter adp = new SqlDataAdapter();

        SqlCommand command = new SqlCommand(
            "SELECT Livre.titre,Membre.nom, " +
                "FORMAT(Retard.DatePret, 'yyyy-MM-dd') as DatePret, Nbjour FROM Retard " +
                "LEFT JOIN Livre ON Retard.Id_livre = Livre.Id " +
                "LEFT JOIN Membre ON Retard.Id_membre = Membre.Id", con);
        adp.SelectCommand = command;

        adp.Fill(tabRetard);

        return View(tabRetard);
    }
}

Now I'm trying to add a parameter to the query like that, but it throws an exception

System.Data.SqlClient.SqlException : 'Incorrect syntax near 'Retard'

I can't figure out what the problem is !

public ActionResult Details(int id)
{
    StringBuilder errorMessages = new StringBuilder();

    using (SqlConnection con = new SqlConnection(chaineConnexion))
    {
        DataTable tabRetard = new DataTable();

        con.Open();

        SqlDataAdapter adp = new SqlDataAdapter();

        SqlCommand command = new SqlCommand(
            "SELECT Livre.titre, Membre.nom, " +
                "FORMAT(Retard.DatePret, 'yyyy-MM-dd') as DatePret, Nbjour FROM Retard " +
                "LEFT JOIN Livre ON Retard.Id_livre = Livre.Id " +
                "LEFT JOIN Membre ON Retard.Id_membre = Membre.Id" +
                "WHERE Retard.Id_membre = @Id_membre", con);

        command.Parameters.AddWithValue("@Id_membre", id);

        adp.SelectCommand = command;
        adp.Fill(tabRetard);

        return View(tabRetard);
    }
}
Solution

  • This is caused by a typo in your string concatenation, it's missing whitespace between Membre.Id and WHERE:

    SqlCommand command = new SqlCommand(
    "SELECT Livre.titre, Membre.nom, " +
        "FORMAT(Retard.DatePret, 'yyyy-MM-dd') as DatePret, Nbjour FROM Retard " +
        "LEFT JOIN Livre ON Retard.Id_livre = Livre.Id " +
        "LEFT JOIN Membre ON Retard.Id_membre = Membre.Id" + /*Needs a space at the end*/
/*or at the beginning*/ "WHERE Retard.Id_membre = @Id_membre", con);

    Try this instead:

    SqlCommand command = new SqlCommand(
    "SELECT Livre.titre, Membre.nom, " +
        "FORMAT(Retard.DatePret, 'yyyy-MM-dd') as DatePret, Nbjour FROM Retard " +
        "LEFT JOIN Livre ON Retard.Id_livre = Livre.Id " +
        "LEFT JOIN Membre ON Retard.Id_membre = Membre.Id " +
        "WHERE Retard.Id_membre = @Id_membre", con);

    Also, try to avoid use of AddWithValue since it can often cause problems with query parameters such as incorrect type conversion, query plan cache bloat and so on:

    command.Parameters.AddWithValue("@Id_membre", id);

    Prefer to use SqlCommand's Parameters.Add methods that include the SqlDbType and length parameters, e.g. for int values:

    command.Parameters.Add("@Id_membre", SqlDbType.Int).Value = id;

    For string values match the length of the related table/view columns, e.g.:

    command.Parameters.Add("@nom", SqlDbType.NVarChar, 50).Value = nom;

    Interesting reading on AddWithValue: