newbie asp - sql question

[monki]

i am trying to write a sql string that pulls a row from a dbase & the rows
either side

i cant use the primary key (autonumber) as some of the records have been
deleted thus there are gaps in the numbering

this is the sql string i have - but it doesnt work
vid is a string passed from a form, NewsId is the primary key

sql="SELECT * FROM tblNews t WHERE t.NewsId="& vid &" OR t.NewsId = (select
max(t.NewsId) t.NewsId WHERE t.NewsId < "& vid &") OR t.NewsId = (SELECT
min(t.NewsId) t.NewsId WHERE t.NewsId > "& vid &") ORDER by t.NewsId; "

can anyone see what is wrong with the sql? i get :-
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error. in query expression 't.NewsId=2 OR t.NewsId = (select
max(t.NewsId) WHERE t.NewsId < 2) OR t.NewsId = (SELECT min(t.NewsId) WHERE
t.NewsId > 2)'.
/geoff/newsalltest.asp, line 23

or is there a better way of doing this?

thanks in advance

j

[Chris Hohmann]

"monki" <j@usura.org> wrote in message
news:wmw0b.67$vv2.30@news-binary.blueyonder.co.uk...

> i am trying to write a sql string that pulls a row from a dbase & the

rows

> either side
>
> i cant use the primary key (autonumber) as some of the records have

been

> deleted thus there are gaps in the numbering
>
> this is the sql string i have - but it doesnt work
> vid is a string passed from a form, NewsId is the primary key
>
> sql="SELECT * FROM tblNews t WHERE t.NewsId="& vid &" OR t.NewsId =

(select

> max(t.NewsId) t.NewsId WHERE t.NewsId < "& vid &") OR t.NewsId =

(SELECT

> min(t.NewsId) t.NewsId WHERE t.NewsId > "& vid &") ORDER by t.NewsId;

"

>
> can anyone see what is wrong with the sql? i get :-
> Error Type:
> Microsoft JET Database Engine (0x80040E14)
> Syntax error. in query expression 't.NewsId=2 OR t.NewsId = (select
> max(t.NewsId) WHERE t.NewsId < 2) OR t.NewsId = (SELECT min(t.NewsId)

WHERE

> t.NewsId > 2)'.
> /geoff/newsalltest.asp, line 23
>
> or is there a better way of doing this?
>
> thanks in advance
>
> j

[spPrevNext]
PARAMETERS
prmNewsId Long
;
SELECT
*
FROM
tblNews AS t
WHERE
t.NewsId = prmNewsId OR
t.NewsID = (SELECT MAX(t.NewsId) FROM tblNews AS t WHERE t.NewsId <
prmNewsId) OR
t.NewsID = (SELECT MIN(t.NewsId) FROM tblNews AS t WHERE t.NewsId >
prmNewsId)

<%
Dim cn,rs,arr,j,jMax
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open "File Name=C:\SomeDirectoryOutsideTheAppRoot\MyConnectio n.UDL"
cn.spPrevNext Request.QueryString("NewsId"), rs
arr = rs.GetRows()
rs.Close : Set rs = Nothing
cn.Close : Set cn = Nothing
jMax = UBound(arr,2)
For j = 0 to jMax
'Process each row here
Next
%>

Notes:
1. When possible, use parameterized queries instead of building the sql
statement in ASP
2. Don't use "SELECT *"
3. Consider using a reference to a UDL file as your connection string

[monki]

thanks

"monki" <j@usura.org> wrote in message
news:wmw0b.67$vv2.30@news-binary.blueyonder.co.uk...

> i am trying to write a sql string that pulls a row from a dbase & the rows
> either side
>
> i cant use the primary key (autonumber) as some of the records have been
> deleted thus there are gaps in the numbering
>
> this is the sql string i have - but it doesnt work
> vid is a string passed from a form, NewsId is the primary key
>
> sql="SELECT * FROM tblNews t WHERE t.NewsId="& vid &" OR t.NewsId =

(select

> max(t.NewsId) t.NewsId WHERE t.NewsId < "& vid &") OR t.NewsId = (SELECT
> min(t.NewsId) t.NewsId WHERE t.NewsId > "& vid &") ORDER by t.NewsId; "
>
> can anyone see what is wrong with the sql? i get :-
> Error Type:
> Microsoft JET Database Engine (0x80040E14)
> Syntax error. in query expression 't.NewsId=2 OR t.NewsId = (select
> max(t.NewsId) WHERE t.NewsId < 2) OR t.NewsId = (SELECT min(t.NewsId)

WHERE

> t.NewsId > 2)'.
> /geoff/newsalltest.asp, line 23
>
> or is there a better way of doing this?
>
> thanks in advance
>
> j
>
>
>

