Hey folks, you'll be sick of me by now....Well here we go again....I have 
a SQL select statement that selects records from 2 tables where the value 
(4) represents CLOSED (Its a helpdesk thing, all calls that are closed are 
represented by a 4....It then pulls off records that where closed for 
today....so i should end up with a list of records closed on todays date!! 
Get me? Right.......This works fine in Access but when connecting to SQL 
database, it pulls off all the records for TODAY, not only the records 
that have been closed....The field is a datetime stamp field, as that is 
how the table has been setup... So i have used instr to pull off the 
date...Can anyone tell me why this aint working? Why it works fine with 
Access but not with SQL....Heres my code:


	Dim oConn
	Dim oRS
	Dim Sqltxt	
	Dim FSO,oFile	'This is for File Object and TextFile Object
	
	Set oConn = Server.CreateObject("ADODB.Connection")
	Set oRS = Server.CreateObject("ADODB.Recordset")
	Set FSO = Server.CreateObject("Scripting.FileSystemObject")'create 
a file object
	
	If NOT FSO.FileExists(Server.MapPath("log.txt") ) Then 'open a 
text file, chking it exists first and creating it if it doesn't
	
		Set oFile = FSO.CreateTextFile(Server.MapPath
("./log.txt") )
	
	Else
		Set oFile = FSO.OpenTextFile(Server.MapPath
("./log.txt") ,8) 
		' 8 is appending. e.g add to end of file; change to 
rewrite mode using 1 or 0 to read only
	End If

	oFile.Write Date() & VbCrLf 'VBCrLf is a carriage return others 
like VbTab exist too
	
			SQLtxt = "SELECT Calls.Callid, Calls.ContactID, 
Calls.Forename, CallsHistory.Action, Calls.Surname, Calls.Email, 
CallsHistory.Notes, Calls.DateClosed "
			SQLtxt = SQLtxt & " FROM Calls INNER JOIN 
CallsHistory ON Calls.Callid = CallsHistory.FKey "
			SQLtxt = SQLtxt & " WHERE (((CallsHistory.Action)
=4));" 
	
			oRS.Open SQLtxt,"DSN=Sunrise_db"
	
		While Not oRS.EOF
				
			If Instr(oRS("DateClosed"),Date()) Then

				ofile.WriteLine "CallID :"  & oRS("CallID")
				ofile.WriteLine "Forename :"  & oRS
("Forename") 
				ofile.WriteLine "Surname :"  & oRS
("Surname")& VbCrLf

			Response.Write "'" & oRS("CallID") & oRS
("Forename") & oRS("Surname") & "<br>"
	End if
		
				oRS.Movenext
			
	Wend
				
				oFile.Close
				Set FSO = nothing

				Set oRS = nothing
				Set oConn = nothing






CHEERS GUYS!!!

Why don't you restrict the records returned to be the ones that were closed
today? - instead of returning all closed records (by checking for code 4)
and then doing the date comparisons in the asp script.

Not only will it get you past any of the problems with odd date forms, it'll
also optimize your code and make it a lot neater.

-Kim


> -----Original Message-----
> From: Dale Wright [mailto:dwright@c...]
> Sent: 14. oktober 2002 10:38
> To: ASP Databases
> Subject: [asp_databases] SQL SELECT PROBLEM
>
>
> Hey folks, you'll be sick of me by now....Well here we go again....I have
> a SQL select statement that selects records from 2 tables where the value
> (4) represents CLOSED (Its a helpdesk thing, all calls that are
> closed are
> represented by a 4....It then pulls off records that where closed for
> today....so i should end up with a list of records closed on
> todays date!!
> Get me? Right.......This works fine in Access but when connecting to SQL
> database, it pulls off all the records for TODAY, not only the records
> that have been closed....The field is a datetime stamp field, as that is
> how the table has been setup... So i have used instr to pull off the
> date...Can anyone tell me why this aint working? Why it works fine with
> Access but not with SQL....Heres my code:
>
>
> 	Dim oConn
> 	Dim oRS
> 	Dim Sqltxt
> 	Dim FSO,oFile	'This is for File Object and TextFile Object
>
> 	Set oConn = Server.CreateObject("ADODB.Connection")
> 	Set oRS = Server.CreateObject("ADODB.Recordset")
> 	Set FSO = Server.CreateObject("Scripting.FileSystemObject")'create
> a file object
>
> 	If NOT FSO.FileExists(Server.MapPath("log.txt") ) Then 'open a
> text file, chking it exists first and creating it if it doesn't
>
> 		Set oFile = FSO.CreateTextFile(Server.MapPath
> ("./log.txt") )
>
> 	Else
> 		Set oFile = FSO.OpenTextFile(Server.MapPath
> ("./log.txt") ,8)
> 		' 8 is appending. e.g add to end of file; change to
> rewrite mode using 1 or 0 to read only
> 	End If
>
> 	oFile.Write Date() & VbCrLf 'VBCrLf is a carriage return others
> like VbTab exist too
>
> 			SQLtxt = "SELECT Calls.Callid, Calls.ContactID,
> Calls.Forename, CallsHistory.Action, Calls.Surname, Calls.Email,
> CallsHistory.Notes, Calls.DateClosed "
> 			SQLtxt = SQLtxt & " FROM Calls INNER JOIN
> CallsHistory ON Calls.Callid = CallsHistory.FKey "
> 			SQLtxt = SQLtxt & " WHERE (((CallsHistory.Action)
> =4));"
>
> 			oRS.Open SQLtxt,"DSN=Sunrise_db"
>
> 		While Not oRS.EOF
>
> 			If Instr(oRS("DateClosed"),Date()) Then
>
> 				ofile.WriteLine "CallID :"  & oRS("CallID")
> 				ofile.WriteLine "Forename :"  & oRS
> ("Forename")
> 				ofile.WriteLine "Surname :"  & oRS
> ("Surname")& VbCrLf
>
> 			Response.Write "'" & oRS("CallID") & oRS
> ("Forename") & oRS("Surname") & "<br>"
> 	End if
>
> 				oRS.Movenext
>
> 	Wend
>
> 				oFile.Close
> 				Set FSO = nothing
>
> 				Set oRS = nothing
> 				Set oConn = nothing
>
>
>
>
>
>
> CHEERS GUYS!!!
>

