Work well with Access but not SQL

tomatochiang · January 19th, 2004, 05:22 AM

I download the code from the Beginning Active Server Pages 3.0 and the BrowseListing.asp in chapter 12 code can't work with SQL Server.Why?

<BASEFONT FACE="Comic Sans MS" COLOR="DarkBlue">
<HTML>
<HEAD>
<TITLE>Wrox Classifieds</TITLE>
</HEAD>

<BODY BGCOLOR="#FFFF80">
<CENTER><H1>Wrox Classifieds<BR>Items for sale</H1>
<%
  If Session("PersonID") <> "" Then
    Response.Write "<H3>Welcome " & Session("GivenName") & "</H3>"
  End If
%>
</CENTER>
<P>Here's a list of all the items that our users have made available for purchase:
</P>
<%
  Dim rsItems
  strSQL = "SELECT * FROM Item " & _
           "WHERE ExpirationDate > #" & FormatDateTime(Now,2) & "# " & _
           "AND ItemStatus = 'Active';"
  Set rsItems = Server.CreateObject("ADODB.Recordset")
  rsItems.Open strSQL, objConn
  If Not rsItems.EOF Then
    Response.Write _
      "<TABLE BORDER=""1"" CELLSPACING=""3"" CELLPADDING=""3"">" & _
      " <TR>" & _
      " <TH>Item ID"
               If Session("PersonID") <> "" Then
                 Response.Write "<BR>Click to Bid"
               End If
           Response.Write "</TH>" & _
      " <TH>Name</TH>" & _
      " <TH>Asking Price</TH>" & _
      " <TH>Listing Date</TH>" & _
      " <TH>Current Bid</TH>" & _
      " <TH>Bid Time</TH>" & _
      " </TR>"
    Do While Not rsItems.EOF
      Response.Write "<TR ALIGN=CENTER>"
      If Session("PersonID") <> "" Then
        Response.Write _
          "<TD><A HREF=""Bid.asp?Item=" & rsItems("ItemID") & """>" & _
          rsItems("ItemID") & "</A></TD>"
      Else
        Response.Write "<TD>" & rsItems("ItemID") & "</TD>"
      End If
      Response.Write _
        "<TD>" & rsItems("ItemName") & "</TD>" & _
        "<TD>" & FormatCurrency(rsItems("AskingPrice")) & "</TD>" & _
        "<TD>" & FormatDateTime(rsItems("ListingDate"),2) & "</TD>" & _
      "</TR>"
      rsItems.MoveNext
    Loop
    Response.Write "</TABLE>"
    rsItems.close
    Set rsItems = Nothing
  Else
    Response.Write "<CENTER><H2>No items currently for sale</H2></CENTER>"
  End If
%>
<P>

<TABLE BORDER=0 WIDTH=100%>
  <TR ALIGN=CENTER>
    <% If Session("PersonID") <> "" Then %>
      <TD WIDTH=33%>Browse the listings</TD>
      <TD WIDTH=33%><A HREF="ViewMySaleItems.asp">List/Edit Sale Items</a></TD>
      <TD WIDTH=33%><A HREF="Register.asp?Update=True">
                    Edit Registration Info</A></TD>
    <% Else %>
      <TD WIDTH=33%>Browse the listings</TD>
      <TD WIDTH=33%><A HREF="Login.asp">Login</A></TD>
      <TD WIDTH=33%><A HREF="Register.asp">I'm a new user</A></TD>
    <% End If %>
  </TR>
</TABLE>
</BODY>
</HTML>

pgtips · January 19th, 2004, 07:36 AM

on this line
strSQL = "SELECT * FROM Item " & _
"WHERE ExpirationDate > #" & FormatDateTime(Now,2) & "# " & _
"AND ItemStatus = 'Active';"
change # to ' (# is specific to Access, SQL Server uses ' for dates)