Why is my SQL query for a single value failing?-CodePudding

I have the following code that fails at the if (!rdr.Read()), and I can't see what I am doing wrong. When I look in the database using the value in fullPath, the record exists. Here's my code:

        Song song = new Song();
        connectionManager = new ConnectionManager();
        try
        {
            using (SqlConnection conn = new SqlConnection(connectionManager.ConnectionString))
            {
                conn.Open();
                string query = $"SELECT * FROM Songs WHERE FullPath LIKE '@FullPath%'";
                using (SqlCommand queryString = new SqlCommand(query, conn))
                {
                    queryString.Parameters.Add("@FullPath", SqlDbType.NVarChar, 300).Value = fullPath;
                    SqlDataReader rdr = queryString.ExecuteReader();
                    if (!rdr.Read())
                    {
                        throw new InvalidOperationException("No records were returned.");
                    }
                    song.Title = rdr["Title"].ToString();
                    song.Artist = rdr["Artist"].ToString();
                    song.Genre = rdr["Genre"].ToString();
                    song.Album = rdr["Album"].ToString();
                    song.Year = (uint)rdr["Year"];
                    song.Length = rdr["Length"].ToString();
                    song.FullPath = rdr["FullPath"].ToString();
                }
                conn.Close();
            }
        }
        catch (Exception ex)
        {
            Logger.log.Error($"Error getting song: {fullPath}\n", ex);
        }
        return song;

CodePudding user response：

The @FullPath placeholder for the parameter is not included in the SQL string correctly. When you use parameters, you do not put them in single-quotes. You want this:

string query = "SELECT * FROM Songs WHERE FullPath LIKE @FullPath   '%'";

Alternatively, you could do this:

string query = "SELECT * FROM Songs WHERE FullPath LIKE @FullPath";
// ...
queryString.Parameters.Add("@FullPath", SqlDbType.NVarChar, 300).Value = fullPath   "%";

Remember, parameterized queries are more than a simple sanitization string substitution. Rather, it quarantines the data inside a variable. Therefore the SQL code must treat the placeholder as an SQL variable. What you had was just a string literal where the value happened to match the parameter name.

That's as far as I got. There may be other errors, too. If you want better help, post the actual error message.