Problem with paging SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem with paging SP

Author

Topic

Blastrix
Posting Yak Master

208 Posts

Posted - 2001-03-28 : 19:50:55

I'm using MSSQL7 and trying to run a stored procedure from an ASP page. Of course I wouldn't be here unless there was a problem, and there is. I run the stored procedure, but it returns saying:

Syntax error converting datetime from character string.

This is my first shot a big(to me) stored procedure, so I'm a bit lost, but I believe I have traced it to this section of code where the adDateTime >= @adDateTime because if I change >= to just = then it works fine, returning no records. I have checked all variables and made sure that they're sending the appropriate information to the query from the ASP page, and the adDateTime field in the classifieds table is of type dateTime:

INSERT INTO #tempList (adId, username, anonName, adDateTime, city, state, country, title, image1, image2, image3, website, adText)
SELECT classifieds.adId, classifieds.anonName, classifieds.adDateTime, classifieds.city, classifieds.state, classifieds.country, classifieds.title,
classifieds.image1, classifieds.image2, classifieds.image3, classifieds.website, classifieds.adText, users.username
FROM classifieds, users
WHERE adType = @adType AND adDateTime >= @adDateTime AND classifieds.userId = users.userId
ORDER BY adDateTime DESC

I've been busting my rear trying to figure this out for the past three days, and have had absolutely *no* luck.

In case I'm completely off here, here's the whole stored procedure:

CREATE PROCEDURE [spListAds]
(
@adType int,
@adDateTime dateTime,
@pageNum int,
@perPage int
)
AS

-- Don't return number of rows inserted into the temp table
SET NOCOUNT ON

-- Create temp table
CREATE TABLE #tempList
(
id int IDENTITY,
adId int,
username varchar(30),
anonName varchar(50),
adDateTime dateTime,
city varchar(50),
state varchar(50),
country varchar(50),
title varchar(50),
image1 varchar(100),
image2 varchar(100),
image3 varchar(100),
website varchar(100),
adText text
)

-- Insert from the classifieds table to the temp table
INSERT INTO #tempList (adId, username, anonName, adDateTime, city, state, country, title, image1, image2, image3, website, adText)
SELECT classifieds.adId, classifieds.anonName, classifieds.adDateTime, classifieds.city, classifieds.state, classifieds.country, classifieds.title,
classifieds.image1, classifieds.image2, classifieds.image3, classifieds.website, classifieds.adText, users.username
FROM classifieds, users
WHERE adType = @adType AND adDateTime >= @adDateTime AND classifieds.userId = users.userId
ORDER BY adDateTime DESC

-- Figure first and last records that we want
DECLARE @firstRec int, @lastRec int
SELECT @firstRec = (@pageNum - 1) * @perPage
SELECT @lastRec = (@pageNum * @perPage + 1)

-- Return our records
SELECT *,
nextPage =
(
SELECT COUNT(*)
FROM #tempList tl
WHERE tl.id >= @lastRec
),

totalAds =
(
SELECT COUNT(*)
FROM #tempList tl
)
FROM #tempList
WHERE id > @firstRec AND id < @lastRec

-- Turn off no count
SET NOCOUNT OFF

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources