开发者

ASP.net SQL Query Problem using Repeater

This is my query I have in a Stored Procedure. I am using a repeater to display the information. The problem is if I search for lets say

Ename = Jim

ELocation = Smith Center

ECity = Atlanta

through the query builder, I get the two results that I have that matches that. But when I bind my data source to the repeater and add the parameters, then try to run the query my repeater is empty. The variables are passed in from Textbox Controls. Also if 开发者_StackOverflow中文版I only pass in one variable, say like title, it works just fine. But when I try to pass in two or more variables I get nothing. Anyone have any ideas on what to do?

    @title varchar(150),
    @venue varchar(150),
    @city varchar(100),
    @state varchar(50),
    @country varchar(100),
    @desc varchar(150),
    @date smalldatetime = null
AS

SELECT     EID, EName, EDate, EDEnd, ELocation, ECity, EState, EDesc, EWebsite
FROM         esc
WHERE     (@title IS NULL OR EName LIKE '%' + @title + '%') 
           AND (@venue IS NULL OR ELocation LIKE '%' + @venue + '%')
           AND (@city IS NULL OR ECity LIKE '%' + @city + '%') 
           AND (@state IS NULL OR EState LIKE '%' + @state + '%') 
           AND (@country IS NULL OR ECountry = @country) 
           AND (@desc IS NULL OR EDesc LIKE '%' + @desc + '%') 
           AND (@date IS NULL OR EDate = @date)

Condensed Code when button is clicked.:

        SqlConnection conn = null;
        try
        {
            conn = new SqlConnection("");
            SqlCommand command = new SqlCommand();
            command.Connection = conn;

            command.CommandText = "seesc";
            command.CommandType = CommandType.StoredProcedure;

            SqlParameter title = new SqlParameter();
            title.ParameterName = "@title";
            title.SqlDbType = SqlDbType.VarChar;
            title.Direction = ParameterDirection.Input;
            title.Value = TitleTextBox.Text;

            //other parameters declared here

                command.Parameters.Add(title);
                //other parameters added here

                conn.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    evrep.DataSource = reader;
                    evrep.DataBind();
                }
        }
        catch { }


There is some typo in the query itself.

       AND (@city IS NULL OR ECity LIKE '+' + @city + '%') 
       AND (@state IS NULL OR EState LIKE '+' + @state + '%')

should be

       AND (@city IS NULL OR ECity LIKE '%' + @city + '%') 
       AND (@state IS NULL OR EState LIKE '%' + @state + '%')

In addition when

       title.Value = TitleTextBox.Text;

is used the parameter would not be null but would have value of empty string. That would fail the condition

       AND (@country IS NULL OR ECountry = @country) 
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