locked
About querying for search page RRS feed

  • Question

  • User-1865478366 posted

    [IMG]http://i141.photobucket.com/albums/r49/xBonic/pic.jpg[/IMG]

    The above is a picture of the search page, the problem is when i tried to key in any one box lets say i key in 0 for quantity it will display out the whole list of products from my product table. Goes the same for price and key word.

    How to change to set it just search for whatever i keyed in ?

    Below is the code of the page

    public partial class ProductSearch : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            // Do not remember data of previous view
            Page.EnableViewState = false;
           
          
        }
        protected void btnSearch_Click(object sender, EventArgs e)
        {
           
            dbMgmt objDbMgmt = new dbMgmt();
            string strSqlCmd;
            System.Data.OleDb.OleDbDataReader objDataReader;
           
            // Connect to the database
            objDbMgmt.Connect();

            // Define SQL statement to perform searching of records.
         

            //    Select products from the database where the product name
            //    contains the search text.
            //    The products are ordered by product name.
            strSqlCmd = "SELECT DISTINCT p.ProductID, p.ProductTitle, p.Quantity, p.P " + "FROM Product p INNER JOIN ProductAttribute pa " + "ON p.ProductID=pa.ProductID " + "WHERE (p.ProductTitle LIKE '%" + txtProductTitle.Text + "%')" + "OR (p.Quantity LIKE '%" + txtQuantity.Text + "%')" + "OR (p.Price LIKE '%" + txtPrice.Text + "%')" + "OR (p.Price LIKE '%" + txtPrice2.Text + "%')" + "ORDER BY p.ProductTitle";
           
            objDataReader = objDbMgmt.ExecuteReader(strSqlCmd);

            if (objDataReader.HasRows)
            {
                dgSearch1.DataSource = objDataReader;

                // Bind the data to the data grid control
                // The data grid control will display the departments
                // available in the store.
                dgSearch1.DataBind();
         
            }
           
            else
            {
               
                lblMsg3.Text = "No product matches your search string!";
            }
            objDbMgmt.Close();

           
        }
        protected void btnAdvanced_Click(object sender, EventArgs e)
        {
            Label2.Visible = true;
            Label3.Visible = true;
            Label4.Visible = true;
            txtQuantity.Visible = true;
            txtPrice.Visible = true;
            txtPrice2.Visible = true;
            btnAdvanced.Visible = false;
            btnHide.Visible = true;
        }
        protected void btnHide_Click(object sender, EventArgs e)
        {
            Label2.Visible = false;
            Label3.Visible = false;
            Label4.Visible = false;
            txtQuantity.Visible = false;
            txtPrice.Visible = false;
            txtPrice2.Visible = false;
            btnAdvanced.Visible = true;
        }
    }

    Wednesday, August 4, 2010 6:22 AM

Answers

  • User-1199946673 posted

    This works on Access database but does not work in visual studio how

    Of course in it works in Cisual studio also, but you need to replace txtPrice.Text and txtPrice2.Text with parameters

    Did you read Mike' article? Also, read another of Mike's articles...

    http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

    Also note that the like operator doesn't work with numeric columns.... 

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, August 7, 2010 11:05 AM

All replies

  • Wednesday, August 4, 2010 7:24 AM
  • User-1865478366 posted

    What if i'm doing for price range with:

    SELECT p.ProductID, p.ProductTitle FROM Product p INNER JOIN ProductAttribute pa ON p.ProductID=pa.ProductID WHERE (p.Price >= txtPrice.Text) AND (p.Price <= txtPrice2.Text) ORDER BY p.ProductTitle;

    This works on Access database but does not work in visual studio how to modify to search lets say price range of 15 to 20?

     

    Wednesday, August 4, 2010 7:34 AM
  • User-1199946673 posted

    This works on Access database but does not work in visual studio how

    Of course in it works in Cisual studio also, but you need to replace txtPrice.Text and txtPrice2.Text with parameters

    Did you read Mike' article? Also, read another of Mike's articles...

    http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

    Also note that the like operator doesn't work with numeric columns.... 

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, August 7, 2010 11:05 AM
  • User-1865478366 posted

    i did modify to replace to -> Price IS NULL OR BETWEEN " + txtPrice.Text +  " AND"+ txtPrice2.Text

    Sunday, August 8, 2010 1:44 AM
  • User-1199946673 posted

    i did modify to replace to -> Price IS NULL OR BETWEEN " + txtPrice.Text +  " AND"+ txtPrice2.Text

     

    That great, but obviously you ddin't read the articles I referred to. That's fine by me if you don't want to use parameterized queries, but then I give up, good luck...

    Sunday, August 8, 2010 5:47 AM