473,320 Members | 1,902 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Date Range lookup between tables in asp 2.0

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
Sep 18 '06 #1
5 1801
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

Sep 18 '06 #2
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
"NickCR_04" <Ni******@discussions.microsoft.comwrote in message
news:66**********************************@microsof t.com...
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


Sep 18 '06 #3
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!
Sep 18 '06 #4
What exactly is your problem now? Binding the result of the query to a
GridView?

-Markus

Sep 19 '06 #5
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!
"Markus Palme" wrote:
What exactly is your problem now? Binding the result of the query to a
GridView?

-Markus

Sep 19 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: CoreyWhite | last post by:
The future of computer architecture will use lookup tables. Currently computer processor speed outweighs the benefits of using computer memory for lookup tables, except in some cases. As computer...
6
by: BlackFireNova | last post by:
Using Access 2002 I am writing a report which draws data from several different tables. I can't link all the tables in a query, as some can not be related without truncating the data. I plan...
1
by: FatBoyThin | last post by:
I love it when I back myself into a corner. Seems to be the fastest way to learn. I have 3 tables; tblINBOX, tblOUTBOX and tblPENDING The relevant rows are as following; Date, userID userID is...
3
by: my-wings | last post by:
I've been reading about how evil Lookup fields in tables are, but I've got to be missing something really basic. I know this subject has been covered before, because I've just spent an hour or two...
3
by: google | last post by:
I have a database with four table. In one of the tables, I use about five lookup fields to get populate their dropdown list. I have read that lookup fields are really bad and may cause problems...
10
by: RoadRunner | last post by:
Hi, I have a employee vacation database that has a vacation table that has the employee name, pay week and date of vacation. I have another lookup table with pay week code and date range for the...
6
by: vdicarlo | last post by:
I am a programming amateur and a Python newbie who needs to convert about 100,000,000 strings of the form "1999-12-30" into ordinal dates for sorting, comparison, and calculations. Though my script...
3
by: Harlequin | last post by:
I must start this posing by making the point that I am NOT a VB programmer and I'm something of a Newbie to MS Access. I can program in a number of languages (Java, Javascript, PERL,PHP and TCL) but...
2
by: wevans | last post by:
I have 3 tables, Customer, Offsite Service and service. I need to create a sales report/query based on the results of the user input, which is a date range. I've got it working great with two of the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.