- 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:
<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',
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>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService]
public class VendorService : System.Web.Services.WebService {
public VendorService () {
}
[WebMethod]
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));
}
}
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