在T-SQL LIKE与ASP.net参数通配符 [英] Wildcards in T-SQL LIKE vs. ASP.net parameters

问题描述

在我的SQL语句，我使用通配符。但是，当我尝试选择一些东西，它永远不会选择一些东西。而当我在Microsoft SQL Server Management Studio中执行查询，它工作正常。
我在做什么错了？

点击处理程序

 保护无效btnTitelAuteur_Click（对象发件人，EventArgs的发送）
{
    cvalTitelAuteur.Enabled = TRUE;
    cvalTitelAuteur.Validate（）;    如果（Page.IsValid）
    {
        objdsSelectedBooks.SelectMethod =getBooksByTitleAuthor;
        objdsSelectedBooks.SelectParameters.Clear（）;
        objdsSelectedBooks.SelectParameters.Add（新参数（称号，DbType.String））;
        objdsSelectedBooks.SelectParameters.Add（新参数（作者，DbType.String））;
        objdsSelectedBooks.Select（）;
        gvSelectedBooks.DataBind（）;        pnlZoeken.Visible = FALSE;
        pnlKiezen.Visible =真;
    }
}
 

在我的数据访问层

 公共静态数据表getBooksByTitleAuthor（字符串名称，字符串作者）
{    字符串SQL =SELECT作者= 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'％@标题％'）;;    SqlDataAdapter的大=新SqlDataAdapter的（SQL，GetConnectionString（））;
    da.SelectCommand.Parameters.Add（@标题，SqlDbType.Text）;
    da.SelectCommand.Parameters [@标题]值=称号。    DataSet的DS =新的DataSet（）;
    da.Fill（DS，书）;    返回ds.Tables [书籍];
}
 

解决方案

试试这个：

  SQL字符串=选择作者= 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（LIKE tblBooks.TITLE @title）;;SqlDataAdapter的大=新SqlDataAdapter的（SQL，GetConnectionString（））;
da.SelectCommand.Parameters.Add（@标题，SqlDbType.Text）;
。da.SelectCommand.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"];
}

解决方案

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 + "%";

这篇关于在T-SQL LIKE与ASP.net参数通配符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！