Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 28th, 2004, 03:32 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
Default getting records by date

ok ... i'm going ballistic
i run an Access Database and need to retrieve records withing a specific timeframe i ask for "startDate" and "endDate" and receive it with the following code:

startDate = cDate(request.queryString("startDate"))
endDate = cDate(request.queryString("endDate"))
ID = request.queryString("ID")

Now this is my Select statement and the dates seem not to work ...:

sqlStrDef = "SELECT dh.RefNr, cr.RefNr, Finish FROM DataHead dh, CritRes cr "
sqlStrDef = sqlStrDef & "WHERE dh.RefNr = cr.RefNr "
sqlStrDef = sqlStrDef & "AND DateCompleted >= #" & startDate & "# And DateCompleted <= #" & endDate & "#"
sqlStrDef = sqlStrDef & "AND dh.Division = '"&strSearch&"' OR dh.Team = '"&strSearch& "'OR dh.CaseOfficer ='"&strSearch& "' "
sqlStrDef = sqlStrDef & "ORDER BY cr.RefNr"
Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = strConn
objCommand.CommandText = sqlStrDef
'executing and resetting to Nothing
objCommand.CommandType = adCmdText
Set objRS = objCommand.Execute
Set objCommand = Nothing

I have tryed as well retrieving with the following satement:

sqlStrDef = "SELECT dh.RefNr, cr.RefNr, Finish FROM DataHead dh, CritRes cr "
sqlStrDef = sqlStrDef & "WHERE dh.RefNr = cr.RefNr "
sqlStrDef = sqlStrDef & "AND DateCompleted BETWEEN #" & startDate & "# And <= #" & endDate & "#"
sqlStrDef = sqlStrDef & "AND dh.Division = '"&strSearch&"' OR dh.Team = '"&strSearch& "'OR dh.CaseOfficer ='"&strSearch& "' "
sqlStrDef = sqlStrDef & "ORDER BY cr.RefNr"
Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = strConn
objCommand.CommandText = sqlStrDef
'executing and resetting to Nothing
objCommand.CommandType = adCmdText
Set objRS = objCommand.Execute
Set objCommand = Nothing

The results are either nothing or all are selected ... but not the records withing the date range.

where do i go wrong???

Any help would be appreciated

... there is always more to learn...
__________________
... there is always more to learn...
Reply With Quote
  #2 (permalink)  
Old April 28th, 2004, 04:32 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Could you post the SQL string to the screen and paste that into this message? If I can see an example of the SQL with values you are using that will help.

Thanks,

Brian
Reply With Quote
  #3 (permalink)  
Old April 29th, 2004, 05:20 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
Default

here the entire page so far the funny thing is that if i check only for the start date or only the end date it works...

<%option explicit%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<head>
    <title>ReportPercentResult.asp</title>
</head>
<body>
<div align="center">
<%

dim ID, sqlStrDef, objCommand, objRS, objCommand2, objRS2, startDate, endDate, chkList, strSearch
dim strRef, totalRefCtr, trueCtr, falseCtr

if NOT Request.QueryString("Team")= "" then
    strSearch = Request.QueryString("Team")
end if
if NOT Request.QueryString("Division")= "" then
    strSearch = Request.QueryString("Division")
end if
if NOT Request.QueryString("CaseOfficer")= "" then
    strSearch = Request.QueryString("CaseOfficer")
end if


startDate = cDate(request.queryString("startDate"))
endDate = cDate(request.queryString("endDate"))
ID = request.queryString("ID")

response.write startDate &"<br>"
response.write endDate &"<br>"

sqlStrDef = "SELECT dh.RefNr, cr.RefNr, Finish FROM DataHead dh, CritRes cr "
sqlStrDef = sqlStrDef & "WHERE dh.RefNr = cr.RefNr "
sqlStrDef = sqlStrDef & "AND DateCompleted >= #" & startDate & "# And DateCompleted <= #" & endDate & "#"
sqlStrDef = sqlStrDef & "AND dh.Division = '"&strSearch&"' OR dh.Team = '"&strSearch& "'OR dh.CaseOfficer ='"&strSearch& "' "
sqlStrDef = sqlStrDef & "ORDER BY cr.RefNr"
Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = strConn
objCommand.CommandText = sqlStrDef
'executing and resetting to Nothing
objCommand.CommandType = adCmdText
Set objRS = objCommand.Execute
Set objCommand = Nothing