[monki]

thanks for your advice got that working cheers

another page i am doing i am trying to acheive a similar thing but display
one row & pull the id for links to previous / next pages. is modding the
code you sent the best way of doing this

ie displat the middle row pull the id's of the first and last into tnext &
previous links

the only problem i can see with this is catching the first & last records of
the table & hiding the links.

if there is a better way of doing this please just point me in the right
direction & i will try and figure the code out myself (often frustraiting
but the best way to learn)

thanks in advance

j


"Chris Hohmann" <hohmannATyahooDOTcom> wrote in message
news:#MhYippZDHA.2632@TK2MSFTNGP12.phx.gbl...

> "monki" <j@usura.org> wrote in message
> news:wmw0b.67$vv2.30@news-binary.blueyonder.co.uk...

> > i am trying to write a sql string that pulls a row from a dbase & the

> rows

> > either side
> >
> > i cant use the primary key (autonumber) as some of the records have

> been

> > deleted thus there are gaps in the numbering
> >
> > this is the sql string i have - but it doesnt work
> > vid is a string passed from a form, NewsId is the primary key
> >
> > sql="SELECT * FROM tblNews t WHERE t.NewsId="& vid &" OR t.NewsId =

> (select

> > max(t.NewsId) t.NewsId WHERE t.NewsId < "& vid &") OR t.NewsId =

> (SELECT

> > min(t.NewsId) t.NewsId WHERE t.NewsId > "& vid &") ORDER by t.NewsId;

> "

> >
> > can anyone see what is wrong with the sql? i get :-
> > Error Type:
> > Microsoft JET Database Engine (0x80040E14)
> > Syntax error. in query expression 't.NewsId=2 OR t.NewsId = (select
> > max(t.NewsId) WHERE t.NewsId < 2) OR t.NewsId = (SELECT min(t.NewsId)

> WHERE

> > t.NewsId > 2)'.
> > /geoff/newsalltest.asp, line 23
> >
> > or is there a better way of doing this?
> >
> > thanks in advance
> >
> > j

>
> [spPrevNext]
> PARAMETERS
> prmNewsId Long
> ;
> SELECT
> *
> FROM
> tblNews AS t
> WHERE
> t.NewsId = prmNewsId OR
> t.NewsID = (SELECT MAX(t.NewsId) FROM tblNews AS t WHERE t.NewsId <
> prmNewsId) OR
> t.NewsID = (SELECT MIN(t.NewsId) FROM tblNews AS t WHERE t.NewsId >
> prmNewsId)
>
> <%
> Dim cn,rs,arr,j,jMax
> Set cn = CreateObject("ADODB.Connection")
> Set rs = CreateObject("ADODB.Recordset")
> cn.Open "File Name=C:\SomeDirectoryOutsideTheAppRoot\MyConnectio n.UDL"
> cn.spPrevNext Request.QueryString("NewsId"), rs
> arr = rs.GetRows()
> rs.Close : Set rs = Nothing
> cn.Close : Set cn = Nothing
> jMax = UBound(arr,2)
> For j = 0 to jMax
> 'Process each row here
> Next
> %>
>
> Notes:
> 1. When possible, use parameterized queries instead of building the sql
> statement in ASP
> 2. Don't use "SELECT *"
> 3. Consider using a reference to a UDL file as your connection string
>
>
>
>
>
>
>
>
>
>
>
>

[monki]

thanks for your advice got that working cheers

another page i am doing i am trying to acheive a similar thing but display
one row & pull the id for links to previous / next pages. is modding the
code you sent the best way of doing this

ie displat the middle row pull the id's of the first and last into tnext &
previous links

the only problem i can see with this is catching the first & last records of
the table & hiding the links.

if there is a better way of doing this please just point me in the right
direction & i will try and figure the code out myself (often frustraiting
but the best way to learn)

thanks in advance

j


"Chris Hohmann" <hohmannATyahooDOTcom> wrote in message
news:#MhYippZDHA.2632@TK2MSFTNGP12.phx.gbl...

