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)
精彩评论