hi iam trying to search with stored procedure with like statment but i have a problem when i type  something like "shamss"   any word that contains s appear but what i want when i type "hello world" what appears to me all words that habe hello and that have world any help please that's my code

 ALTER PROCEDURE [dbo].[GetPagedProducts]
(
    @PageSize int,
    @CurrentPage int,
    @CategoryID int,
    @CompanyName nvarchar,
    @ItemCount int output
)
AS
    Declare @UpperBand int, @LowerBand int
    
    -- Get The Count Of The Rows That They Meet the Criteria
    SET @ItemCount = (SELECT COUNT(*) FROM Companies  WHERE CompanyName LIKE '%' + @CompanyName  + '%'  AND CategoryID = @CategoryID)

    -- Calculate the @LowerCount and @UpperCount
    SET @LowerBand  = (@CurrentPage - 1) * @PageSize
    SET @UpperBand  = (@CurrentPage * @PageSize) + 1

    -- create a temporaty table
    CREATE TABLE #AllRows(
    RowID int PRIMARY KEY IDENTITY(1, 1),
    CompanyName nvarchar,
    CategoryID int,
    CompanyLogo ntext,
    CompanyID int
    )

    -- INSERT ALL THE Rows that meets the Criteria
    INSERT INTO #AllRows
    SELECT CompanyName, CategoryID, CompanyLogo, CompanyID
    FROM Companies WHERE CompanyName  LIKE '%' + @CompanyName + '%' AND CategoryID = @CategoryID
    ORDER BY  CompanyName DESC

    -- AND finally select and return desired -Paged- Rows
    SELECT RowID, CompanyName, CategoryID, CompanyLogo, CompanyID
    FROM #AllRows
    WHERE RowID > @LowerBand AND RowID < @UpperBand AND CompanyName  LIKE '%' + @CompanyName + '%' AND CategoryID = @CategoryID
    ORDER BY  CompanyName DESC

    RETURN

my aspx page

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class Search : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Request.QueryString["Keyword"] == null || Request.QueryString["Category"] == null)
        {
            //Response.Redirect("Default.aspx");
        }
        if (!IsPostBack)
        {

            pager1.CurrentIndex = 1;
            BindRepeater(1);
            if (pager1.ItemCount == 0)
            {
                pager1.Visible = false;
                lblError.Text = "Sorry there is no records found";
            }
            else
            {
                lblLogo.Text = "<font color='#990033'> Goooooooo</font><font color='#000000'>Web</font>";
            }

        }
    }
   
    protected string strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    public void pager_Command(object sender, CommandEventArgs e)
    {
        int currnetPageIndx = Convert.ToInt32(e.CommandArgument);
        pager1.CurrentIndex = currnetPageIndx;
        BindRepeater(currnetPageIndx);
    }

    private void BindRepeater(int pageNo)
    {

        try
        {
            SqlConnection cn = new SqlConnection(strConn);

            SqlCommand Cmd = new SqlCommand("dbo.GetPagedProducts", cn);
            Cmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader dr;


            Cmd.Parameters.Add("@PageSize", SqlDbType.Int, 4).Value = pager1.PageSize;
            Cmd.Parameters.Add("@CurrentPage", SqlDbType.Int, 4).Value = pageNo;
            Cmd.Parameters.Add("@ItemCount", SqlDbType.Int).Direction = ParameterDirection.Output;
            Cmd.Parameters.Add("@CategoryID", SqlDbType.Int).Value = Request.QueryString["CategoryID"];
            Cmd.Parameters.Add("@CompanyName", SqlDbType.NVarChar).Value = Request.QueryString["CompanyName"].ToString();
            cn.Open();
            dr = Cmd.ExecuteReader();

            DataList1.DataSource = dr;
            DataList1.DataBind();

            dr.Close();
            cn.Close();

            Int32 _totalRecords = Convert.ToInt32(Cmd.Parameters["@ItemCount"].Value);
            pager1.ItemCount = _totalRecords;
        }
        catch (Exception ex) { lblError.Text = ex.Message.ToString(); }//"sorry an error was occured"; }
     
    }
    protected void btnSearch_Click(object sender, EventArgs e)
    {
        string Keyword = txtSearch.Text;
        string Category = ddlCat.SelectedItem.Value;
        Response.Redirect("Search.aspx?CompanyName=" + Keyword + "&CategoryID=" + Category);
    }
}
 

any help please

thankss

hi,

just reply ...

1) if u type  ....  H then ..result as  Startwith 'H' ..... like Hello , Hai, Hi , Hour....etc ... ?

or

2) if u type    H then ...... resulr as  within  'H' ,,,,, like     BHEL , NHL , AHL, whatisthis ....?

or

3) if u type Hello then ...result as with 'HELLO' .... like    TestHELLOtis, whathellotis... ?   ( NOTE : its used in ur query ....)

which one .. plz mention

4) from ur Code.....(stored procedure) ..

.....the query Only select  records based on ur word given like     .....if u give input (keyword) as ..... "TEST",,,,,,,,,then

....  the step no :3 .....

 what i want when i type hello apear to me  all words that have hello

like

3) if u type Hello then ...result as with 'HELLO' .... like    TestHELLOtis, whathellotis... ?   ( NOTE : its used in ur query ....)

i want number 3 

but when i get the result in search i get all words that have h and o what can i do

please help

hi,

just .... Assign the size for the Nvarchar ...

ie

    @PageSize int,
    @CurrentPage int,
    @CategoryID int,
    @CompanyName nvarchar(10),       or  u need
    @ItemCount int output