SELECT
    a.CallID
    , a.ContactID
    , a.ForeName
    , b.Action
    , a.SurName
    , a.Email
    , b.Notes
    , a.DateClosed
FROM
    Call AS a
INNER JOIN
    CallsHistory AS b
ON
    a.CallID = b.FKey
WHERE
    b.Action = 4
AND
    CAST(CONVERT(VarChar, DateClosed, 112) AS SmallDateTime) 
CAST(CONVERT(VarChar, GetDate, 112) AS SmallDateTime)

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Dale Wright" <dwright@c...>
Subject: [asp_databases] SQL SELECT PROBLEM


: Hey folks, you'll be sick of me by now....Well here we go again....I have
: a SQL select statement that selects records from 2 tables where the value
: (4) represents CLOSED (Its a helpdesk thing, all calls that are closed are
: represented by a 4....It then pulls off records that where closed for
: today....so i should end up with a list of records closed on todays date!!
: Get me? Right.......This works fine in Access but when connecting to SQL
: database, it pulls off all the records for TODAY, not only the records
: that have been closed....The field is a datetime stamp field, as that is
: how the table has been setup... So i have used instr to pull off the
: date...Can anyone tell me why this aint working? Why it works fine with
: Access but not with SQL....Heres my code:
:
:
: Dim oConn
: Dim oRS
: Dim Sqltxt
: Dim FSO,oFile 'This is for File Object and TextFile Object
:
: Set oConn = Server.CreateObject("ADODB.Connection")
: Set oRS = Server.CreateObject("ADODB.Recordset")
: Set FSO = Server.CreateObject("Scripting.FileSystemObject")'create
: a file object
:
: If NOT FSO.FileExists(Server.MapPath("log.txt") ) Then 'open a
: text file, chking it exists first and creating it if it doesn't
:
: Set oFile = FSO.CreateTextFile(Server.MapPath
: ("./log.txt") )
:
: Else
: Set oFile = FSO.OpenTextFile(Server.MapPath
: ("./log.txt") ,8)
: ' 8 is appending. e.g add to end of file; change to
: rewrite mode using 1 or 0 to read only
: End If
:
: oFile.Write Date() & VbCrLf 'VBCrLf is a carriage return others
: like VbTab exist too
:
: SQLtxt = "SELECT Calls.Callid, Calls.ContactID,
: Calls.Forename, CallsHistory.Action, Calls.Surname, Calls.Email,
: CallsHistory.Notes, Calls.DateClosed "
: SQLtxt = SQLtxt & " FROM Calls INNER JOIN
: CallsHistory ON Calls.Callid = CallsHistory.FKey "
: SQLtxt = SQLtxt & " WHERE (((CallsHistory.Action)
: =4));"
:
: oRS.Open SQLtxt,"DSN=Sunrise_db"
:
: While Not oRS.EOF
:
: If Instr(oRS("DateClosed"),Date()) Then
:
: ofile.WriteLine "CallID :"  & oRS("CallID")
: ofile.WriteLine "Forename :"  & oRS
: ("Forename")
: ofile.WriteLine "Surname :"  & oRS
: ("Surname")& VbCrLf
:
: Response.Write "'" & oRS("CallID") & oRS
: ("Forename") & oRS("Surname") & "<br>"
: End if
:
: oRS.Movenext
:
: Wend
:
: oFile.Close
: Set FSO = nothing
:
: Set oRS = nothing
: Set oConn = nothing
:
:
:
:
:
:
: CHEERS GUYS!!!