Missing Date

[toddsalcedo]

The code below pulls data from a SQL Server database and then generates an XML file. For some eason the dates from the database d not come through but everythingse does. Is it apprarent at all from this code as to why it isn't working?

<%
Option Explicit
Dim DataConn, cmdDC, rsDC
Dim sFileName, sConn

sFileName = "projects_new.xml"
sConn = "DRIVER={SQL Server};Server=SQL-NWSS-007\AUB1;Database=bdcodata;uid=xxxxxxxxxxx;pwd=xxxxxxxxx;"
'sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("test1.mdb") & ";"

Set DataConn = Server.CreateObject("ADODB.Connection")
Set cmdDC = Server.CreateObject("ADODB.Command")
Set rsDC = Server.CreateObject("ADODB.Recordset")

DataConn.ConnectionTimeout = 15
DataConn.CommandTimeout = 30
DataConn.Open sConn
cmdDC.ActiveConnection = DataConn
cmdDC.CommandText = "SELECT * FROM vw_projects where DateFileCreated is Null"
cmdDC.CommandType = 1
rsDC.Open cmdDC, , 0, 1

makeXML "Test File", "XYZ-123", "John Doe", "This is a test", rsDC, sFileName
Response.Write "File written: <a href='" & sFileName & "'> click here</a></br>"

rsDC.Close
DataConn.Close
Set rsDC = Nothing
Set cmdDC = Nothing
Set DataConn = Nothing

'=================================================================================
' Routines
'=================================================================================

Sub makeXML(FileTitle, FileIdentifier, FileCreator, FileComment, rs, sFileName) ' rs As ADODB.Recordset)
Dim objParser ' As New MSXML2.DOMDocument40
Dim sTmp ' As String
Dim z ' As Object

Const FILE_FORMAT_VERSION = "v2.0" ' Hard-coded

set objParser = Server.CreateObject("Microsoft.XMLDOM")

objParser.appendChild objParser.createProcessingInstruction("xml", " version = ""1.0""")

'----------------------------------------------------
' Create the root node
'----------------------------------------------------
Set objParser.documentElement = objParser.createElement("cde")

'----------------------------------------------------
' Create the FileInfo element and its child elements
'----------------------------------------------------
Dim oFileInfo ' As IXMLDOMElement
Set oFileInfo = objParser.createElement("FileInfo")
oFileInfo.appendChild objParser.createElement("FileTitle")

' File 0 1 2 3 4 5
sTmp = "Title|Identifier|Creator|CreationDate|FormatVersion|Comment"
For Each z In Split(sTmp, "|")
oFileInfo.appendChild objParser.createElement("File" & z)
Next

' Set the values (mostly from the routine's arguments)
With oFileInfo
.childNodes(0).Text = FileTitle ' Argument
.childNodes(1).Text = FileIdentifier ' Argument
.childNodes(2).Text = FileCreator ' Argument
.childNodes(3).Text = XMLDateFormat(Date) ' Today's date
.childNodes(4).Text = FILE_FORMAT_VERSION ' Constant
.childNodes(5).Text = FileComment ' Argument
End With

' Append FileInfo to the document
objParser.documentElement.appendChild oFileInfo.cloneNode(True)

'----------------------------------------------------
' Create the ProjecList element
'----------------------------------------------------
Dim oProjectList ' As IXMLDOMElement
Set oProjectList = objParser.createElement("ProjectList")

'----------------------------------------------------
' Create a Project element (do just once, then clone)
'----------------------------------------------------
Dim oProject ' As IXMLDOMElement
Set oProject = objParser.createElement("Project")

' Loop through list and create the elements within Project
sTmp = "ID|CompanyProjectNumber|FAAProjectNumber|ProjectType|ProductType|" & _
"ACO|DateApplication|ExpectedCompletionDate|Title|Description|Model|" & _
"ApplicantName|ApplicantName2|StreetAddress|StreetAddress2|" & _
"City|StateProv|PostalCode|Country|POCLastName|POCFirstName|POCPhone|" & _
"POCEmail|POCTitle|POCOrganization"
For Each z In Split(sTmp, "|")
oProject.appendChild objParser.createElement(z)
Next

'----------------------------------------------------
' Loop through projects, insert new values and
' append clone to project list
' To comply with definition, used output names and order shown
' This needs some work to accomodate Model,
' which can have multiple occurrences
'----------------------------------------------------
rs.MoveFirst
While Not rs.EOF
LoadNodeText oProject, "ID", rs("ID").Value
LoadNodeText oProject, "CompanyProjectNumber", rs("ProjectNumber").Value
LoadNodeText oProject, "FAAProjectNumber", rs("ProjectNumber").Value
LoadNodeText oProject, "ProjectType", rs("TypeofChange").Value
LoadNodeText oProject, "ProductType","Transport Airplane"

if rs("location") = "PS" then
LoadNodeText oProject, "ACO", "Seattle"
elseif rs("location") = "LB" then
LoadNodeText oProject, "ACO", "Los Angeles"
end if

LoadNodeText oProject, "DateApplication", XMLDateFormat(rs("ProjectInitiationDate").Value)
LoadNodeText oProject, "ExpectedCompletionDate", XMLDateFormat(rs("ProjectClosureDate").Value)
LoadNodeText oProject, "Title", rs("ChangeTitle").Value
LoadNodeText oProject, "Description", rs("ChangeDescription").Value
LoadNodeText oProject, "Model", rs("ModelSeries").Value



LoadNodeText oProject, "Country", "United States"
LoadNodeText oProject, "POCLastName", rs("LastName").Value
LoadNodeText oProject, "POCFirstName", rs("FirstName").Value
LoadNodeText oProject, "POCPhone", rs("Phone").Value
LoadNodeText oProject, "POCEmail", rs("Email").Value
LoadNodeText oProject, "POCTitle", rs("Title").Value
LoadNodeText oProject, "POCOrganization", rs("Organization").Value

oProjectList.appendChild oProject.cloneNode(True)
rs.MoveNext
Wend

' Add project list to document
objParser.documentElement.appendChild oProjectList.cloneNode(True)

' Write to text file
objParser.Save Server.MapPath(sFileName)
End Sub

Sub LoadNodeText(oNode, sNodeName, byVal sFieldValue)
' Takes a node, finds a child node (by name), and inserts a value
if IsNull(sFieldValue) then
oNode.selectSingleNode(sNodeName).Text = ""
else
oNode.selectSingleNode(sNodeName).Text = sFieldValue
end if
End Sub

Function XMLDateFormat(dtDate)
' Returns a string of the format yyyy-mm-dd
XMLDateFormat = Year(dtDate) & "-" & _
Right("0" & Month(dtDate), 2) & "-" & _
Right("0" & Day(dtDate), 2)
End Function
%>

 

[toddsalcedo]

FYI - the specific lines I am referring too are:
LoadNodeText oProject, "DateApplication", XMLDateFormat(rs("ProjectInitiationDate").Value)
LoadNodeText oProject, "ExpectedCompletionDate", XMLDateFormat(rs("ProjectClosureDate").Value)

 

[tsuji]

>cmdDC.ActiveConnection = DataConn
[tt][red]set[/red] cmdDC.ActiveConnection = DataConn[/tt]