dominicall
11-18-2002, 06:32 PM
OK - any takers on this one???
I'm attempting to use a dynamic stored procedure for paging a recordset (it's a pretty big RS so using the dynamic SP works perfectly as it only returns the required records). Anyway, it keeps throwing an error and I can't see it at all so thought I'd ask if anyone else can see it.
The stored proc is...CREATE Procedure spSearchBuyNew
(
@Page int,
@RecsPerPage int,
@WhereClause varchar(3000)
)
As
-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON
--Create a temporary table
CREATE TABLE #TempItems
(
PropAutoID int IDENTITY,
PropID int,
PropRef nvarchar(20),
HouseNum nvarchar(4),
Road nvarchar(75),
PostArea nvarchar(4),
PostCode nvarchar(3),
Town nvarchar(60),
SalesPrice money,
FreeLease nvarchar(35),
Beds smallint,
SalesStatus nvarchar(12),
Agency nvarchar(75),
Office nvarchar(75),
PropType nvarchar(60),
Notes nvarchar(500),
Receps smallint
)
-- Insert the rows from tblItems into the temp. table
DECLARE @SearchSQL varchar(5000)
SELECT @SearchSQL = "INSERT INTO #TempItems (PropID, PropRef, HouseNum, Road, PostArea, PostCode, Town, SalesPrice, FreeLease, Beds, SalesStatus,Agency, Office, PropType, Notes, Receps)"
+ "SELECT tbl_Sales.PropID AS PropID, tbl_Sales.PropRef AS PropRef, tbl_Sales.HouseNum AS HouseNum, tbl_Sales.Road AS Road, "
+ "tbl_Sales.PostArea AS PostArea, tbl_Sales.PostCode AS PostCode, tbl_Towns.Town AS Town, tbl_Sales.SalesPrice AS SalesPrice, "
+ "tbl_FreeLease.FreeLease AS FreeLease, tbl_Sales.Beds AS Beds, tbl_SalesStatus.SalesStatus AS SalesStatus, tbl_Offices.Agency AS Agency, "
+ "tbl_Offices.Office AS Office, tbl_PropType.PropType AS PropType, tbl_Sales.Notes AS Notes, tbl_Sales.Receps AS Receps "
+ "FROM tbl_Sales LEFT OUTER JOIN tbl_PropType ON tbl_Sales.PropType = tbl_PropType.PropTypeID LEFT OUTER JOIN "
+ "tbl_Offices ON tbl_Sales.OfficeID = tbl_Offices.OfficeID LEFT OUTER JOIN "
+ "tbl_SalesStatus ON tbl_Sales.PropStatus = tbl_SalesStatus.SalesStatusID LEFT OUTER JOIN "
+ "tbl_FreeLease ON tbl_Sales.FreeLease = tbl_FreeLease.FreeLeaseID LEFT OUTER JOIN "
+ "tbl_Towns ON tbl_Sales.Area = tbl_Towns.TownID LEFT OUTER JOIN tbl_Agent ON tbl_Sales.AgentID = tbl_Agent.AgentID "
+ "WHERE " + @WhereClause + " ORDER BY tbl_Sales.SalesPrice ASC"
EXEC(@SearchSQL)
-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.PropAutoID >= @LastRec
),
TotalRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
)
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
GOThe @WhereClause that gets submitted to the SP is...
tbl_Sales.PropType IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13) AND tbl_Sales.PropAge IN (1, 2, 3, 4, 5, 6, 7, 8) AND (tbl_Sales.SalesPrice BETWEEN 1 AND 1111111111) AND (tbl_Sales.Beds BETWEEN 1 AND 1111111111) AND (tbl_Sales.County = 71) AND tbl_Agent.AgentStatus = 'LI' AND tbl_Sales.PropStatus <> 4 AND tbl_Sales.Area IN (7013, 788)
I've checked out the full select statement in Query Analyzer hard coding the @WhereClause and that all works OK.
and the code for opening the RS is...Dim strSQL
strSQL = "spSearchBuyNew " & currentPage & "," & iRecordsPerPage & ", " & searchString
rsGetProps.Open strSQL, objConn
The error I'm getting is this....
Microsoft OLE DB Provider for SQL Server error '80040e14'
Line 1: Incorrect syntax near '.'.
/content/contentpages/buying/townprops.asp, line 105
Line 105 is the rsGetProps.Open line
Can anyone else see the error???
Dominic :confused:
I'm attempting to use a dynamic stored procedure for paging a recordset (it's a pretty big RS so using the dynamic SP works perfectly as it only returns the required records). Anyway, it keeps throwing an error and I can't see it at all so thought I'd ask if anyone else can see it.
The stored proc is...CREATE Procedure spSearchBuyNew
(
@Page int,
@RecsPerPage int,
@WhereClause varchar(3000)
)
As
-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON
--Create a temporary table
CREATE TABLE #TempItems
(
PropAutoID int IDENTITY,
PropID int,
PropRef nvarchar(20),
HouseNum nvarchar(4),
Road nvarchar(75),
PostArea nvarchar(4),
PostCode nvarchar(3),
Town nvarchar(60),
SalesPrice money,
FreeLease nvarchar(35),
Beds smallint,
SalesStatus nvarchar(12),
Agency nvarchar(75),
Office nvarchar(75),
PropType nvarchar(60),
Notes nvarchar(500),
Receps smallint
)
-- Insert the rows from tblItems into the temp. table
DECLARE @SearchSQL varchar(5000)
SELECT @SearchSQL = "INSERT INTO #TempItems (PropID, PropRef, HouseNum, Road, PostArea, PostCode, Town, SalesPrice, FreeLease, Beds, SalesStatus,Agency, Office, PropType, Notes, Receps)"
+ "SELECT tbl_Sales.PropID AS PropID, tbl_Sales.PropRef AS PropRef, tbl_Sales.HouseNum AS HouseNum, tbl_Sales.Road AS Road, "
+ "tbl_Sales.PostArea AS PostArea, tbl_Sales.PostCode AS PostCode, tbl_Towns.Town AS Town, tbl_Sales.SalesPrice AS SalesPrice, "
+ "tbl_FreeLease.FreeLease AS FreeLease, tbl_Sales.Beds AS Beds, tbl_SalesStatus.SalesStatus AS SalesStatus, tbl_Offices.Agency AS Agency, "
+ "tbl_Offices.Office AS Office, tbl_PropType.PropType AS PropType, tbl_Sales.Notes AS Notes, tbl_Sales.Receps AS Receps "
+ "FROM tbl_Sales LEFT OUTER JOIN tbl_PropType ON tbl_Sales.PropType = tbl_PropType.PropTypeID LEFT OUTER JOIN "
+ "tbl_Offices ON tbl_Sales.OfficeID = tbl_Offices.OfficeID LEFT OUTER JOIN "
+ "tbl_SalesStatus ON tbl_Sales.PropStatus = tbl_SalesStatus.SalesStatusID LEFT OUTER JOIN "
+ "tbl_FreeLease ON tbl_Sales.FreeLease = tbl_FreeLease.FreeLeaseID LEFT OUTER JOIN "
+ "tbl_Towns ON tbl_Sales.Area = tbl_Towns.TownID LEFT OUTER JOIN tbl_Agent ON tbl_Sales.AgentID = tbl_Agent.AgentID "
+ "WHERE " + @WhereClause + " ORDER BY tbl_Sales.SalesPrice ASC"
EXEC(@SearchSQL)
-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.PropAutoID >= @LastRec
),
TotalRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
)
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
GOThe @WhereClause that gets submitted to the SP is...
tbl_Sales.PropType IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13) AND tbl_Sales.PropAge IN (1, 2, 3, 4, 5, 6, 7, 8) AND (tbl_Sales.SalesPrice BETWEEN 1 AND 1111111111) AND (tbl_Sales.Beds BETWEEN 1 AND 1111111111) AND (tbl_Sales.County = 71) AND tbl_Agent.AgentStatus = 'LI' AND tbl_Sales.PropStatus <> 4 AND tbl_Sales.Area IN (7013, 788)
I've checked out the full select statement in Query Analyzer hard coding the @WhereClause and that all works OK.
and the code for opening the RS is...Dim strSQL
strSQL = "spSearchBuyNew " & currentPage & "," & iRecordsPerPage & ", " & searchString
rsGetProps.Open strSQL, objConn
The error I'm getting is this....
Microsoft OLE DB Provider for SQL Server error '80040e14'
Line 1: Incorrect syntax near '.'.
/content/contentpages/buying/townprops.asp, line 105
Line 105 is the rsGetProps.Open line
Can anyone else see the error???
Dominic :confused: