By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,146 Members | 1,820 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,146 IT Pros & Developers. It's quick & easy.

Populating a Listbox

P: n/a
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 ;-)
Jul 19 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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.

"Broder" <br**********@gmx.net> wrote in message
news:c2*************@news.hansenet.net...
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 ;-)

Jul 19 '05 #2

P: n/a
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" <ch***@blue-canoe.co.uk.NOSPAM> schrieb im Newsbeitrag
news:OY**************@TK2MSFTNGP09.phx.gbl...
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.

"Broder" <br**********@gmx.net> wrote in message
news:c2*************@news.hansenet.net...
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 ;-)

Jul 19 '05 #3

P: n/a
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/featu...le.php/2197931
http://www.databasejournal.com/featu...0894_2209321_2
http://www.techonthenet.com/access/f...e/datepart.htm
http://www.informit.com/articles/article.asp?p=31453

Chris.

"Broder" <br**********@gmx.net> wrote in message
news:c2*************@news.hansenet.net...
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" <ch***@blue-canoe.co.uk.NOSPAM> schrieb im Newsbeitrag
news:OY**************@TK2MSFTNGP09.phx.gbl...
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.

"Broder" <br**********@gmx.net> wrote in message
news:c2*************@news.hansenet.net...
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 ;-)


Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.