if objRS.eof then
    response.write ""
else
    response.write "<table border='1' bgcolor ='#e0e0e0' cellpadding='5' cellspacing='5'><tr><th>Issue</th><th>Total # of</th>"
    objRS.movefirst
    strRef = objRS("cr.RefNr")
    totalRefCtr = 1
    trueCtr = 0
    falseCtr = 0
    while not objRS.eof
        if objRS("cr.RefNr") = strRef then
            if objRS("Finish") = true then
                trueCtr = trueCtr +1
            end if
            if objRS("Finish") = false then
                falseCtr = falseCtr +1
            end if

            objRS.movenext
        else
            totalRefCtr = totalRefCtr + 1
            objRS.movenext
            strRef = objRS("cr.RefNr")
        end if
    wend
    response.write "<tr><td>Reference Numbers</td><td>" & totalRefCtr & "</td></tr>"
    response.write "<tr><td>Completed Number of C/B/A Numbers</td><td>" & trueCtr & "</td></tr>"
    response.write "<tr><td>Currently Not completed C/B/A Numbers</td><td>" & falseCtr & "</td></tr></table><br><br>"
End If

objRS.close

%>
</dev>
</body>
</html>


any help is appreciated... thanks Brian

... there is always more to learn...
Reply With Quote
  #4 (permalink)  
Old April 30th, 2004, 08:09 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Hey,

Instead of this:

sqlStrDef = sqlStrDef & "AND DateCompleted BETWEEN #" & startDate & "# And <= #" & endDate & "#"

Try:

sqlStrDef = sqlStrDef & "AND DateCompleted BETWEEN #" & startDate & "# And #" & endDate & "#"

What's different is after the and I removed the "<=", as I think this may be causing problems. Also, could you do a:

Response.Write(sqlStrDef)

after the query and post that. That may help too.

Brian
Reply With Quote
  #5 (permalink)  
Old April 30th, 2004, 05:27 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
Default

once again the code:

<%option explicit%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<head>
    <title>ReportPercentResult.asp</title>
</head>
<body>
<div align="center">
<%

dim ID, sqlStrDef, objCommand, objRS, objCommand2, objRS2, startDate, endDate, chkList, strSearch
dim strRef, totalRefCtr, trueCtr, falseCtr

if NOT Request.QueryString("Team")= "" then
    strSearch = Request.QueryString("Team")
end if
if NOT Request.QueryString("Division")= "" then
    strSearch = Request.QueryString("Division")
end if
if NOT Request.QueryString("CaseOfficer")= "" then
    strSearch = Request.QueryString("CaseOfficer")
end if


startDate = cDate(request.queryString("startDate"))
endDate = cDate(request.queryString("endDate"))
ID = request.queryString("ID")

response.write startDate &"<br>"
response.write endDate &"<br>"

sqlStrDef = "SELECT dh.RefNr, cr.RefNr, Finish FROM DataHead dh, CritRes cr "
sqlStrDef = sqlStrDef & "WHERE dh.RefNr = cr.RefNr "
sqlStrDef = sqlStrDef & "AND DateCompleted BETWEEN #" & startDate & "# And #" & endDate & "#"
sqlStrDef = sqlStrDef & "AND dh.Division = '"&strSearch&"' OR dh.Team = '"&strSearch& "'OR dh.CaseOfficer ='"&strSearch& "' "
sqlStrDef = sqlStrDef & "ORDER BY cr.RefNr"
Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = strConn
objCommand.CommandText = sqlStrDef
'executing and resetting to Nothing
objCommand.CommandType = adCmdText
Set objRS = objCommand.Execute
Set objCommand = Nothing


Response.Write(sqlStrDef) & "<br><br><br>"

if objRS.eof then
    response.write ""
else
    response.write "<table border='1' bgcolor ='#e0e0e0' cellpadding='5' cellspacing='5'><tr><th>Issue</th><th>Total # of</th>"
    objRS.movefirst
    strRef = objRS("cr.RefNr")
    totalRefCtr = 1
    trueCtr = 0
    falseCtr = 0
    while not objRS.eof
        if objRS("cr.RefNr") = strRef then
            if objRS("Finish") = true then
                trueCtr = trueCtr +1
            end if
            if objRS("Finish") = false then
                falseCtr = falseCtr +1
            end if

            objRS.movenext
        else
            totalRefCtr = totalRefCtr + 1
            objRS.movenext
            strRef = objRS("cr.RefNr")
        end if
    wend
    response.write "<tr><td>Reference Numbers</td><td>" & totalRefCtr & "</td></tr>"
    response.write "<tr><td>Completed Number of C/B/A Numbers</td><td>" & trueCtr & "</td></tr>"
    response.write "<tr><td>Currently Not completed C/B/A Numbers</td><td>" & falseCtr & "</td></tr></table><br><br>"
End If

objRS.close

%>
</dev>
</body>
</html>

here is the outcome: (including the SQL string)

1/1/2001
1/1/2001
SELECT dh.RefNr, cr.RefNr, Finish FROM DataHead dh, CritRes cr WHERE dh.RefNr = cr.RefNr AND DateCompleted BETWEEN #1/1/2001# And #1/1/2001#AND dh.Division = 'Toga' OR dh.Team = 'Toga'OR dh.CaseOfficer ='Toga' ORDER BY cr.RefNr

it does not make sense ...
in the database the date is set as Date (i had it for some time as text since i did not want to search on it at first and i thought it's not required but that changed).

Issue Total # of
Reference Numbers 1
Completed Number of C/B/A Numbers 9
Currently Not completed C/B/A Numbers 0

it should not find anything since the Database has only 1 entry sometime (in 2003) for test purpose only

... there is always more to learn...
Reply With Quote
  #6 (permalink)  
Old April 30th, 2004, 08:15 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Toran, Brian was asking you to post the output of this line

Response.Write(sqlStrDef) & "<br><br><br>"

Not the entire code again:)

Also it would be better if you could run the output of the "sqlStrDef" in your Access DB directly and post its status along with any error you get while executing the same in your db directly. That would help you resolve the problem quicker.

HTH
Cheers!

-Vijay G
Reply With Quote
  #7 (permalink)  
Old May 1st, 2004, 12:52 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
Default

got it solved.
the sql string now :

sqlStrDef = "SELECT DataHead.RefNr, CritRes.RefNr,DataHead.Finish, CritRes.AssetNr, CritRes.CritName, CritRes.Status, DataHead.DateCompleted, DataHead.CaseOfficer, DataHead.Team "
sqlStrDef = sqlStrDef & "FROM DataHead INNER JOIN CritRes ON DataHead.RefNr = CritRes.RefNr "
sqlStrDef = sqlStrDef & "WHERE CritRes.Status ='NM' AND DataHead.DateCompleted Between #" &startDate&"# And #"&endDate&"# AND DataHead.CaseOfficer= '"&strSearch&"' OR DataHead.Team = '"&strSearch&"' "
sqlStrDef = sqlStrDef & "AND DataHead.Finish = 'true'"

it's done in the Microsoft Access application and revised for .asp

Vijay and Brian ... Thanks so much for your help and the great tip to run it in Access (never thought about it) DUHHHHH... as i say ... there is ALWAYS more to learn ...
Thanks again

... there is always more to learn...
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
problem in updating records & finding records naveed77 VB Databases Basics 1 January 16th, 2007 12:12 PM
problem displaying records in order of time ,date method PHP Databases 3 July 25th, 2006 08:16 PM
Select records not equal to current date SoC Classic ASP Basics 2 July 12th, 2005 10:20 PM
Date query pulling records from yesterday Justine Access 5 June 27th, 2004 03:45 PM



All times are GMT -4. The time now is 07:46 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.