|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Select next item in DB without knowing what ID it hashas an associated ID, but the ID's are not necessarily sequential (some images may have been deleted, leaving gaps in the list of ID's). I am looking to be able to call an ID and its Image from the database, but also have returned the previous ID and the next ID, even when those actual ID's are not necessarily +1 and -1 to the ID being called. For example, I want to bring up an image with the ID of 22. There *had* been images with ID's of 21, 23 and 24, but they had been deleted in the past. I want to be able to have returned, in the same query (or be able to immediately use another query to discover them) the ID's of 20 and 25, because these are the ID's of currently existing photos that are directly before and after the photo that has the ID of 22. Is there anywhere where I can see and example that makes use of images stored in a database? TIA ....Geshel -- *********************************************************************** * My reply-to is an automatically monitored spam honeypot. Do not use * * it unless you want to be blacklisted by SpamCop. Please reply to my * * first name at my last name dot org. * *********************************************************************** you can use the TOP & UNION keywords
select id from table where id = @id union select top 1 id from table where id<@id union select top 1 id from table where id>@id HTH, Show quote "Neo Geshel" <got***@geshel.org> wrote in message news:evlOppb5FHA.2864@tk2msftngp13.phx.gbl... >I have an Access DB, from which I am going to pull images. Each image has >an associated ID, but the ID's are not necessarily sequential (some images >may have been deleted, leaving gaps in the list of ID's). > > I am looking to be able to call an ID and its Image from the database, but > also have returned the previous ID and the next ID, even when those actual > ID's are not necessarily +1 and -1 to the ID being called. > > For example, I want to bring up an image with the ID of 22. There *had* > been images with ID's of 21, 23 and 24, but they had been deleted in the > past. I want to be able to have returned, in the same query (or be able to > immediately use another query to discover them) the ID's of 20 and 25, > because these are the ID's of currently existing photos that are directly > before and after the photo that has the ID of 22. > > Is there anywhere where I can see and example that makes use of images > stored in a database? > > TIA > ...Geshel > -- > *********************************************************************** > * My reply-to is an automatically monitored spam honeypot. Do not use * > * it unless you want to be blacklisted by SpamCop. Please reply to my * > * first name at my last name dot org. * > *********************************************************************** Onin Tayson wrote:
Show quote > "Neo Geshel" <got***@geshel.org> wrote in message (bottom-posted for clarity: http://www.caliburn.nl/topposting.html)> news:evlOppb5FHA.2864@tk2msftngp13.phx.gbl... > >>I have an Access DB, from which I am going to pull images. Each image has >>an associated ID, but the ID's are not necessarily sequential (some images >>may have been deleted, leaving gaps in the list of ID's). >> >>I am looking to be able to call an ID and its Image from the database, but >>also have returned the previous ID and the next ID, even when those actual >>ID's are not necessarily +1 and -1 to the ID being called. >> >>For example, I want to bring up an image with the ID of 22. There *had* >>been images with ID's of 21, 23 and 24, but they had been deleted in the >>past. I want to be able to have returned, in the same query (or be able to >>immediately use another query to discover them) the ID's of 20 and 25, >>because these are the ID's of currently existing photos that are directly >>before and after the photo that has the ID of 22. >> >>Is there anywhere where I can see and example that makes use of images >>stored in a database? >> >>TIA >>...Geshel >>-- >>*********************************************************************** >>* My reply-to is an automatically monitored spam honeypot. Do not use * >>* it unless you want to be blacklisted by SpamCop. Please reply to my * >>* first name at my last name dot org. * >>*********************************************************************** > > > you can use the TOP & UNION keywords > > select id from table where id = @id union > select top 1 id from table where id<@id union > select top 1 id from table where id>@id > > HTH, > > You know, I didn't think of that at all. Although I am curious, the way I read the third line, it wouldn't bring me #25, but rather the last ID (the highest numbered ID) in the list of images. Although I'm not an SQL guru, shouldn't the second and third lines be ORDER BY [ID], where the second line is normal (ASC) and the third line is reversed (DESC)? That way, the second line has the highest number under 22 at the top, and the third line has the lowest number over 22 at the top. As well, since three ID's are being returned, they'll have to be renamed before being plunked into the repeater, no? I see the query as being: SELECT TOP 1 [ID] AS [prev] FROM [tblImages] WHERE [ID]<@id SORT BY [ID] ASC UNION SELECT TOP 1 [ID] AS [next] FROM [tblImages] WHERE [ID]>@id SORT BY [ID] DESC This way, the ID of the image requested is being used to pull two more ID's from the database. One ID is the highest one beneath it, the other is the lowest one above it. Because more than one ID is being pulled from the database at a time, they have to be renamed so they don't conflict. Please tell me if I am missing something here. (BTW, the image's ID will already be known. What is being pulled are the ID's for the prev and next images, so they can be linked to and provide a continuous image series.) ....Geshel -- *********************************************************************** * My reply-to is an automatically monitored spam honeypot. Do not use * * it unless you want to be blacklisted by SpamCop. Please reply to my * * first name at my last name dot org. * *********************************************************************** yep, i missed the ORDER BY ("SORT") keyword (i just typed it here directly
as what i'm doing right now ;). but it should be the other way around (based on reading the SQL - no testing done here). the Previous ID should be ORDERed BY [ID] DESC and Next ID should be ordered ASC. below should be your SQL code. SELECT [ID] FROM [tblImages] WHERE [ID] = @id UNION SELECT TOP 1 [ID] FROM [tblImages] WHERE [ID]<@id ORDER BY [ID] DESC UNION SELECT TOP 1 [ID] FROM [tblImages] WHERE [ID]>@id ORDER BY [ID] regarding column renaming... you don't have to rename the UNIONed columns as it will have no effect on the results of the query, it will still use the column name from the first SELECT. HTH, Show quote "Neo Geshel" <got***@geshel.org> wrote in message news:eBn18tc5FHA.2560@TK2MSFTNGP12.phx.gbl... > Onin Tayson wrote: >> "Neo Geshel" <got***@geshel.org> wrote in message >> news:evlOppb5FHA.2864@tk2msftngp13.phx.gbl... >> >>>I have an Access DB, from which I am going to pull images. Each image has >>>an associated ID, but the ID's are not necessarily sequential (some >>>images may have been deleted, leaving gaps in the list of ID's). >>> >>>I am looking to be able to call an ID and its Image from the database, >>>but also have returned the previous ID and the next ID, even when those >>>actual ID's are not necessarily +1 and -1 to the ID being called. >>> >>>For example, I want to bring up an image with the ID of 22. There *had* >>>been images with ID's of 21, 23 and 24, but they had been deleted in the >>>past. I want to be able to have returned, in the same query (or be able >>>to immediately use another query to discover them) the ID's of 20 and 25, >>>because these are the ID's of currently existing photos that are directly >>>before and after the photo that has the ID of 22. >>> >>>Is there anywhere where I can see and example that makes use of images >>>stored in a database? >>> >>>TIA >>>...Geshel >>>-- >>>*********************************************************************** >>>* My reply-to is an automatically monitored spam honeypot. Do not use * >>>* it unless you want to be blacklisted by SpamCop. Please reply to my * >>>* first name at my last name dot org. * >>>*********************************************************************** >> >> >> you can use the TOP & UNION keywords >> >> select id from table where id = @id union >> select top 1 id from table where id<@id union >> select top 1 id from table where id>@id >> >> HTH, >> >> > (bottom-posted for clarity: http://www.caliburn.nl/topposting.html) > > You know, I didn't think of that at all. Although I am curious, the way I > read the third line, it wouldn't bring me #25, but rather the last ID (the > highest numbered ID) in the list of images. > > Although I'm not an SQL guru, shouldn't the second and third lines be > ORDER BY [ID], where the second line is normal (ASC) and the third line is > reversed (DESC)? That way, the second line has the highest number under 22 > at the top, and the third line has the lowest number over 22 at the top. > > As well, since three ID's are being returned, they'll have to be renamed > before being plunked into the repeater, no? > > I see the query as being: > > SELECT TOP 1 [ID] AS [prev] FROM [tblImages] WHERE [ID]<@id SORT BY [ID] > ASC UNION > SELECT TOP 1 [ID] AS [next] FROM [tblImages] WHERE [ID]>@id SORT BY [ID] > DESC > > This way, the ID of the image requested is being used to pull two more > ID's from the database. One ID is the highest one beneath it, the other is > the lowest one above it. Because more than one ID is being pulled from the > database at a time, they have to be renamed so they don't conflict. > > Please tell me if I am missing something here. (BTW, the image's ID will > already be known. What is being pulled are the ID's for the prev and next > images, so they can be linked to and provide a continuous image series.) > > ...Geshel > -- > *********************************************************************** > * My reply-to is an automatically monitored spam honeypot. Do not use * > * it unless you want to be blacklisted by SpamCop. Please reply to my * > * first name at my last name dot org. * > *********************************************************************** Onin Tayson wrote:
Show quote > "Neo Geshel" <got***@geshel.org> wrote in message (bottom-posted for clarity: http://www.caliburn.nl/topposting.html)> news:eBn18tc5FHA.2560@TK2MSFTNGP12.phx.gbl... >>Onin Tayson wrote: >>>"Neo Geshel" <got***@geshel.org> wrote in message >>>news:evlOppb5FHA.2864@tk2msftngp13.phx.gbl... >>>>I have an Access DB, from which I am going to pull images. Each image has >>>>an associated ID, but the ID's are not necessarily sequential (some >>>>images may have been deleted, leaving gaps in the list of ID's). >>>> >>>>I am looking to be able to call an ID and its Image from the database, >>>>but also have returned the previous ID and the next ID, even when those >>>>actual ID's are not necessarily +1 and -1 to the ID being called. >>>> >>>>For example, I want to bring up an image with the ID of 22. There *had* >>>>been images with ID's of 21, 23 and 24, but they had been deleted in the >>>>past. I want to be able to have returned, in the same query (or be able >>>>to immediately use another query to discover them) the ID's of 20 and 25, >>>>because these are the ID's of currently existing photos that are directly >>>>before and after the photo that has the ID of 22. >>>> >>>>Is there anywhere where I can see and example that makes use of images >>>>stored in a database? >>> >>>you can use the TOP & UNION keywords >>> >>>select id from table where id = @id union >>>select top 1 id from table where id<@id union >>>select top 1 id from table where id>@id >> >>You know, I didn't think of that at all. Although I am curious, the way I >>read the third line, it wouldn't bring me #25, but rather the last ID (the >>highest numbered ID) in the list of images. >> >>Although I'm not an SQL guru, shouldn't the second and third lines be >>ORDER BY [ID], where the second line is normal (ASC) and the third line is >>reversed (DESC)? That way, the second line has the highest number under 22 >>at the top, and the third line has the lowest number over 22 at the top. >> >>As well, since three ID's are being returned, they'll have to be renamed >>before being plunked into the repeater, no? >> >>I see the query as being: >> >>SELECT TOP 1 [ID] AS [prev] FROM [tblImages] WHERE [ID]<@id SORT BY [ID] >>ASC UNION >>SELECT TOP 1 [ID] AS [next] FROM [tblImages] WHERE [ID]>@id SORT BY [ID] >>DESC >> >>This way, the ID of the image requested is being used to pull two more >>ID's from the database. One ID is the highest one beneath it, the other is >>the lowest one above it. Because more than one ID is being pulled from the >>database at a time, they have to be renamed so they don't conflict. >> >>Please tell me if I am missing something here. (BTW, the image's ID will >>already be known. What is being pulled are the ID's for the prev and next >>images, so they can be linked to and provide a continuous image series.) > > yep, i missed the ORDER BY ("SORT") keyword (i just typed it here directly > as what i'm doing right now ;). but it should be the other way around (based > on reading the SQL - no testing done here). the Previous ID should be > ORDERed BY [ID] DESC and Next ID should be ordered ASC. below should be > your SQL code. > > SELECT [ID] FROM [tblImages] WHERE [ID] = @id UNION > SELECT TOP 1 [ID] FROM [tblImages] WHERE [ID]<@id ORDER BY [ID] DESC UNION > SELECT TOP 1 [ID] FROM [tblImages] WHERE [ID]>@id ORDER BY [ID] > > regarding column renaming... you don't have to rename the UNIONed columns as > it will have no effect on the results of the query, it will still use the > column name from the first SELECT. > You are correct about the sorting of the ID's - I wasn't thinking straight. But as for the UNIONed columns, I thought I would *have* to rename them, because all three [ID]'s would be going into the same Repeater, no? If I didn't rename them, how would the Repeater know which <%# Container.DataItem("ID") %> to attach each of them to? I intend to have at least three of these containers inside the Repeater - one for the "previous" link (which reloads the page, but with the previous image's ID as a variable in the URL), one for the current image (so the image.aspx page being called inside the <img> tag knows which image to get and load from the DB), and one for the "next" link (which, once again, reloads the current page, but with the next image's ID as a variable). Although, I have to say, thanks for your help, Onin! I don't think I would have figured this out on my own. ...Geshel -- *********************************************************************** * My reply-to is an automatically monitored spam honeypot. Do not use * * it unless you want to be blacklisted by SpamCop. Please reply to my * * first name at my last name dot org. * *********************************************************************** “Anyone who believes in Intelligent Design (“creationismâ€) is just as ignorant and ill-educated as someone who believes that the world is flat, that the Sun circles the Earth or that there really is a tooth fairy. Darwinism has an overwhelming foundation of evidence that can be tested and reproduced. Intelligent Design, on the other hand, has no evidence at all; not one single shred of testable proof. As such, Intelligent Design is Religious Mythology, and has no right whatsoever to be in our Science classrooms.†- 99.99+% of Scientists *********************************************************************** Mignon McLaughlin once said that “A nymphomaniac is a woman [who is] as obsessed with sex as the average man.†Unfortunately, since true nymphomaniacs are so rare, this means that it takes an extraordinary woman to keep up with an ordinary man. *********************************************************************** I thought UNION'd queries supported only a single ORDER BY at the end,
determining the order of the entire union'd set of records. If you run into this, you could try subqueries: SELECT [ID] FROM [tblImages] WHERE [ID] = @id UNION SELECT * FROM (SELECT TOP 1 [ID] FROM [tblImages] WHERE [ID]<@id ORDER BY [ID] DESC) alias1 UNION SELECT * FROM (SELECT TOP 1 [ID] FROM [tblImages] WHERE [ID]>@id ORDER BY [ID]) alias2 KitWest wrote:
> I thought UNION'd queries supported only a single ORDER BY at the end, I'll be trying it out when I build my Image Gallery viewer in a couple > determining the order of the entire union'd set of records. If you run > into this, you could try subqueries: > SELECT [ID] FROM [tblImages] WHERE [ID] = @id UNION > SELECT * FROM (SELECT TOP 1 [ID] FROM [tblImages] WHERE [ID]<@id ORDER > BY [ID] DESC) alias1 UNION > SELECT * FROM (SELECT TOP 1 [ID] FROM [tblImages] WHERE [ID]>@id ORDER > BY [ID]) alias2 > COOL! of hours. Hopefully it'll work flawlessly. Thanks! ...Geshel -- *********************************************************************** * My reply-to is an automatically monitored spam honeypot. Do not use * * it unless you want to be blacklisted by SpamCop. Please reply to my * * first name at my last name dot org. * *********************************************************************** “Anyone who believes in Intelligent Design (“creationismâ€) is just as ignorant and ill-educated as someone who believes that the world is flat, that the Sun circles the Earth or that there really is a tooth fairy. Darwinism has an overwhelming foundation of evidence that can be tested and reproduced. Intelligent Design, on the other hand, has no evidence at all; not one single shred of testable proof. As such, Intelligent Design is Religious Mythology, and has no right whatsoever to be in our Science classrooms.†- 99.99+% of Scientists *********************************************************************** Mignon McLaughlin once said that “A nymphomaniac is a woman [who is] as obsessed with sex as the average man.†Unfortunately, since true nymphomaniacs are so rare, this means that it takes an extraordinary woman to keep up with an ordinary man. *********************************************************************** Neo Geshel wrote:
> KitWest wrote: For the next ID, isn't is simpler to get the minimum ID larger than the >> I thought UNION'd queries supported only a single ORDER BY at the >> end, determining the order of the entire union'd set of records. If >> you run into this, you could try subqueries: >> SELECT [ID] FROM [tblImages] WHERE [ID] = @id UNION >> SELECT * FROM (SELECT TOP 1 [ID] FROM [tblImages] WHERE [ID]<@id >> ORDER BY [ID] DESC) alias1 UNION >> SELECT * FROM (SELECT TOP 1 [ID] FROM [tblImages] WHERE [ID]>@id >> ORDER BY [ID]) alias2 specified one? SELECT MIN(ID) FROM [tblImages] WHERE ID>@id; And similarly using MAX for the previous ID. If there were a lot of values and "holes" were rare, perhaps it would be better to check to find if the next value (ID+1) exists before making it search everything. Andrew Andrew Morton wrote:
Show quote > Neo Geshel wrote: Yes, but will it work with Access? This is only a small site, I don't >>KitWest wrote: >>>I thought UNION'd queries supported only a single ORDER BY at the >>>end, determining the order of the entire union'd set of records. If >>>you run into this, you could try subqueries: >>>SELECT [ID] FROM [tblImages] WHERE [ID] = @id UNION >>>SELECT * FROM (SELECT TOP 1 [ID] FROM [tblImages] WHERE [ID]<@id >>>ORDER BY [ID] DESC) alias1 UNION >>>SELECT * FROM (SELECT TOP 1 [ID] FROM [tblImages] WHERE [ID]>@id >>>ORDER BY [ID]) alias2 > > For the next ID, isn't is simpler to get the minimum ID larger than the > specified one? > > SELECT MIN(ID) FROM [tblImages] WHERE ID>@id; > > And similarly using MAX for the previous ID. > > If there were a lot of values and "holes" were rare, perhaps it would be > better to check to find if the next value (ID+1) exists before making it > search everything. need SQL Server just to make the SQL queries work. ...Geshel -- *********************************************************************** * My reply-to is an automatically monitored spam honeypot. Do not use * * it unless you want to be blacklisted by SpamCop. Please reply to my * * first name at my last name dot org. * *********************************************************************** “Anyone who believes in Intelligent Design (“creationismâ€) is just as ignorant and ill-educated as someone who believes that the world is flat, that the Sun circles the Earth or that there really is a tooth fairy. Darwinism has an overwhelming foundation of evidence that can be tested and reproduced. Intelligent Design, on the other hand, has no evidence at all; not one single shred of testable proof. As such, Intelligent Design is Religious Mythology, and has no right whatsoever to be in our Science classrooms.†- 99.99+% of Scientists *********************************************************************** Mignon McLaughlin once said that “A nymphomaniac is a woman [who is] as obsessed with sex as the average man.†Unfortunately, since true nymphomaniacs are so rare, this means that it takes an extraordinary woman to keep up with an ordinary man. *********************************************************************** Neo Geshel wrote:
> Andrew Morton wrote: Ooh, didn't check that bit. I don't have Access here. What happens when you >> For the next ID, isn't is simpler to get the minimum ID larger than >> the specified one? >> >> SELECT MIN(ID) FROM [tblImages] WHERE ID>@id; > > Yes, but will it work with Access? This is only a small site, I don't > need SQL Server just to make the SQL queries work. try it on your test copy of the database? Andrew i think it will work. btw, i tested the SQL i sent you and it's not not
working properly... sorry 'bout that. I tried the code below (using SQL Server though) and i think it's what you're looking for... select [ID] from [tblImages] where [ID] = @ID union select max([ID]) from [tblImages] where [ID] < @ID union select min([ID]) from [tblImages] where [ID] > @ID HTH, Show quote "Andrew Morton" <a**@in-press.co.uk.invalid> wrote in message news:%23y77pwh5FHA.2816@tk2msftngp13.phx.gbl... > Neo Geshel wrote: >> Andrew Morton wrote: >>> For the next ID, isn't is simpler to get the minimum ID larger than >>> the specified one? >>> >>> SELECT MIN(ID) FROM [tblImages] WHERE ID>@id; >> >> Yes, but will it work with Access? This is only a small site, I don't >> need SQL Server just to make the SQL queries work. > > Ooh, didn't check that bit. I don't have Access here. What happens when > you try it on your test copy of the database? > > Andrew > Onin Tayson wrote:
Show quote > "Andrew Morton" <a**@in-press.co.uk.invalid> wrote in message I've actually broken up all three queries, so they work independant of > news:%23y77pwh5FHA.2816@tk2msftngp13.phx.gbl... >>Neo Geshel wrote: >>>Andrew Morton wrote: >>>>For the next ID, isn't is simpler to get the minimum ID larger than >>>>the specified one? >>>> >>>>SELECT MIN(ID) FROM [tblImages] WHERE ID>@id; >>> >>>Yes, but will it work with Access? This is only a small site, I don't >>>need SQL Server just to make the SQL queries work. >> >>Ooh, didn't check that bit. I don't have Access here. What happens when >>you try it on your test copy of the database? >> > i think it will work. btw, i tested the SQL i sent you and it's not not > working properly... sorry 'bout that. I tried the code below (using SQL > Server though) and i think it's what you're looking for... > > select [ID] from [tblImages] where [ID] = @ID union > select max([ID]) from [tblImages] where [ID] < @ID union > select min([ID]) from [tblImages] where [ID] > @ID > each other. I still have to test them properly, as I don't have a reliable way of inserting the name of the table into the stream extracted from the database and sent on to the Repeater. The Repeater is used to build the HTML code for bringing in the Image Viewer, which is another aspx page which draws the image from the database. Unfortunately, this image viewer requires the table name in which the image rests, and I don't know how to dynamically insert this into the data sent to the Repeater. If you are confused, think of the set up this way: Many pages that have image galleries all reference different tables that contain images. Each table that contain images have the same columns as the others, namely ID and Comment, but the tables themselves are named differently from each other (duh!). The problem arises because I want to have one Gallery Viewer that all these different galleries can reference. The galleries are simple pages filled with thumbnails linked to the gallery viewer. The gallery viewer is a single page with the current image at full size, and a prev and a next thumbnail image. The images are drawn from the database via an aspx page that requires the database name and the ID of the image itself. I need to have the table name preserved across two "hops" - one from the thumbnail link to the Gallery Viewer, and then from the Gallery Viewer to the ImageViewer.aspx page. As such, because the three images on the Gallery Viewer are "determined" by your SQL, and then assembled by a Repeater, I need to have name of the table inserted into the HTML that references the Image Viewer aspx file so it knows which table to grab. FYI, I've made another post asking for just this info. ...Geshel -- *********************************************************************** * My reply-to is an automatically monitored spam honeypot. Do not use * * it unless you want to be blacklisted by SpamCop. Please reply to my * * first name at my last name dot org. * *********************************************************************** “Anyone who believes in Intelligent Design (“creationismâ€) is just as ignorant and ill-educated as someone who believes that the world is flat, that the Sun circles the Earth or that there really is a tooth fairy. Darwinism has an overwhelming foundation of evidence that can be tested and reproduced. Intelligent Design, on the other hand, has no evidence at all; not one single shred of testable proof. As such, Intelligent Design is Religious Mythology, and has no right whatsoever to be in our Science classrooms.†- 99.99+% of Scientists *********************************************************************** Mignon McLaughlin once said that “A nymphomaniac is a woman [who is] as obsessed with sex as the average man.†Unfortunately, since true nymphomaniacs are so rare, this means that it takes an extraordinary woman to keep up with an ordinary man. ***********************************************************************  |
|||||||||||||||||||||||
Other interesting topics