Click here to Skip to main content
15,891,863 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am creating SearchVendor page with 3 dropdown filter(city/area/vendortype).On page scroll it should display related vendors filtered by the user.
To get above result I have created
1)Storedprocedure(pageNumber/pageSize/city/area/vendortype)- Which should return all data based on combination of filter selected by the user. If none of the filter is selected then it should return all data i.e vendor.
2)Webservice(pageNumber/pageSize/city/area/vendortype) - to get the data from the database
3)Ajax Jquery - For page scroll logic and to pass parameters to WebService.

My problem is
I am getting correct data when all 3(city/area/vendortype) options is selected.But its not showing any result when one of options(
city/area/vendortype
) is not selected.

What I have tried:

HTML
<script type="text/javascript">
        $(document).ready(function () {
            var currentPageNumber = 1;
            loadData(currentPageNumber);

            $(window).scroll(function () {
                if ($(window).scrollTop() == $(document).height() - $(window).height()) {

                    currentPageNumber += 1;
                    loadData(currentPageNumber);
                }

            });

            function loadData(currentPage) {
                var parcity=$("#lblCityParameter").val();
                var pararea = $("#lblAreaParameter").val();
                var parvendortype = $("#lblVendortype").val();
                $.ajax({
                    type: 'post',
                    url: 'WebService/VendorService.asmx/GetVendors',
                    data: { pageNumber: currentPage, pageSize: 6, city:parcity, area:pararea, vendortype:parvendortype },
                   dataType: 'json',
                   //contentType: "application/json; charset=utf-8",
                     success: function (data) {
                      var vendorRepeater = $('#repdata');
                      $(data).each(function (index, ven) {
                         
                            vendorRepeater.append('<div class="col-md-4 wow fadeInLeft animated" data-wow-delay="0.4s" style="text-align:center"><div id="Div1" class="living_boxauto" runat="server"><a href="/KB/answers/VendorDetails.aspx"?VendorId=' + ven.pID + '"><div class="media"><img runat="server" class="img-responsive" src="images/'
                                + ven.pcoverimage +
                                '" style="width:300px;height:200px;" /><div class="media__body"><h2>Image Title</h2><p>Description</p></div></div></a><div id="Div2" class="living_desc" runat="server"><h3><a href="/KB/answers/VendorDetails.aspx"?VendorId='
                                + ven.pID +
                                '"><asp:Label ID="lblVendorName" runat="server" Text="'
                                + ven.pvendorname +
                                '" CssClass="lblVendorName"></asp:Label></a></h3><p></p></div></div></div>');
                        });
                    }
                });

            }

        });

    </script>


C#
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
[System.Web.Script.Services.ScriptService]
public class VendorService : System.Web.Services.WebService {

    public VendorService () {

        //Uncomment the following line if using designed components 
        //InitializeComponent(); 
    }


    //   public void GetVendors(int pageNumber, int pageSize)
    [WebMethod]
 //  [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
  public void GetVendors(int pageNumber, int pageSize, string city, string area, string vendortype)
  {
        List<VendorData> listVendors = new List<VendorData>();

        string cs = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        using (SqlConnection con = new SqlConnection(cs))
        {
            SqlCommand cmd = new SqlCommand("spGetVendorbyFilter", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@PageNumber", pageNumber);
            cmd.Parameters.AddWithValue("@PageSize", pageSize);
            
            cmd.Parameters.AddWithValue("@city", city);
            cmd.Parameters.AddWithValue("@area", area);
            cmd.Parameters.AddWithValue("@vendortype", vendortype);
            

            con.Open();
            SqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                VendorData vendor = new VendorData();
                vendor.pID = Convert.ToInt32(rdr["VendorID"]);
                vendor.pvendorname = Convert.ToString(rdr["VendorName"]);
                vendor.pcoverimage = Convert.ToString(rdr["CoverImage"]);
                vendor.pcategory = Convert.ToString(rdr["Category"]);
                listVendors.Add(vendor);

            }
        }

        JavaScriptSerializer js = new JavaScriptSerializer();
        Context.Response.Write(js.Serialize(listVendors));
    }
    
}



SQL
ALTER PROCEDURE [dbo].spGetVendorbyFilter 
                      @PageNumber INT,
                      @PageSize   INT,
                      @city       VARCHAR(200),
                      @area       VARCHAR(200),
                      @vendortype VARCHAR(200)
AS
  BEGIN
      DECLARE @StartRow INT
      DECLARE @EndRow INT

      SET @StartRow = ( ( @PageNumber - 1 ) * @PageSize ) + 1;
      SET @EndRow= @PageNumber * @PageSize;



WITH Result
           AS (
               SELECT *,
                      Row_number()
                        OVER (
                          ORDER BY VendorID ASC) RowNumber
               FROM   tblVendor WHERE (@city IS NULL OR (City = @city))
                 AND  (@area IS NULL OR (Area = @area))
                 and  (@vendortype IS NULL OR (Category = @vendortype))
               
              ) 
                  
     Select  * from Result
     WHERE RowNumber BETWEEN @StartRow And @EndRow

END
Posted
Updated 21-Dec-16 0:37am
v2
Comments
F-ES Sitecore 21-Dec-16 4:05am    
You don't need to use JSON.stringify on the data variable, it is already a string. Google for how you send data to a webservice via jquery, it's a very well documented issue.
Member 12915152 21-Dec-16 6:36am    
@fes-sitecore -Thanks for reply. I have updated my query. Could you please look into it.
F-ES Sitecore 21-Dec-16 6:47am    
The "(@area IS NULL OR (Area = @area))" clause is correct, but you're never passing NULL to @area, if the param has not been supplied you are passing an empty string and SQL does not consider that NULL. Give the params default values then simply omit the values for data you don't have

the sp;
@city VARCHAR(200) = null,
@area VARCHAR(200) = null,
@vendortype VARCHAR(200) = null

then change the code to something like

if (!string.IsNullOrWhiteSpace(city))
{
cmd.Parameters.AddWithValue("@city", city);
}
Tomas Takac 21-Dec-16 6:34am    
You need to use the "Reply" button otherwise the user won't be notified of your comment.
Member 12915152 21-Dec-16 6:37am    
Thanks Tomas Takac.

1 solution

You have not explicitly passing NULL for the non selection.
Check this out: Passing NULL Parameters | T-SQL content from SQL Server Pro[^]
 
Share this answer
 
Comments
Member 12915152 21-Dec-16 7:09am    
Thanks Peter Leow. Your Answer is just awesome. It really working for me.

I have added below code to my webmethod.


if (city !="")
{
cmd.Parameters.AddWithValue("@city", city);

}
else
{
cmd.Parameters.AddWithValue("@city", DBNull.Value);
}

if (area !="")
{
cmd.Parameters.AddWithValue("@area", area);

}
else
{
cmd.Parameters.AddWithValue("@area", DBNull.Value);
}
if (vendortype !="")
{
cmd.Parameters.AddWithValue("@vendortype", vendortype);

}
else
{
cmd.Parameters.AddWithValue("@vendortype", DBNull.Value);
}

and minor change to stored procedure.

@city VARCHAR(200)= NULL,
@area VARCHAR(200)= NULL,
@vendortype VARCHAR(200)= NULL

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900