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

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

CodePudding user response：

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: