Help with SELECT

Can anyone point me in the right direction, the code below returns results but the date range imposed by the BETWEEN command doesn't seem to work, asall the results from the table are shown.

<asp:SqlDataSourceID="ResultsSqlDataSource1"runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT DISTINCT venue, town, artist, date FROM gigs
WHERE (town +' (Town)' = @.town) OR (venue +' (Venue)' = @.venue) OR (artist +' (Artist)' = @.artist)
AND date BETWEEN CONVERT(DATETIME,'25/11/2007',103) AND CONVERT(DATETIME, '26/11/2007',103)">
<SelectParameters>
<asp:controlparameterControlID="TextBox1"Name="town"PropertyName="Text"/>
<asp:controlparameterControlID="TextBox1"Name="venue"PropertyName="Text"/>
<asp:controlparameterControlID="TextBox1"Name="artist"PropertyName="Text"/>
</SelectParameters>
</asp:SqlDataSource>

Regards

Tino

What format is your date column in the DB table? You can try using:

WHERE...AND date <='25/11/2007' AND date >= '26/11/2007'

Hope this helps,

Vivek

|||

Vivek

Do you mean the date type, if so its a datetime.

Thanks

Tino

|||

Hitinomclaren ,

try like this

ANDCONVERT(DATETIME,'date',101) BETWEEN CONVERT(DATETIME,'2007-11-25',101) AND CONVERT(DATETIME,'2007-11-26',101)

or

AND (date > '2007-11-25' anddate < '2007-11-26' )

Regards,
Shri

|||

Its a good practice to apply the conversion to all the participants in any comparison. So, your query should be like

"SELECT DISTINCT venue, town, artist, date FROM gigs
WHERE (town +' (Town)' = @.town) OR (venue +' (Venue)' = @.venue) OR (artist +' (Artist)' = @.artist)
ANDCONVERT(DATETIME,date,103) BETWEEN CONVERT(DATETIME,'25/11/2007',103) AND CONVERT(DATETIME, '26/11/2007',103)"

Hope this helps.

|||

Thanks for suggestions, they didn't work for me

I really think im missing something fundamental now because all the posts i've trawled through have suggested similar solutions.

After 2 days at this one problem im trying not to get down but its really getting hard now

My database is SQLExpress and i've used the datetime datatype in the date colum on the DB. Im using VS2008 beta 2.

I have 20 test records in my DB and all have the date column filled in, im not using thetimepart but they are there in the DB and all default to midnight 00:00:00.

I dont know how to use stored procedures yet but im looking at the video tutorial form thelearn part of this site to hopfully get to grips with it, im assuming that the examples you have given me will work in either on the page (in the SqlDataSource as above) or in a Stored Procedure. Is there any Difference in the Syntax when switching between the two?

When I tried the suggestions you gave me using the Convert way I get ALL the records in the DB and when I use the < > way I get No records.

What am i doing wrong?

thx

tino

|||

Can you post the table structure along with some sample records, so that we can understand your problem better ?

|||

Here's the Table structure......

Coumn Name Data Type

gigId int ---> Primary Key, Is Identity-->Yes

venue varchar(50)

town varchar(50)

artist varchar(50

date datetime

I've looked at the datatypes available and there isn't just a date (without the time) must be an SqlExpress thing.

I cant even hard code 1 date in the SELECT command i.e.

SelectCommand="SELECT DISTINCT venue, town, artist, date FROM gigs WHERE (town + ' (Town)' = @.town) OR (venue + ' (Venue)' = @.venue) OR (artist + ' (Artist)' = @.artist)AND (date = '25/11/2007') ">

thx tino

|||

tinomclaren:

Thanks for suggestions, they didn't work for me

I really think im missing something fundamental now because all the posts i've trawled through have suggested similar solutions.

Yes, you are missing something fundamental. :)

You cannot mix AND with OR as you have done and expect to get correct results.

In your example, all the ORs need to be within a set of () .

() must be used to delimit the logic boundaries of the ANDs and ORs.

Example:

If I tell you I will only date women who have red hair and blue eyes or green eyes, what do I mean?

Do I mean the women must have red hair - then they must also have blue eyes or green eyes?

Or would a blonde with green eyes qualify?

My original statement was ambiguous.

If, instead, I said, "I will only date women with red hair and (blue eyes or green eyes)", then the blonde with green eyes won't qualify.

If I said "I will only date women with (red hair and blue eyes) or green eyes" then the blonde with green eyes will qualify. So would a redhead with green eyes, for that matter.

HTH

|||

David,

Once again you have saved my life. I can only hope that one day I will be as experienced as you.

Two days for 2 brackets........I wont forget this.

Thankyou

Tino

|||

Thanks everyone for your help....All answers turned out to be correct......this forum is simply superb :)

For anyone with a similar problem here is the final code I used, which works perfectly....

<asp:SqlDataSourceID="ResultsSqlDataSource1"runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT DISTINCT venue, town, artist, date FROM gigs WHERE((town + ' (Town)' = @.town) OR (venue + ' (Venue)' = @.venue) OR (artist + ' (Artist)' = @.artist)) AND CONVERT(DATETIME,date,103) BETWEEN CONVERT(DATETIME,'25/11/2007',103) AND CONVERT(DATETIME, '26/11/2007',103) ORDER BY date asc">
<SelectParameters>
<asp:controlparameterControlID="TextBox1"Name="town"PropertyName="Text"/>
<asp:controlparameterControlID="TextBox1"Name="venue"PropertyName="Text"/>
<asp:controlparameterControlID="TextBox1"Name="artist"PropertyName="Text"/>
</SelectParameters>
</asp:SqlDataSource>

Brackets inbold made the whole thing work correctly

thnx

Tino