ASP.NET Forum

**andrew99** · 12-15-2010, 06:08 PM

I'm keep getting stuck here. I have a site page which is pulling off an access database (it shows the records on our site). I have a column in the database called "Region"

I am trying to get the page where it will put up records that have a region of 316, and not any other records. Here is the original "if statement" which pulls of the regions. Any ideas on how I can code this to get it to work correctly? Thank you for any help.

If (Len(Request("region")) = 3) Then
If(Request("region") = "swg") Then
region = " AND (Region='316' OR Region='317' OR Region='398')"
Else
region = " AND Region='" & Request("region") & "'"
End If
Else

**chrishirst** · 12-15-2010, 06:19 PM

Is "region" numeric or character data?

And what does the request collection have to do with the database?

Where is the SQL code to return the recordset?

**andrew99** · 12-15-2010, 06:40 PM

chrishirst:

the region column is numeric.

Not sure about the second question except that the website page is pulling records out of the database to display on the page.

As far as the third question, I included the code for the whole page here:

Code:

<% option explicit
Response.Buffer=True

Dim Connect, Query, Search, Stagger, tint, region

Set Connect = Server.CreateObject("ADODB.Connection")
Connect.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0; DATA SOURCE=" & server.mappath("access_db/wedtally.mdb")

If (Len(Request("region")) = 3) Then
    If(Request("region") = "swg") Then
        region = " AND (Region='316' OR Region='317' OR Region='398')"
    Else
        region = " AND Region='" & Request("region") & "'"
    End If
Else
    region = ""
End If

Query = "SELECT * FROM [Brides and Grooms] WHERE" & _
        " (WeddingDate >= #" & Date & "#" & region & ") OR " & _
        " (Year > " & Year(Date) & region & ") OR " & _
        " (Year = " & Year(Date) & " AND Month >= " & Month(Date) & region & ")" & _
        " ORDER BY Year, Month, Day, WeddingDate" 
%>
<html>
<head>
</head>

<style>
.main    {font-family:arial; font-size:12pt; font-weight:bold}
td        {font-size:10pt}
    </style>

<body>
<p align=center>
Engagements are listed by wedding date and only show weddings from this month and beyond.<br><br>
Choose from the sort order and date options below for a different view of registered brides.
<form method=post action=engage-custom.asp>
<table width=75% align=center border=0 cellspacing=2>
<tr bgcolor=AliceBlue>
    <td colspan=3 align=center>
<select name=sort>
<option value=current>-- Select One
<option value=bride>Bride's Last (Maiden) Name
<option value=groom>Groom's Last Name
<option value=current>Wedding Date (Ascending)
<option value=distant>Wedding Date (Descending)
</select>
    </td>
</tr>
<tr bgcolor=AliceBlue>
    <td><input type=radio name=dates value=old>Prior to current month</td>
    <td><input type=radio name=dates value=new checked>This month and following</td>
    <td><input type=radio name=dates value=all>All registered brides</td>
</tr>
<tr bgcolor=AliceBlue>
    <td colspan=3 align=center>
    <input type=hidden name=region value="<% =region %>">
    <input type=submit value="Submit Options">
    </td>
</tr>
</table>

<br><br>

<table width=75% align=center cellspacing=0>

<% Set Search = Connect.Execute(Query)  
Stagger = 1
tint = "gainsboro"

dim brides
brides = 0

Do until Search.EOF 

 %>
<tr bgcolor = <% =tint %>>
    <td rowspan=2 class=main><a href="engagements.asp?entryno=<% =Search("entryno") %>"><% =Search("LastName") & " - " & Search("GroomsLast") %></a></td>
    <td><% =Search("firstname") & " " & Search("lastname") %></td>
    <td rowspan=2>
    <% If Len(Search("weddingdate"))<1 Then
    Response.Write MonthName(Month(Search("Month"))) & " " & Search("Year")
    Else
    Dim WedDate
    WedDate = Search("weddingdate")
        If IsNull(WedDate) AND IsNull(Search("Month")) AND Search("Year")>0 Then
        Response.Write Search("Year")
        ElseIf IsNull(WedDate) Then
        Response.Write MonthName(Search("Month")) & " " & Search("Year")
        Else
        Response.Write MonthName(Month(weddate)) & " " & Day(weddate) & ", " & Year(weddate)
        End If
    End If %></td>
</tr>
<tr bgcolor = <% =tint %>>
    <td><% =Search("groomsfirst") & " " & Search("groomslast") %></td>
</tr>
<% 
Search.MoveNext

Stagger = Stagger+1
If Stagger=3 Then
Stagger=1
tint = "Gainsboro"
Else
tint = "white"
End If


brides = brides + 1

Loop %>
</table>

<% 
    'Response.Write "<h1>" & brides & "</h1>" 
    'Response.Write "<b>" & query & "</b>" 
%>
</body>
</html>