Date Range lookup between tables in asp 2.0

[Guest]

Hi all,

I am (using ASP.net 2.0 in Visual Web developer 2005 Express Edit) trying to
set up an asp front end to a music DB which has 2 main tables:

1) MUSIC - containing music release information (eg label, artist, name,
date reviewed)
2) ISSUES - containing 3 columns - 1) a list of issue numbers and 2) issue
start date and 3) Issue end date columns

I am (a very novice programmer) having difficulty with presenting search
data in a gridview after a user enters the "date reviewed" details in a text
box and hits a button to perform the search.

user enters date and i am trying to get it to lookup the list of dates in
the second table and return the issue number

i create the textbox and button and then create a gridview connecting back
to the db and am getting stuck from here.

I have tried "ADD WHERE" with various SQL Queries/criteria and thought it
could be done in a "RangeValidator" but have had no joy as the upper and
lower ranges are dependent on what is entered in the textbox and must be
looked up from a separate table. can thios be done in RangeValidator?

one string i tried (which returns everything not just the week in Q) is as
follows:

select NEWMUSIC.MusicID, NEWMUSIC.MusicReleaseDate, NEWMUSIC.MusicTitle,
NEWMUSIC.MusicArtist, NEWMUSIC.MusicLabel, NEWMUSIC.MusicReviewedDate,
ISSUEDATES.Issue#
from NEWMUSIC INNER JOIN ISSUEDATES ON NEWMUSIC.MusicReleaseDate Between
ISSUEDATES.ISSUE_START_DATE and ISSUEDATES.ISSUE_END_DATE

I am really struggling here so would appreciate any assistance!

thanks in advance

Nick

 

[Tim_Mac]

hi,
seems to me that there is no relationship between the two tables, so you
can't do a relational join on them. i'm not surprised you're getting too
many records.

from your question, it looks like you only want the issue number, so i would
change your query to something like this:

select ISSUEDATES.Issue#
from ISSUEDATES WHERE [UserDate] Between
ISSUEDATES.ISSUE_START_DATE and ISSUEDATES.ISSUE_END_DATE


i've never used the Between operator with the first operand as the
user-provided one, so if the above doesn't work, you can use this:
WHERE ISSUE_START_DATE <= [UserDate] AND ISSUE_END_DATE >= [UserDate]

i can't see any sensible way for you to combine an issue row with a music
row as your query attempts to do. you can try to hack together two tables
that have nothing to do with each other, but this gets messy at the best of
times.

my 2 cents advice would be to go and read up on relational databases,
especially "normalisation", before you go any further with the project. a
poor database design will cause endless maintenance and bugs further down
the road. a good database design means you do your job easier and better in
less time, and you get to go home early

hope this helps
tim

 

[Markus Palme]

I have tried "ADD WHERE" with various SQL Queries/criteria and thought it

could be done in a "RangeValidator" but have had no joy as the upper and
lower ranges are dependent on what is entered in the textbox and must be
looked up from a separate table. can thios be done in RangeValidator?

A range validator can be used to check (validate) user input - for
instance you can use it to ensure that a entered number is between 10
and 20.

one string i tried (which returns everything not just the week in Q) is as
follows:
[...]

You need to join on an id column that exists in both tables not on the
date. I don't know how it's called, it should look like this
(completely untested code):

select
NEWMUSIC.MusicID,
NEWMUSIC.MusicReleaseDate,
NEWMUSIC.MusicTitle,
NEWMUSIC.MusicArtist,
NEWMUSIC.MusicLabel,
NEWMUSIC.MusicReviewedDate,
ISSUEDATES.Issue#
from NEWMUSIC
INNER JOIN ISSUEDATES ON
NEWMUSIC.MusicID = ISSUEDATES.MusicID
WHERE
ISSUEDATES.ISSUE_START_DATE > NEWMUSIC.MusicReleaseDate
AND
ISSUEDATES.ISSUE_END_DATE < NEWMUSIC.MusicReleaseDate

 

[Guest]

Hi guys thanks for the help thus far

re normalisation i understand what that means just figured the way i set it
out was the best structure/design:

1 table detailing music release information (including release date)
a 2nd table detailing magazine issue number and start and end dates for the
week it hits the streets (ie a weekly magazine)
goal is for a user to be able to enter a date in text box/search field hit a
button and it automatically can work out what week and issue it falls within
and display that along with the other info on each music item.

re the code markus still struggling to get it to work within the asp - see
below for an attempt to add it near the connection string details for the
datagrid that will display the info. am really stuck here so sorry if this is
really obvious for you seasoned designers!

<asp:SqlDataSource ID="releaseweekSqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:MUSICConnectionString1 %>"
SelectCommand="SELECT * FROM [NEWMUSIC] WHERE
([MusicReleaseDate] = @MusicReleaseDate)and select [Issue#] from [issues]
INNER JOIN ISSUEDATES ON NEWMUSIC.MusicID = ISSUEDATES.Issue#
WHERE [ISSUE_START_DATE] >= [MusicReleaseDate] AND
ISSUEDATES.ISSUE_END_DATE < NEWMUSIC.MusicReleaseDate"
<SelectParameters>

thanks again!

 

[Markus Palme]

What exactly is your problem now? Binding the result of the query to a
GridView?

-Markus

 

[Guest]

yup the syntax of getting that into the aspx doc (vs a straight SQL query)
and where it should be positioned?

can I do it in the GUI dev tool and specify the select statement for the
gridview?

if so how?

thanks again!