locked
create simply search SQL statement RRS feed

  • Question

  • User-464009799 posted

    Hi

    i have two tables UserInfo which is for user info and ads where users added their ads into, so from the below statement i want to show records from UserInfo where result value which is query string equal or match BizCateg, BizSubCateg columns values into UserInfo and check also into Wtags from ads table so if the BizCateg or BizCateg, and Wtags (from ads)  has values equal to result then show the records. (UserInfo and ads has shared column which is UID so each record into ads related to UID into UserInfo).

    The statement below is not working very well i mean id not showing exactly what i am looking for 

            case "Business Directory":
    
                                if (Request.QueryString["Searchfor"] != null)
                                {
    
                                    using (SqlConnection srcbizhsql = new SqlConnection(sc))
                                    {
    
                                        srcbizhsql.Open();
                                        SqlDataAdapter DAsearchbiz = new SqlDataAdapter(@"SELECT 
    UI.[UID]
    ,UI.[Country]
    ,UI.[State]
    ,UI.[City]
    ,UI.[Logo]
    ,UI.[Website]
    ,UI.[UsrType]
    ,UI.[BizCateg]
    ,UI.[BizSubCateg]
    ,UI.[Twitter]
    ,UI.[GooglePlus]
    ,UI.[Facebook]
    ,UI.[CompNme]
    ,SUBSTRING([CompDesc],1,40) AS CompDesc
    
    ,AD.[Wtags] 
    
    FROM UserInfo AS UI , ads AS AD
    WHERE UI.[Country]= @Location AND UI.[UsrType]= 'Business' AND CHARINDEX(@Wtag, AD.[Wtags] )>0 ORDER BY UI.[RegDate] DESC ", sc);
    
                                        DataSet DSsrchBiz = new DataSet();
    
                                        DAsearchbiz.SelectCommand.Parameters.AddWithValue("@Location", cookie.Value);
                                        DAsearchbiz.SelectCommand.Parameters.AddWithValue("@Wtag", result);
    
                                        DAsearchbiz.Fill(DSsrchBiz);
                                        SrchbizHomLstviw.DataSource = DSsrchBiz.Tables[0];
                                        SrchbizHomLstviw.DataBind();
                                        SrchMultiView.ActiveViewIndex = 1;
                                    }
                                }
    
                                break;

    Thursday, April 21, 2016 11:51 PM

Answers

  • User-219423983 posted

    Hi msimo,

    with one problem if i write into search box "Toyota" which will be value of result (query string) then it will show many records depending of how many time the user added Ads with word Toyota where it suppose to show once even if UID has many Ads with the same result (query string) value.

    If you want to show only once for different UID based on the value of “result” in your above SQL string, I suggest you could use “select distinct UID, …” to achieve it. If not, please let me know.

    Best Regards,

    Weibo Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 22, 2016 8:38 AM

All replies

  • User-219423983 posted

    Hi msimo,

    msimo

    UserInfo and ads has shared column which is UID so each record into ads related to UID into UserInfo

    I think maybe you’d better change the “where clause” in your SQL string as below to combine these two tables.

    WHERE UI.UID = AD.UID and UI.[Country]= @Location AND UI.[UsrType]= 'Business' AND CHARINDEX(@Wtag, AD.[Wtags] )>0 ORDER BY UI.[RegDate] DESC

    Besides, when you meet with this similar issue later, you could first copy this code to your SQL Server to check where causes the errors. It's better for you to solve the issues quickly.

    Best Regards,

    Weibo Zhang

    Friday, April 22, 2016 2:12 AM
  • User-464009799 posted

    Hi @Weibo Zhang

    Thanks for reply , and your solution is working but with one problem if i write into search box "Toyota" which will be value of result (query string) then it will show many records depending of how many time the user added Ads with word Toyota where it suppose to show once even if UID has many Ads with the same result (query string) value.

    Friday, April 22, 2016 2:45 AM
  • User-219423983 posted

    Hi msimo,

    with one problem if i write into search box "Toyota" which will be value of result (query string) then it will show many records depending of how many time the user added Ads with word Toyota where it suppose to show once even if UID has many Ads with the same result (query string) value.

    If you want to show only once for different UID based on the value of “result” in your above SQL string, I suggest you could use “select distinct UID, …” to achieve it. If not, please let me know.

    Best Regards,

    Weibo Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 22, 2016 8:38 AM