> "monki" <j@usura.org> wrote in message
> news:wmw0b.67$vv2.30@news-binary.blueyonder.co.uk...

> > i am trying to write a sql string that pulls a row from a dbase & the

> rows

> > either side
> >
> > i cant use the primary key (autonumber) as some of the records have

> been

> > deleted thus there are gaps in the numbering
> >
> > this is the sql string i have - but it doesnt work
> > vid is a string passed from a form, NewsId is the primary key
> >
> > sql="SELECT * FROM tblNews t WHERE t.NewsId="& vid &" OR t.NewsId =

> (select

> > max(t.NewsId) t.NewsId WHERE t.NewsId < "& vid &") OR t.NewsId =

> (SELECT

> > min(t.NewsId) t.NewsId WHERE t.NewsId > "& vid &") ORDER by t.NewsId;

> "

> >
> > can anyone see what is wrong with the sql? i get :-
> > Error Type:
> > Microsoft JET Database Engine (0x80040E14)
> > Syntax error. in query expression 't.NewsId=2 OR t.NewsId = (select
> > max(t.NewsId) WHERE t.NewsId < 2) OR t.NewsId = (SELECT min(t.NewsId)

> WHERE

> > t.NewsId > 2)'.
> > /geoff/newsalltest.asp, line 23
> >
> > or is there a better way of doing this?
> >
> > thanks in advance
> >
> > j

>
> [spPrevNext]
> PARAMETERS
> prmNewsId Long
> ;
> SELECT
> *
> FROM
> tblNews AS t
> WHERE
> t.NewsId = prmNewsId OR
> t.NewsID = (SELECT MAX(t.NewsId) FROM tblNews AS t WHERE t.NewsId <
> prmNewsId) OR
> t.NewsID = (SELECT MIN(t.NewsId) FROM tblNews AS t WHERE t.NewsId >
> prmNewsId)
>
> <%
> Dim cn,rs,arr,j,jMax
> Set cn = CreateObject("ADODB.Connection")
> Set rs = CreateObject("ADODB.Recordset")
> cn.Open "File Name=C:\SomeDirectoryOutsideTheAppRoot\MyConnectio n.UDL"
> cn.spPrevNext Request.QueryString("NewsId"), rs
> arr = rs.GetRows()
> rs.Close : Set rs = Nothing
> cn.Close : Set cn = Nothing
> jMax = UBound(arr,2)
> For j = 0 to jMax
> 'Process each row here
> Next
> %>
>
> Notes:
> 1. When possible, use parameterized queries instead of building the sql
> statement in ASP
> 2. Don't use "SELECT *"
> 3. Consider using a reference to a UDL file as your connection string
>
>
>
>
>
>
>
>
>
>
>
>

[Chris Hohmann]

"monki" <j@usura.org> wrote in message
news:NNP0b.1$Ij.0@news-binary.blueyonder.co.uk...

> thanks for your advice got that working cheers
>
> another page i am doing i am trying to acheive a similar thing but

display

> one row & pull the id for links to previous / next pages. is modding

the

> code you sent the best way of doing this
>
> ie displat the middle row pull the id's of the first and last into

tnext &

> previous links
>
> the only problem i can see with this is catching the first & last

records of

> the table & hiding the links.
>
> if there is a better way of doing this please just point me in the

right

> direction & i will try and figure the code out myself (often

frustraiting

> but the best way to learn)
>
> thanks in advance

I'm glad to here you got it working. Modifying your existing code is
certainly one way to achieve what you've described for your other page.
You could also create two stored procedures. spArticleNext and
spArticlePrev. spArticleNext would look something like this:

PARAMETERS
prmNewsId Long
;
SELECT TOP 1
*
FROM
tblNews AS T
WHERE
T.NewsId > prmNewsId
ORDER BY
T.NewsID ASC

This has a number of benefits.
1. You only return one (1) record at a time.
2. You avoid concurrency issues. For example suppose you retrieve
prev/current/next. While you view the page, someone else inserts a
record in between current and next. When you click on next, you end up
jumping right over the new record. By retrieving one record at a time,
you can avoid this.
3. The logic of the query in a lot more straightforward and
consequently, more efficient.

As for detecting the first/last record, simple check for BOF/EOF in the
recordset. The spArticlePrev is left as an exercise for the reader. :)

HTH
-Chris