I´m trying to build a page where you can search events happening today, this week or this month..
I´m using SQL Server7 and this SQL sentence

SELECT * FROM gigs
WHERE date =< Dateadd("d",+'vardays',getdate()) AND
locationID = 'varLocation'


The variable vardays says how far in the future you want to search the events..
And everytime i´m getting SQL Server Errors..
Any Help?

Does Ultradev have any problem with Date functions?

Edited by - capuleto on 08/24/2001 20:06:40

Try this:

I assume <b>vardays</b> and <b>varlocation</b> are numeric so they should not be enclosed in quotes, else they will be treated as strings.

SELECT * FROM gigs
WHERE date &lt;= Dateadd("d",vardays,getdate()) AND
locationID = varLocation


Regards

Owen.

Thanx indeed..
So if I want to retrieve the events of all locations.. What generic value should I use?
I´m trying with % but it´s not valid.. and every time I try to test the recordset it gives me back an error.. but if i use 0 as default value, i´m getting back an empty rs..
What´s the generic value for a numeric column?


TC

Edited by - capuleto on 08/24/2001 21:50:05

You need to create a WHERE clause that is true for all loctations.

You are looking for a match with an ID number, presumably a database auto generated field, which will therefore always be positive (unless you've set up something very strange).

I assume that you are entering the default value in the Ultradev Recordset dialogue box Default Value field.

You could use <b>&lt;&gt; -1</b> so that the default WHERE clause would be:

WHERE date &lt;= Dateadd("d",vardays,getdate()) AND
locationID &lt;&gt; -1

Regards

Owen.



Edited by - oeastwick on 08/24/2001 21:58:51

Alright.. but the idea is that the users could choose the 2 variables.. one the City and the second one, the period of time..
But i don´t actually get your point..
If i change the default value to &lt;&gt;-1, I could get a weird WHERE clause

WHERE date &lt;= Dateadd("d",vardays,getdate()) AND
locationID =&lt;&gt; -1

So if i use 0 as a default value.. it wouldn´t match with any of them.. So my question is still Is there any default value as % for text that i could use in "int" column?

Thanx again..
TC

TC

OK, in the code view you now have something like this:

<pre id=code><font face=courier size=2 id=code>
&lt;%
Dim Recordset1__vardays
Recordset1__varDays = "0"
if (Request("vardays" &lt;&gt; "" then Recordset1__vardays = Request("vardays"
%&gt;
&lt;%
Dim Recordset1__varLocation
Recordset1__varLocation = "&lt;&gt; -1"
if (Request("varlocation" &lt;&gt; "" then Recordset1__varLocation = Request("varlocation"
%&gt;
&lt;%
set Recordset1 = Server.CreateObject("ADODB.Recordset"
Recordset1.ActiveConnection = MM_TDSFdemoOLE_STRING
Recordset1.Source = "SELECT * FROM gigs WHERE date &lt;= Dateadd("d"," + Replace(Recordset1__vardays, "'", "''" + ",getdate()) AND locationID = " + Replace(Recordset1__varLocation, "'", "''" + ""
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 3
Recordset1.Open()
Recordset1_numRows = 0
%&gt;
</font id=code></pre id=code>

Modify it as follows:

<pre id=code><font face=courier size=2 id=code>
&lt;%
vardays = Request("vardays"
If vardays = "" Then
vardays = 0
End If
%&gt;
&lt;%
varlocation = Request("varlocation"
If varlocation = "" Then
varlocation = "&lt;&gt; -1"
Else
varlocation = "= " & varlocation
End If
%&gt;
&lt;%
set Recordset1 = Server.CreateObject("ADODB.Recordset"
Recordset1.ActiveConnection = MM_TDSFdemoOLE_STRING
Recordset1.Source = "SELECT * FROM gigs WHERE date &lt;= Dateadd("d"," & vardate & ",getdate()) AND locationID " & varlocation
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 3
Recordset1.Open()
Recordset1_numRows = 0
%&gt;
</font id=code></pre id=code>

Should work.


Regards

Owen.



Edited by - oeastwick on 08/25/2001 00:11:22

I don´t know what to say..
Cheers mate..

Do you know any good SQL manual? I actually don´t mind if it´s a printed book..
Thanx again..


TC

No problem.

I've never read a book dealing specifically with SQL, but there is a section that deals with it in <b>The Complete Reference Dreamweaver UltraDev 4 - Ray West, Tom Muck & Tom Allen</b>. Also, for more complicated SQL statements including joins, I use the Query designer in Access or SQL Server to get the basics then add my WHERE clauses etc., either in the code view or the Recordset Dialogue box.

The final solution I provided is more to do with modifying VBScript than SQL. I often find that it's not possible to get the results I wan't from the Ultradev Recordset dialogue box alone and that it is necessary to modify the code directly. You can take a look at a tutorial I have written:

www.tdsf.co.uk/tdsfdemo/

It's quite extensive and covers some SQL, and VBScript issues and in particular building dynamically generated WHERE clauses in order to deal with multiple search parameters.


Regards

Owen.



Edited by - oeastwick on 08/27/2001 02:36:03