www.webdeveloper.com
Results 1 to 2 of 2

Thread: Select statement

  1. #1
    Join Date
    Jan 2003
    Location
    PA
    Posts
    1,898

    Select statement

    I have a page where I am listing the months that I hve data for in my database. I am getting the first 2 digits from my date_show column and then using a case statement to determine what month it is. I want to be able to click on the Month name, and it only show me those items that belong to that month. I am passing the month value (Ex. 10 for October, 11 for November, etc. How can I get my select statement to work because the date_show field is defined as a date. Below is the code I have so far (maybe there is an easier way to do this, if so, please advise):
    Code:
    <%
    Dim iPageSize       'How big our pages are
    Dim iPageCount      'The number of pages we get back
    Dim iPageCurrent    'The page we want to show
    Dim iRecordsShown   'Loop controller for displaying iPageSize records
    Dim I               'Standard looping var
    
    ' Get parameters
    iPageSize = 10 ' You could easily allow users to change this
    
    ' Retrieve page to show or default to 1
    If Request.QueryString("page") = "" Then
    	iPageCurrent = 1
    Else
    	iPageCurrent = CInt(Request.QueryString("page"))
    End If
    
    db = "/db/ziondb.mdb"
    %>
    
    <!--#include virtual="/inc/dbconn.asp"-->
    <%
    sel_month = Request.Querystring("months")
    Select Case True
    Case (trim(request.querystring("months"))) = ""
        SQLstmt = "SELECT * from Bible_Verses"
    Case (trim(request.querystring("months"))) <> ""
    	SQLstmt = "SELECT * from Bible_Verses where Left(date_show,2) = " & sel_month & " order by date_show"
    End Select	
    Set objPagingRS = Server.CreateObject("adodb.recordset")
    objPagingRS.PageSize = iPageSize
    objPagingRS.CacheSize = iPageSize
    Response.Write(SQLstmt)
    ' Open RS
    objPagingRS.Open SQLstmt,conn,3,3
    
    
    ' Get the count of the pages using the given page size
    iPageCount = objPagingRS.PageCount
    
    ' If the request page falls outside the acceptable range,
    ' give them the closest match (1 or max)
    If iPageCurrent > iPageCount Then iPageCurrent = iPageCount
    If iPageCurrent < 1 Then iPageCurrent = 1
    
    ' Check page count to prevent bombing when zero results are returned!
    If iPageCount = 0 Then
    %>
      <tr>
                      <td valign=top align=center> 
                        <p align="left"><img src="../images/bible_verse_top.gif" width="403" height="28"></p>
                        <p>No 
                          records 
                          found!</p>
          <a href="index.asp" onFocus="if(this.blur)this.blur()">Back to Bible Verse Listing</a>
        </td>
      </tr>
    <%
    Else
    	' Move to the selected page
    	objPagingRS.AbsolutePage = iPageCurrent
    %>
    			<tr>
    				<td colspan="3"><div align="center">							
    				<%
    				Set objMonthsRS = Server.CreateObject("adodb.recordset")
    				SQL_query = "SELECT distinct(left(date_show,2)) AS Months FROM Bible_Verses"
    				objMonthsRS.Open SQL_query,conn,3,1
    				
    		Do While Not objMonthsRS.eof 
    		months = objMonthsRS("Months")
    		if Right(months,1) = "/" then
    			months = "0" & Left(months,1)
    		end if
    		Select Case months 
    		Case "01" 
    			MonthNme = "January" 
    		Case "02" 
    			MonthNme = "February" 
    		Case "03" 
    			MonthNme = "March" 
    		Case "04" 
    			MonthNme = "April"
    		Case "05" 
    			MonthNme = "May" 
    		Case "06" 
    			MonthNme = "June"
    		Case "07" 
    			MonthNme = "July"
    		Case "08" 
    			MonthNme = "August"
    		Case "09" 
    			MonthNme = "September"
    		Case "10" 
    			MonthNme = "October"
    		Case "11" 
    			MonthNme = "November"
    		Case "12" 
    			MonthNme = "December"
    		End Select 
    				%>
                        <a href="index.asp?months=<%=months%>" onFocus="if(this.blur)this.blur()"><%=MonthNme%></a>
    				<%	objMonthsRS.movenext 
    				Loop
    			%>
    
    				</div>
    				
    				</td>
    			</tr>
    <%
    x=0
    Dim iRecordCount,mycolor
    iRecordCount = 0
    mycolor = "#ffffff"
    Do while iRecordCount < iPageSize And NOT objPagingRS.EOF
    If mycolor = "#ffffff" Then 
    			mycolor = "#d7d7ff" 
    		Else 
    			mycolor = "#ffffff" 
    		End If 
    		
    strdate = objPagingRS("date_show")
    bible_verse = objPagingRS("bible_verse")
    %>
                    <tr valign="top" bgcolor="<%= mycolor %>"> 
                      <td> 
                        <a href="update.asp?strdate=<%= strdate%>" onFocus="if(this.blur)this.blur()"><%= strdate %></a> 
                      </td>
                      <td> 
                        <% = bible_verse %> 
                      </td>
                      <td nowrap><a href="delete.asp?strdate=<%= strdate%>" onFocus="if(this.blur)this.blur()">Delete</a></td>
                    </tr>
                    <% 
    				iRecordCount = iRecordCount + 1
    				objPagingRS.MoveNext
    	Loop
    	conn.Close
    	'objPagingRS.Close
    	Set conn = nothing
    	Set SQLstmt = nothing
    	Set objPagingRS = Nothing	
    	
    %>

  2. #2
    Join Date
    Jan 2003
    Location
    PA
    Posts
    1,898
    Thanks, that works!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles