Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

problem creating xml document from database output

Status
Not open for further replies.
aamaker

aamaker

Programmer
Jan 20, 2002
222
US
I am trying to do someting like this:

... which performs a query to a simple Access db and then 'writes' out XML that is saved on the server as newly created .xml file.

It doesnt seem to work and just hangs - and I have to restart IIS on my local production server to unlock the .ldb file that is created from the .mdb database file.

Anyone have any idea where I've gone wrong?

Code: 
<html>
<title>CodeAve.com(Create XML from Access)</title>
<body bgcolor="#FFFFFF">
<%
' Name of the access db being queried
accessdb="sitessearchdatabase" 

' Connection string to the access db
cn="DRIVER={Microsoft Access Driver (*.mdb)};"
cn=cn & "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\MyLocalProductionServer\database\sitessearchdatabase.mdb;"


' Create a server recordset object
Set rs = Server.CreateObject("ADODB.Recordset")

' Query the states table from the state_info db
sql = "select * from Tbl_BuildingData" 

' Execute the sql
rs.Open sql, cn

' Move to the first record
rs.MoveFirst

' Name for the ouput document 
file_being_created= "buildings.xml"

' create a file system object
set fso = createobject("scripting.filesystemobject")

' create the text file - true will overwrite any previous files
' Writes the db output to a .xml file in the same directory 
Set act = fso.CreateTextFile(server.mappath(file_being_created), true)

' All non repetitive xml on top goes here
act.WriteLine("<?xml version=""1.0""?>")
act.WriteLine("<Buildings>")

'Loop to output all the query results to the xml document
do while not rs.eof

' counter to give each record a sequential listing
counter=counter+1

act.WriteLine("<Building id="""& counter &""">")
act.WriteLine("<DBid>" & rs("ID") & "</DBid>" )
act.WriteLine("<Building_name>" & rs("name") & "</Building_name>" )
act.WriteLine("<SqFeet>" & rs("SqFeet") & "</SqFeet>")
act.WriteLine("<County>"& rs("county") & "</County>")
act.WriteLine("<YrBuilt>"& rs("YrBuilt") & "</YrBuilt>")
act.WriteLine("</Building>")

' move to the next record
rs.movenext
loop

' All non repetitive xml on bottom goes here
act.WriteLine("</Buildings>")


' close the object (xml)
act.close


' Writes a link to the newly created xml document in the browser
response.write "<a href='buildings.xml'>Buildings</a> (.xml) has been created <br>"
response.write "on " & now() & "<br>"
%>
</body>
</html>
 
Sort by date Sort by votes
hmm it looks like a mappath for a file that doesnt exist yet... try a response.write to make sure you are passing a valid path to the fso's CreateTextFile method.

I wouldnt think that would cause the server to hang, that is usually something like a loop through a recordset without a .MoveNext or other such infinate loop but you can always figure out where it is hanging by adding something like this:[tt]
Response.Write "<br>I got here at: " & cStr(Now)
Response.End[/tt]

run that and, if it works, move it down a few lines, save, and run it again... keep doing that until you figure out the last line that successfully runs...

you dont have to put the timestamp your response.write like that but i like to do it so i know it is actually running each time rather than showing me a cached output.
 
Upvote 0 Downvote
[1] Your connection string looks strange. Either you have an active connection with connection string like this.
[tt]
cn="DRIVER={Microsoft Access Driver (*.mdb)};"
cn=cn & DBQ=\\MyLocalProductionServer\database\sitessearchdatabase.mdb;"
[/tt]
as direct mimic of your quoted reference is using; or you use your jet.oledb.
[tt]
cn="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\MyLocalProductionServer\database\sitessearchdatabase.mdb;"
[/tt]
[2] You pass the connection string to recordset open method. You have to make sure you have a connection with that connection string available for use as direct mimic of your reference article. Else, you have to establish the connection object first.
[tt]
set oconn=server.createobject("adodb.connection")
oconn.open cn
'etc etc
rs.open sql,[red]oconn[/red]
[/tt]
[3] Make sure you close out the rs and release its resource on the server after all things done. (I think it is material for server application, but the ref article does not show neither.)
[tt]
rs.close
set rs=nothing
[/tt]
In case you establish the specific connection, maybe you close and release it as well.
 
Upvote 0 Downvote
Also, your db source requires your application to run with network credential which it normally lacks. Make it a local mdb before doing that kind of access.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
534
penguinspeaks
penguinspeaks
penguinspeaks
  • Locked
  • Question
Start loop from a specified point
Replies
0
Views
470
penguinspeaks
penguinspeaks
bslintx2021
  • Locked
  • Question
How to configure IIS 10 server to allow Classic ASP to connect to MSSQL server database on a domain
Replies
1
Views
1K
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
Problem: sql-Select with request.form
2
Replies
22
Views
963
leifoet
leifoet
leifoet
  • Locked
  • Question
How to update the content of radio buttons in a database?
Replies
2
Views
377
leifoet
leifoet

Part and Inventory Search

Sponsor

Back
Top