In an asp/vbscript/sql/dreamweaver mx website I inherited, having this problem:

It appears that the query that the creator of the page wrote is looking for a start date (2000, for example) and an end date (2099) to be pulled from the SQL database "date" field for various events. There is no field existing called "startyear" or "start" or "endyear" or "end" -- just the "date" field currently exists in the database. when you try to run this coded page, you get various errors, such as:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value '1952-1955' to a column of data type int.
/pages/document.asp, line 14

QUESTIONS: Do I need to update the SQL database to include 2 new fields, start and end (year)? Or does the query statement contain some kind of an error? Is there a way to write the query on the .asp page to indicate that, depending on the user's selection, we want to display records from say, 2000-2099? (or 1900-1999, etc.?) OR do I need to go through and edit all 400+/- db entries so they only list ONE date in the date field (like 1995) and then just put the time span maybe in the title? (Although people here told me this page USED to work with the time span as is.)

When I examine the SQL query in the Recordset, DW MX (helpfully!) displays an error message saying "invalid column name '& startyear' " AND ALSO "invalid column name '& endyear' " . Here is SQL query statement as it currently exists:

SELECT sid,date,voices
FROM dbo.timeline_entries
WHERE date between " & startyear & " and " & endyear & "
ORDER BY date desc


Here is some of the code from the .asp page:

<%@LANGUAGE="VBSCRIPT"%>

<%
if ((Request.QueryString("start") <> "") and (Request.QueryString("end") <> "")) then
    startyear = Request.QueryString("start")
    endyear = Request.QueryString("end")

    set rs = Server.CreateObject("ADODB.Recordset")
    rs.ActiveConnection = MM_convergeCTE_STRING
    rs.Source = "SELECT sid,date,voices FROM dbo.timeline_entries where date between " & startyear & " and " & endyear & " order by date desc"
    rs.CursorType = 0
    rs.CursorLocation = 2
    rs.LockType = 3
    rs.Open()
    rs_numRows = 0

**************

Note that the line 14 referenced in the error messages returned by Firefox and Netscape refers to the
     rs.Open()
line. Very curious.

MS IE just returns the HTTP 500 Internal server error.

If you have any suggestions on this, I would greatly appreciate it. Thanks so much.

--BuddyZ

Date values have to be surrounded in ' ' so your statment should be:

"SELECT sid,date,voices FROM dbo.timeline_entries where date between '" & startyear & "' and '" & endyear & "' order by date"

"The one language all programmers understand is profanity."

Doug,

THANK YOU. And my boss thanks you. That worked like a charm, great when it's a simple fix. I will be taking some courses over the next 6 months in programming, asp, vbscript etc. so that should help. Maybe I will be able to give something back here sometime in the future.

HAVE A GREAT DAY!

-buddyz

Your welcome, glad it worked out for you.

"The one language all programmers understand is profanity."