CODEWITHSUNDEEP
c#asp.netsqlsql-server
Vinzcent
Vinzcent

Reputation: 1448

Wildcards in T-SQL LIKE vs. ASP.net parameters

In my SQL statement I use wildcards. But when I try to select something, it never select something. While when I execute the query in Microsoft SQL Server Management Studio, it works fine. What am I doing wrong?

Click handler

protected void btnTitelAuteur_Click(object sender, EventArgs e)
{
    cvalTitelAuteur.Enabled = true;
    cvalTitelAuteur.Validate();

    if (Page.IsValid)
    {
        objdsSelectedBooks.SelectMethod = "getBooksByTitleAuthor";
        objdsSelectedBooks.SelectParameters.Clear();
        objdsSelectedBooks.SelectParameters.Add(new Parameter("title", DbType.String));
        objdsSelectedBooks.SelectParameters.Add(new Parameter("author", DbType.String));
        objdsSelectedBooks.Select();
        gvSelectedBooks.DataBind();

        pnlZoeken.Visible = false;
        pnlKiezen.Visible = true;
    }
}

In my Data Access Layer

public static DataTable getBooksByTitleAuthor(string title, string author)
{

    string sql = "SELECT 'AUTHOR' = tblAuthors.FIRSTNAME + ' ' + tblAuthors.LASTNAME, tblBooks.*, tblGenres.GENRE "
                + "FROM tblAuthors INNER JOIN tblBooks ON tblAuthors.AUTHOR_ID = tblBooks.AUTHOR_ID INNER JOIN tblGenres ON tblBooks.GENRE_ID = tblGenres.GENRE_ID "
                +"WHERE (tblBooks.TITLE LIKE '%@title%');";

    SqlDataAdapter da = new SqlDataAdapter(sql, GetConnectionString());
    da.SelectCommand.Parameters.Add("@title", SqlDbType.Text);
    da.SelectCommand.Parameters["@title"].Value = title;

    DataSet ds = new DataSet();
    da.Fill(ds, "Books");

    return ds.Tables["Books"];
}

Upvotes: 1

Views: 4883

Answers (3)

John Saunders
John Saunders

Reputation: 161773

The answer from John Allers is correct. As an aside, you should wrap the SqlDataAdapter in a using block:

using (SqlDataAdapter da = new SqlDataAdapter(sql, GetConnectionString()))
{
    da.SelectCommand.Parameters.Add("@title", SqlDbType.Text);
    da.SelectCommand.Parameters["@title"].Value = title;

    DataSet ds = new DataSet();
    da.Fill(ds, "Books");

    return ds.Tables["Books"];
}

Upvotes: 1

John Allers
John Allers

Reputation: 3122

Try this:

string sql = "SELECT 'AUTHOR' = tblAuthors.FIRSTNAME + ' ' + tblAuthors.LASTNAME, tblBooks.*, tblGenres.GENRE "
           + "FROM tblAuthors INNER JOIN tblBooks ON tblAuthors.AUTHOR_ID = tblBooks.AUTHOR_ID INNER JOIN tblGenres ON tblBooks.GENRE_ID = tblGenres.GENRE_ID "
           +"WHERE (tblBooks.TITLE LIKE @title);";

SqlDataAdapter da = new SqlDataAdapter(sql, GetConnectionString());
da.SelectCommand.Parameters.Add("@title", SqlDbType.Text);
da.SelectCommand.Parameters["@title"].Value = "%" + title + "%";

Upvotes: 6

Joel Coehoorn
Joel Coehoorn

Reputation: 415790

You can't include your query parameter inside a string literal. Do it like this instead:

WHERE (tblBooks.TITLE LIKE '%' + @title + '%');

Also, whenever you have a leading wildcard you should look into a full text index instead. Your query as written is doomed to be much slower than it could be, because you can't use index when you have a leading wild card.

Upvotes: 1

Related Questions