Populating a Listbox

[Broder]

Hi there,

I am currently running into a somewhat weired problem and hope that this NG
is able to help ;-)

I have a table in a MSSQL Server in which there is one Column that stores a
date. The Format is 04.03.2004

Now I want to populate a Listbox but only with the year from that Column. So
if I have lets say 10 entries with *.*.2004 and 5 with *.*.2003 I just want
to show 2003 and 2004 once in the Listbox.
Basically intended for a Searchform to search for entries from a specific
year.
I can get the Listbox to display the years allright but if I have 1000
entries in the Column I gett as much in the Listbox.
This is the Code I have so far

<select name="select">
<%
While (NOT rsMyDate.EOF)
%>
<option value="<% Response.Write (Month
(rsMyDate.Fields.Item("Date").Value)) %>"><% Response.Write (Month
(rsMyDate.Fields.Item("Date").Value)) %></option>
<%
rsMyDate.MoveNext()
Wend
If (rsMyDate.CursorType > 0) Then
rsMyDate.MoveFirst
Else
rsMyDate.Requery
End If
%>
</select>


Any ideas are greatly appreciated.

Cheers

Btw..sorry 4 crossposting ;-)

 

[Chris Barber]

Why is the date stored as a string and not a datetime value? If it was a
datetime value then you could use the intrinsic SQL functions to return the
relevant date part.

Chris.

Hi there,

I am currently running into a somewhat weired problem and hope that this NG
is able to help ;-)

I have a table in a MSSQL Server in which there is one Column that stores a
date. The Format is 04.03.2004

Now I want to populate a Listbox but only with the year from that Column. So
if I have lets say 10 entries with *.*.2004 and 5 with *.*.2003 I just want
to show 2003 and 2004 once in the Listbox.
Basically intended for a Searchform to search for entries from a specific
year.
I can get the Listbox to display the years allright but if I have 1000
entries in the Column I gett as much in the Listbox.
This is the Code I have so far

<select name="select">
<%
While (NOT rsMyDate.EOF)
%>
<option value="<% Response.Write (Month
(rsMyDate.Fields.Item("Date").Value)) %>"><% Response.Write (Month
(rsMyDate.Fields.Item("Date").Value)) %></option>
<%
rsMyDate.MoveNext()
Wend
If (rsMyDate.CursorType > 0) Then
rsMyDate.MoveFirst
Else
rsMyDate.Requery
End If
%>
</select>


Any ideas are greatly appreciated.

Cheers

Btw..sorry 4 crossposting ;-)

 

[Broder]

Hi there,

excuse my ignorance but I have to confess I never heard of them ;-).
Actually in the DB it is stored in datetime

Broder

 

[Chris Barber]

In which case use the DatePart function to output the specific part of the
datetime value that you want. It's also better when updating the database to
pass in an ISO datetime string in the SQL so that the database cannot
misinterpret your dates as either US or UK etc.

http://www.databasejournal.com/features/mssql/article.php/2197931
http://www.databasejournal.com/features/mssql/article.php/10894_2209321_2
http://www.techonthenet.com/access/functions/date/datepart.htm
http://www.informit.com/articles/article.asp?p=31453

Chris.

Hi there,

excuse my ignorance but I have to confess I never heard of them ;-).
Actually in the DB it is stored in datetime

Broder