I've created an ActiveX Script within a DTS Package in SQL Server 2000 that
exports data from a database table into an XML file. It works great, but now
I'd like to filter the data to only include data where the "item_date" field
is before today's date. (i.e. item_date = 2005-11-30; today_date =
2005-12-07) But when I try the following code:
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************
Function Main()
Dim objADORS
Dim objXMLDoc

Dim nodeRoot
Dim nodeTemp
Dim nodeRelease

'Create ADO and MSXML DOMDocument Objects
Set objADORS = CreateObject("ADODB.Recordset")
Set objXMLDoc = CreateObject("MSXML2.DOMDocument.4.0")

'Run the stored procedure and load the Recordset
objADORS.Open "SELECT * FROM tblTABLENAME", _
"PROVIDER=SQLOLEDB.1;SERVER=SERVERNAME;UID=USERNAM E;PWD=PASSWORD;DATABASE=DBNAME;"

'Prepare the XML Document
objXMLDoc.loadXML "<root />"
Set nodeRoot = objXMLDoc.documentElement

'For each record in the Recordset
Set nodeDate = objXMLDoc.createElement("item_date")
While Not objADORS.EOF and Not date() = nodeDate
Set nodeRelease = objXMLDoc.createElement("release")
nodeRoot.appendChild nodeRelease

Set nodeTemp = objXMLDoc.createElement("release_id")
nodeTemp.nodeTypedValue = Trim(objADORS.Fields("release_id").Value)
nodeRelease.appendChild nodeTemp

Set nodeTemp = objXMLDoc.createElement("start_date")
nodeTemp.nodeTypedValue = Trim(objADORS.Fields("start_date").Value)
nodeRelease.appendChild nodeTemp

Set nodeTemp = objXMLDoc.createElement("end_date")
nodeTemp.nodeTypedValue = Trim(objADORS.Fields("end_date").Value)
nodeRelease.appendChild nodeTemp

Set nodeTemp = objXMLDoc.createElement("item_date")
nodeTemp.nodeTypedValue = Trim(objADORS.Fields("item_date").Value)
nodeRelease.appendChild nodeTemp

Set nodeTemp = objXMLDoc.createElement("title")
nodeTemp.nodeTypedValue = Trim(objADORS.Fields("title").Value)
nodeRelease.appendChild nodeTemp

Set nodeTemp = objXMLDoc.createElement("information")
nodeTemp.nodeTypedValue = Trim(objADORS.Fields("information").Value)
nodeRelease.appendChild nodeTemp

objADORS.moveNext
Wend

objADORS.Close
Set objADORS = Nothing

'Save the created XML document
objXMLDoc.Save "B:\XMLFILE.xml"

Main = DTSTaskExecResult_Success
End Function

....I receive an error stating:
Error Code: 0
Error Source: Microsoft VBScript runtime error
Error Description: Object doesn't support this property or method
Error on Line 25

If someone could help me to figure out what I'm doing wrong with this
script, it would be greatly appreciated. Thanks.

Post Note:
I've worked with this script more, and figured out why I wasn't pulling the
value from the DB table. (Not the smartest moment of my life) I copied the
wrong line to get the value. I had used:
Set nodeDate = objXMLDoc.createElement("item_date")

....and I should have used:
Set nodeDate = Trim(objADORS.Fields("item_date").Value)

Anyway, now it's trying to compare the two dates, like this:
....'For each record in the Recordset
While Not objADORS.EOF
Set nodeDate = Trim(objADORS.Fields("item_date").Value)
If date() >= nodeDate Then

Set nodeRelease = objXMLDoc.createElement("release")...

....but I'm getting this error message now:
Error Code: 0
Error Source=Microsoft VBScript runtime error
Error Description: Object required: '[string: "6/26/2002"]'
Error on Line 25

The good news is that the date it references is the date of the first record
in the DB table. Of course the bad news is that I'm still getting an error.
If anyone can let me know what I'm doing wrong, that would be great. Thanks
again.

KWilliams

"KWilliams" wrote:

format the date BEFORE comparing to date()

"KWilliams" wrote:

Hi mattb,

Thanks for replying to my problem. I've been able to have some success with
my filtering, but I'm still running into a problem. Here's the info:

I've created an ActiveX Script that's located within a DTS package to export
database table data into an XML file. It works great, but now I need to
filter the data to be exported a bit.

The first setup was to get all "archived" records by using the "end_date"
and current date, like this:

But when I try a similar setup to get all records in which the current date
is between the "start date" and "end_date" of the record like this, no
records come up:

I was easily able to do this in SQL Server using this query:
SELECT *
FROM tblNewsReleases_test
WHERE (start_date < GETDATE()) AND (end_date > GETDATE())

....so I know that I have the right idea. I'm just not sure what I'm doing
wrong, and I'm hoping that you can help me out. If anyone can see where I'm
making a mistake with the second setup, it would be very helpful. Thanks.

"mattb" wrote:

Hi mattb,

I just received a quick reply from the SQLTeam forum, and it worked great
for me. Here's what he said:


So I changed my script to read like this:

....and it works great. Thanks.