locked
search the record RRS feed

  • Question

  • User-1117760597 posted

    hello everyone, i have an .aspx page with one textbox and button for search.when user enter some text into textbox and clicl on button an javascript will replace each space with + and an vb back code will store each string before + into seprate array possition for example:-

    suppose user enter microsoft windows

    then javascript will convert it into microsoft+windows

    then in back code(VB) there is an string array and an code which will seprate microsoft+windows like str(0)=microsoft,str(1)=windows

    NOW MY PROBLEM IS

    i want to search the seprated string like str(0),str(1)....str(n) into sql database.?

    IN SHORT I WANT TO MAKE SEARCH OPTION WHICH WILL TAKE SEARCH KEYWORD FROM USER IF IT HAS MULTIPLE WORD THEN SEPRATE IT AS MENTION ABOVE(microsoft+windows) AND THAT EACH WORD WILL COMPARE INTO WHOLE DATABASE IF ANY WORD WILL FOUND EQUAL THAT WORD THEN CURRENT ROW IS DISPLAYED? 

    Saturday, June 4, 2011 8:06 PM

Answers

  • User-744022866 posted

    Once you have an array of search words, str, you can construct a query that matches any of the given word..

    see the below sample

    strSql = "select * from table "

    strWhere = string.Empty;

    for each keyword as string in str

         if strWhere = string.Empty then

           strWhere += "where columnname='" + keyword + "'"

         else

           strWhere += "or columnname='" + keyword + "'"

         end if

    next

    strsql += strWhere

    Now if you execute strSQl against your database, you will get results with any match..

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, June 5, 2011 12:04 AM
  • User-1117760597 posted

    hey sreejukg after posting the reply to you i again tried on ur code and now i can genrate the query as per my need.HERE IS THE CHANGE..

    str = "select * from buy_enquiry"
                strwhere = String.Empty
                For Each keyword As String In qarr
                    If strwhere = String.Empty Then
                        strwhere += " where name LIKE '%" + keyword + "%' or mobileno LIKE '%" + keyword + "%' or person_add LIKE  '%" + keyword + "%' or property_type LIKE '%" + keyword + "%' or unit LIKE '%" + keyword + "%' or location_property LIKE '%" + keyword + "%' or bhk_rate LIKE '%" + keyword + "%'"
                    Else
                        strwhere += " or name LIKE '%" + keyword + "%' or mobileno LIKE '%" + keyword + "%' or person_add LIKE '%" + keyword + "%' or property_type LIKE '%" + keyword + "%' or unit LIKE '%" + keyword + "%' or location_property LIKE '%" + keyword + "%' or bhk_rate LIKE '%" + keyword + "%'"
                    End If
                Next
                str += strwhere
                MsgBox(str)
                cmd = New SqlCommand(str, conn)
                conn.Open()
                buy_post.DataSource = cmd.ExecuteReader()
                buy_post.DataBind()
                conn.Close()


    THANK YOU VERY MUCH FOR UR QUICK HELP....u solved my prob

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, June 5, 2011 7:26 AM

