System.IndexOutOfRangeException: - Please Help!!!
I am using a stored procedure to fill a datagrid and i keep coming up with this error please help me....my code below;
CODE BEHIND
#################
Dim rs As SqlDataReader = _search.getBasicResults()
If rs.Read() Then
ResultsPagerTop.TotalRecords = rs(0)
ResultsPagerBottom.TotalRecords = ResultsPagerTop.TotalRecords
End If
rs.NextResult()
Dim otherResults As New ArrayList
While rs.Read()
otherResults.Add(New JobBoardResult(rs("EmployerID"), rs("Name"), rs("NumVacancies"), rs("smallLogoPath")))
End While
rs.NextResult()
dtgResults.DataSource = rs
dtgResults.DataBind()
dtgOtherResults.DataSource = otherResults
dtgOtherResults.DataBind()
rs.Close()
If ResultsPagerTop.TotalRecords = 0 Then
pnlSort.Visible = False
End If
STORED PROCEDURE
################
CREATE PROCEDURE spSearchVacancies
(
@countyID as int = null,
@townID as int = null,
@regionID as int = null,
@contractTypeID as int = null,
@keywords as varchar(100) = null,
@minsalary as money =null,
@maxsalary as money =null,
@sectorID as int = null,
@employerID as int = null,
@userID as int = null,
@jobRef as varchar(100) = '',
@positionTitle as varchar(100) = '',
@PageIndex int = 0,
@PageSize int = 20,
@OrderedBy nvarchar(50) = 'datePosted DESC'
)
AS
If @regionID is not null and @countyID is not null
set @regionID = null
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
/*Create a table to hold the first result set*/
CREATE TABLE #vacancy
(
vacancyID int NOT NULL,
score int,
employerID int,
membershipTypeID int
)
DECLARE @min_score int
SET @min_score = 10
/*Get the vacancy ids of those that fit the criteria*/
INSERT INTO #vacancy
SELECT distinct v.vacancyID,10, v.EmployerID, e.MembershipTypeID FROM Vacancy v
inner join ContractType ON (v.contractTypeID = ContractType.contractTypeID)
inner join Employer E on(v.EmployerID = E.EmployerID)
left outer join VacancyRegion vr on(vr.vacancyid = v.vacancyid)
left outer join VacancyCounty vc ON(vc.vacancyid = v.vacancyid)
left outer join County c ON (vc.countyID = c.countyID)
left outer join VacancyTown vt on(vt.vacancyid = v.vacancyid)
left outer join Town T on(t.townid = vt.townid)
left outer join VacancySector vs on(vs.vacancyid = v.vacancyid)
left outer JOIN Sector s ON (vs.sectorID = s.sectorID)
WHERE (vr.RegionID = @regionid or @regionid is null)
AND (vc.countyID = @countyid or @countyid is null)
AND (vt.townID = @townid or @townid is null)
AND (vs.sectorID = @sectorid or @sectorid is null)
AND v.contractTypeID = COALESCE(@contractTypeID, v.contractTypeID)
AND salary >= COALESCE(@minsalary, salary) and salary <= COALESCE(@maxsalary,salary)
AND v.employerID= COALESCE (@employerID, v.employerID)
AND v.userID = COALESCE(@userID,v.userID)
AND jobRef like '%' + @jobRef + '%'
AND positionTitle like '%' + @positionTitle + '%'
/*Score the results we have got according their matches with any specifed keywords*/
DECLARE @singleWord varchar(100)
DECLARE @start int
SET @start = 0
DECLARE @position int
if(@keywords IS NOT NULL and @keywords <> '')
BEGIN
SET @min_score = 30 /*if a keyword is entered, something must be found*/
SELECT @position = CHARINDEX(',', @keywords)
IF(@position = 0) SET @position = LEN(@keywords)
WHILE @start < @position
BEGIN
SET @singleWord = SUBSTRING(@keywords, @start, @position+1)
SET @start = @position + 1
UPDATE #vacancy SET score = score + 50 from #Vacancy vtemp
inner join Vacancy v on(v.vacancyid = vtemp.vacancyid) where jobRef like '%' + @singleWord + '%'
UPDATE #vacancy SET score = score + 50 from #Vacancy vtemp
inner join Vacancy v on(v.vacancyid = vtemp.vacancyid) where location like '%' + @singleWord + '%'
UPDATE #vacancy SET score = score + 50 from #Vacancy vtemp
inner join Vacancy v on(v.vacancyid = vtemp.vacancyid) where positionTitle like '%' + @singleWord + '%'
UPDATE #vacancy SET score = score + 30 from #Vacancy vtemp
inner join Vacancy v on(v.vacancyid = vtemp.vacancyid) where dbo.fnGetVacancySectors(vtemp.vacancyid) + ' ' + v.skillsText like '%' + @singleWord + '%'
UPDATE #vacancy SET score = score + 20 from #Vacancy vtemp
inner join Vacancy v on(v.vacancyid = vtemp.vacancyid) where dbo.fnGetVacancySkill(vtemp.vacancyid) like '%' + @singleWord + '%'
UPDATE #vacancy SET score = score + 20 from #Vacancy vtemp
inner join Vacancy v on(v.vacancyid = vtemp.vacancyid) where positionDesc like '%' + @singleWord + '%'
SELECT @position = CHARINDEX(',', @keywords, @start)
IF (@position = 0) SET @position = len(@keywords)
END
delete from #vacancy where score < @min_score
END
CREATE TABLE #OtherJobBoards
(
employerID int,
numVacancies int
)
insert into #OtherJobBoards
select employerID, count(*) from #vacancy where membershiptypeid = 9 group by employerid
delete from #vacancy where membershiptypeid = 9
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
IndexId int IDENTITY (1, 1) NOT NULL,
UID nvarchar(50),
score int
)
DECLARE @execSQL nvarchar(200)
SET @execSQL = 'INSERT INTO #PageIndex (UID,score) SELECT vtemp.VacancyID,score from #Vacancy vtemp inner join Vacancy v on(vtemp.VacancyID = v.vacancyid) ORDER BY ' + @OrderedBy
-- Insert into the temp table
EXEC sp_executesql @execSQL
drop table #Vacancy
-- Return total count
select count(IndexID) from #PageIndex
-- Return paged results
select E.employerid, E.Name, numVacancies from #OtherJobBoards O inner join Employer E on(E.EmployerID = O.EmployerID)
-- First set the rowcount
if @PageSize > -1
begin
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn
-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
end
if @PageSize = -1
select v.VacancyID, v.jobRef, v.positionTitle, v.location, v.salaryText, v.datePosted, c.contractType, v.skillsText as Skills, I.score,
(select count(*) from VacancyViews vv where vv.vacancyID = v.vacancyID) as NoViews,
(select count(*) from Application a where a.vacancyid = v.vacancyid and Active = 1) as Applications,
(select count(*) FROM BritSplit bs where bs.vacancyID = v.vacancyID) as IsBritSplit,
dbo.fnGetVacancySectors(v.vacancyid) as Sectors, DateRefreshed,featured,u.userid,u.forename,u.surna me,v.emailAddress
from #PageIndex I
inner join Vacancy v on(I.UID = v.vacancyid)
inner join ContractType c on(c.contracttypeid = v.contracttypeid)
inner join Users u on(v.userid = u.userid)
order by I.IndexID
else
select v.VacancyID, v.jobRef, v.positionTitle, v.location, v.salaryText, v.datePosted, c.contractType, v.skillsText as Skills, I.score,
(select count(*) from VacancyViews vv where vv.vacancyID = v.vacancyID) as NoViews,
(select count(*) from Application a where a.vacancyid = v.vacancyid and Active = 1) as Applications,
(select count(*) FROM BritSplit bs where bs.vacancyID = v.vacancyID) as IsBritSplit,
dbo.fnGetVacancySectors(v.vacancyid) as Sectors, DateRefreshed,featured,u.userid,u.forename,u.surna me,v.emailAddress
from #PageIndex I
inner join Vacancy v on(I.UID = v.vacancyid)
inner join ContractType c on(c.contracttypeid = v.contracttypeid)
inner join Users u on(v.userid = u.userid)
where I.IndexID > @PageLowerBound AND I.IndexID < @PageUpperBound ORDER BY I.IndexID
GO
What am i doing wrong?
|
