ASP-XML Mail Merge
Sorry if this has been answered before, but here goes. I got code for a Mail Merge from Microsoft, and it only works for me locally. When run live, I get the following error:
The connection cannot be used to perform this operation. It is either closed or invalid in this context.
What it's trying to do is use client-side VBScript to generate the merge by pulling an xml recordset from my live database. Here's the code that gets the recordset:
''''''
<%
' Execute the SQL statement.
set oRS = objConn.Execute(replace(Request.QueryString("SQL") ,"%20"," "))
set rs = server.createobject("adodb.recordset")
rs.fields.append "LName",200,255,&H00000020
rs.fields.append "FName",200,255,&H00000020
rs.fields.append "SchoolName",200,255,&H00000020
rs.open
TeacherID = ""
'while not oRs.eof
if TeacherID <> oRs("TeacherID") then
rs.AddNew
rs("LName") = oRs("LName")
rs("FName") = oRs("FName")
rs("SchoolName") = oRs("SchoolName")
rs.update
TeacherID = oRs("TeacherID")
end if
'oRs.movenext
'WEND
' Save the recordset in the Response object.
oRS.Save Response,1
%>
''''''
And the code that is generating the merge
''''''
<%@ Language=VBScript %>
<HTML>
<BODY>
<SCRIPT LANGUAGE=VBScript>
Sub CreateDataDoc(oApp)
' Declare variables.
Dim sServer,oDoc,oRS,sTemp,sHead,oRange,oField
' Place your server's name here.
sServer = "<servername>"
' Create a new document.
Set oDoc = oApp.Documents.Add
' Create a new recordset.
Set oRS = CreateObject("ADODB.Recordset")
' Open the XML recordset from the server and pass the SQL statement
' to the Getdata.asp page.
sSQL = "SELECT Teachers.TeacherID, Teachers.LName, Teachers.FName, Schools.SchoolName FROM (StudentsRelTeachers INNER JOIN Teachers ON StudentsRelTeachers.TeacherID = Teachers.TeacherID) INNER JOIN Schools ON Teachers.SchoolID = Schools.SchoolID ORDER BY Teachers.TeacherID;"
oRS.Open "http://platths.com/JNedelka/top10/forms/Getdata.asp?SQL=" & sSql
' Convert the recordset to a string.
sTemp = oRS.GetString(2, -1, vbTab) ' 2 = adClipString
' Append the field names to the front of the string.
For Each oField In oRS.Fields
sHead = sHead & oField.Name & vbTab
Next
' Strip off the last tab.
sTemp = Mid(sHead, 1, Len(sHead) - 1) & vbCrLf & sTemp
' Get a range object and insert the text into the document.
Set oRange = oDoc.Range
oRange.Text = sTemp
' Convert the text to a table.
oRange.ConvertToTable vbTab
' Save the document to a temp file.
oDoc.SaveAs "C:\data.doc"
' Close the document (no save).
oDoc.Close False
End Sub
'Sub ButtonClick()
Dim oApp
Dim oDoc
Dim oMergedDoc
' Create an instance of Word.
Set oApp = CreateObject("Word.Application")
' Create our data file.
CreateDataDoc oApp
' Add a new document.
Set oDoc = oApp.Documents.Add
With oDoc.MailMerge
' Add our fields.
.Fields.Add oApp.Selection.Range, "Fname"
oApp.Selection.TypeText " "
.Fields.Add oApp.Selection.Range, "Lname"
oApp.Selection.TypeParagraph
.Fields.Add oApp.Selection.Range, "SchoolName"
oApp.Selection.TypeParagraph
' Create an autotext entry.
Dim oAutoText
Set oAutoText = oApp.NormalTemplate.AutoTextEntries.Add _
("MyLabelLayout", oDoc.Content)
oDoc.Content.Delete
.MainDocumentType = 1 ' 1 = wdMailingLabels
' Open the saved data source.
.OpenDataSource "C:\data.doc"
' Create a new document.
oApp.MailingLabel.CreateNewDocument "8160", "", _
"MyLabelLayout", , 4 ' 4 = wdPrinterManualFeed
.Destination = 0 ' 0 = wdSendToNewDocument
' Execute the mail merge.
.Execute
oAutoText.Delete
End With
' Close the mail merge edit document.
oDoc.Close False
' Get the current document.
Set oMergedDoc = oApp.ActiveDocument
' Show Word to the user.
oApp.Visible = True
' Uncomment these lines to save the merged document locally.
'oMergedDoc.SaveAs "C:\test.doc"
'oMergedDoc.Close False
'oApp.Quit False
'End Sub
window.location.href="management.asp"
</SCRIPT>
</BODY>
</HTML>
'''''''
The error is on the line oRs.Open "http..."
Again, this works fine on my testing server but doesn't want to work live. Is there some kind of setting I'm missing? Local testing server is WinXP Pro, live is Server 2000. Thanks
|