All replies

  • User-744022866 posted

    Once you have an array of search words, str, you can construct a query that matches any of the given word..

    see the below sample

    strSql = "select * from table "

    strWhere = string.Empty;

    for each keyword as string in str

         if strWhere = string.Empty then

           strWhere += "where columnname='" + keyword + "'"

         else

           strWhere += "or columnname='" + keyword + "'"

         end if

    next

    strsql += strWhere

    Now if you execute strSQl against your database, you will get results with any match..

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, June 5, 2011 12:04 AM
  • User-1117760597 posted

    Once you have an array of search words, str, you can construct a query that matches any of the given word..

    see the below sample

    strSql = "select * from table "

    strWhere = string.Empty;

    for each keyword as string in str

         if strWhere = string.Empty then

           strWhere += "where columnname='" + keyword + "'"

         else

           strWhere += "or columnname='" + keyword + "'"

         end if

    next

    strsql += strWhere

    Now if you execute strSQl against your database, you will get results with any match..

    THANKS FOR UR QUICK REPLY.i tried this code and this will genrate the query correct but my database table have 8 columns and what i need is each keyword from the array str is compare with each column in DB table then matching record is displayed in label then move to next keyword so on upto last keyword in array str....

    PLzzzz buddy help me...

    Sunday, June 5, 2011 6:55 AM
  • User-1117760597 posted

    hey sreejukg after posting the reply to you i again tried on ur code and now i can genrate the query as per my need.HERE IS THE CHANGE..

    str = "select * from buy_enquiry"
                strwhere = String.Empty
                For Each keyword As String In qarr
                    If strwhere = String.Empty Then
                        strwhere += " where name LIKE '%" + keyword + "%' or mobileno LIKE '%" + keyword + "%' or person_add LIKE  '%" + keyword + "%' or property_type LIKE '%" + keyword + "%' or unit LIKE '%" + keyword + "%' or location_property LIKE '%" + keyword + "%' or bhk_rate LIKE '%" + keyword + "%'"
                    Else
                        strwhere += " or name LIKE '%" + keyword + "%' or mobileno LIKE '%" + keyword + "%' or person_add LIKE '%" + keyword + "%' or property_type LIKE '%" + keyword + "%' or unit LIKE '%" + keyword + "%' or location_property LIKE '%" + keyword + "%' or bhk_rate LIKE '%" + keyword + "%'"
                    End If
                Next
                str += strwhere
                MsgBox(str)
                cmd = New SqlCommand(str, conn)
                conn.Open()
                buy_post.DataSource = cmd.ExecuteReader()
                buy_post.DataBind()
                conn.Close()


    THANK YOU VERY MUCH FOR UR QUICK HELP....u solved my prob

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, June 5, 2011 7:26 AM
  • User-1199946673 posted

    You better use parameterized queries, something like:

    dim i as integer = 1 
    str = "select * from buy_enquiry where 1=0" 
    cmd = New SqlCommand(str, conn) 
    For Each keyword As String In qarr 
      cmd.CommandText += String.Format(" or name LIKE '%' + @keyword{0} + '%' or mobileno LIKE '%' + @keyword{0} + '%' or person_add LIKE '%' + @keyword{0} + '%' or property_type LIKE '%' + @keyword{0} + '%' or unit LIKE '%' + @keyword{0} + '%' or location_property LIKE '%' + @keyword{0} + '%' or bhk_rate LIKE '%' + @keyword{0} + '%'", i.ToString())  
      cmd.Parameters.AddWithValue(String.Format("keyword{0}", i.ToString()), keyword) 
      i += 1 
    Next  
    buy_post.DataSource = cmd.ExecuteReader()  
    buy_post.DataBind() 



    http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access
    http://www.mikesdotnetting.com/Article/68/An-ASP.NET-Search-Engine-with-MS-Access-for-optional-search-criteria

    Sunday, June 5, 2011 4:27 PM
  • User1867929564 posted

    I assume  you are using parameterized queries.
    (@param1,@param2,@param3,@param4,@param5,@param6,@param7,@param8)--8 columns's parameter
    Write query like, 
    don't use * in Select instead mention column name

    select col1,col2,col3 from buy_enquiry where ((@param1 is null) or (name=@param1)) or
     ((@param2 is null) or (mobile=@param2))
    and so on

    one sql query without if condition.also you can like only where neccessary.
     

     

     

    Monday, June 6, 2011 5:14 AM
  • User-1199946673 posted

    I assume  you are using parameterized queries.

    No, he's not...

    I think you're missing the point of what he's trying to do!

    Monday, June 6, 2011 2:08 PM
  • User-1117760597 posted

    I assume  you are using parameterized queries.

    No, he's not...

    I think you're missing the point of what he's trying to do!

    you are correct hans_v and thanks kumarharsh for replying.

    hans_v i need more help from you if you don't mind.plz go to below link and help to fix the problem:

    http://forums.asp.net/p/1687213/4448198.aspx/1?p=True&t=634430112246200950

    i m geting this error after uploading the asp.net 4.0 website on the webserver

    Tuesday, June 7, 2011 2:45 AM