ADO Updates with ASP

Created 05 November 2002 00:00
  Dim Conn, rs, sql

  ' Open connection
  Set Conn = Server.CreateObject("ADODB.Connection")
  Conn.Open "DSN=MyDSN; UID=Internet"
  Set rs = Server.CreateObject("ADODB.RecordSet")
  ' Open recordset using sql string
  sql = "SELECT * FROM MyTable"
  rs.Open sql, Conn, 1, 3
  ' loop through recordset updating data
  While NOT rs.EOF
    If rs("MyField") = 20 Then
      rs("Name") = rs("Name") & "+"
    End If
    Response.Write(rs("Name") & ".<br>")
  ' Close the recordset and connection
  Set rs = Nothing
  Set Conn = Nothing

XML Transformation with ASP

Created 05 November 2002 00:00
  Dim objStyleFile
  Dim objFS
  Dim objXML
  Dim objXSL
  Dim strXSL
  Set objXML = Server.CreateObject("Microsoft.XMLDOM")
  ' load XML file into XML parser
  objXML.load Server.MapPath("/RBK/XML/Product.xml")
  ' open the xsl/stylesheet file and store as a string
  Set objFS = CreateObject("Scripting.FileSystemObject")
  Set objStyleFile = objFS.OpenTextFile(Server.MapPath("/RBK/XML/Product.xsl"))
  strXSL = objStyleFile.ReadAll 
  Set objStyleFile = Nothing
  Set objFS = Nothing
  'Load the stylesheet to an xml document object
  Set objXSL = Server.CreateObject("Microsoft.XMLDOM")
  ' replace any fields you want to in the XSL file
  strXSL = Replace(strXSL, "", intProductID)
  ' objXSL.async = false
  If objXML.parseError = 0 AND objXSL.parseError = 0 Then
    strProductInfo = objXML.transformNode(objXSL)
    Response.Write strProductInfo
    Call DisplayError("There has been a problem retrieving the product information")
  End If
  ' clean up objects
  Set objXML = Nothing
  Set objXSL = Nothing

Creating Word Documents with ASP

Created 13 November 2002 00:00
Using this code you can create a word document which you can then allow your users to download. All you need is to have Word installed on your web server, and to have a template file ( available to the application to open up.
  ' dim variables
  Dim wObject, wDocument

  ' create word document object
  Set wObject = CreateObject("Word.Application")
  ' Set temp word file
  Set wDocument = wObject.Documents.Add(Server.MapPath(".") & "")

  ' choose the font that you want for your document (will default to Times New 
  ' Roman if you leave this out)
  wObject.Selection.Font.Name = "Verdana"
  ' write text to the page
  wObject.Selection.TypeText("Plain verdana")
  ' new paragraph
  ' insert page break 
  ' add picture to document
  wObject.Selection.InlineShapes.AddPicture Server.MapPath(".") & "" & "picName.gif"
  ' change the size of the font
  wObject.Selection.Font.Size = 10
  ' change the colour and style of the font
  wObject.Selection.Font.ColorIndex = 2
  wObject.Selection.Font.Bold = True
  wObject.Selection.Font.Italic = False
  ' justify the new parapgraph in the word document (0=left, 1=center, 2=right)
  wObject.Selection.ParagraphFormat.Alignment = 1

  ' save document
  wDocument.SaveAs Server.MapPath(".") & "Output.doc"

  ' quit the word application - very important to prevent multiple 
  ' instances of Word running on the server.  Best approach is to 
  ' have an On Error Resume Next on this page so this line is always 
  ' executed - rather manually trap errors.

  ' clean up
  Set rs = Nothing
  Set wObject = Nothing
  Set wDocument = Nothing
Inserting tables into word documents

You need to create tables into a range in the document - generally I find the easiest way is to have a document template with a bookmark already created in the template. In the following example, a bookmark called "TEST" is created, and the range used is the range of that bookmark. Assuming "obj" is a reference to a running word application object:

  Dim doc       ' word document
  Dim tbl       ' table you are going to create
  Set doc = obj.Documents.Open("D:\Matt\Test.doc")
  Set tbl = doc.Tables.Add(doc.Bookmarks("TEST").Range, 3, 5)
  tbl.Cell(1, 1).Range.Text = "hello"
or alternatively, if you don't want to use bookmarks and just want to add tables to the end of a document, use the paragraph object's range:
  Dim oPar as Word.Paragraph
  Dim oTable as Word.Table
  ' add first table
  Set oPar = oDoc.Paragraphs.Add()
  Set oTable = oDoc.Tables.Add(oPar.Range, 3, 5)
  ' add second table
  Set oPar = oDoc.Paragraphs.Add()
  Set oTable = oDoc.Tables.Add(oPar.Range, 3, 5)

asp : Adding XML nodes to an existing document

Created 05 November 2002 00:00
If you need to insert nodes into an XML file that already exists, use the Microsoft XMLDOM object to achieve this with ASP.
' Adds nodes to an existing XML document
Sub AddNodes
  Dim objXML
  Dim arrFields
  Dim objNewElem
  Dim objRoot
  Dim sql
  Dim strFields
  Dim strFieldName
  ' create the object to manipulate XML file
  Set objXML = Server.CreateObject("Microsoft.XMLDOM")
  ' load the file
  objXML.load(strPath & "" & strFileName)
  ' get the main elements by the tag name
  Set collElements = objXML.getElementsByTagName(strTagName)
  ' create a collection object of all the child nodes in the XML file
  Set collChildren = collElements.item(0).childNodes
  ' add elements to XML file
  For intFieldCounter = 0 To 10
    Set objRoot = objXML.documentElement
    Set objNewElem = objXML.createElement(intFieldCounter)
    ' add the new element to the XML file
    objRoot.childNodes.item(I).appendChild objNewElem
    ' add the field value to the created node as CDATA (so ignores illegal characters)
    objRoot.childNodes.item(I).lastChild.appendChild objXML.createCDATASection("text goes here") 
  ' save the modified XML file & "" & strFileName)
  Set objXML = Nothing
End Sub

XPath Queries with ASP

Created 05 November 2002 00:00
This shows you how to execute XPath queries. The server you are using will need to have ADO 2.6 and Microsoft SQL Server 2000 installed. The query returns XML which can be manipulated using the MS DOM object.
  Dim conn    ' connection object
  Dim cmd     ' command object
  Dim strm    ' stream object
  Set conn = Server.CreateObject"ADODB.Connection.2.6")
  Set cmd = Server.CreateObject("ADODB.Command.2.6")
  conn.Open "connection string"
  Set cmd.ActiveConnection = conn
  Set strm = Server.CreateObject("ADODB.Stream.2.6")
  '   open the result stream so it may receive the output from the execute
  '   set the command type to an XPath query
  cmd.Dialect = "{ec2a4293-e898-11d2-b1b7-00c04f680c56}"
  '   set the file name for the mapping schema
  cmd.Properties("Mapping Schema") = Server.MapPath(".") & "Yourfile.xdr"
  '   hook up the command to the result stream
  cmd.Properties("Output Stream") = strm
  '   set the actual text for the XPath command
  cmd.CommandText = "/ARTICLES/ARTICLE[A_ARTICLE_ID=1]"
  '   execute the command stream
  cmd.Execute , , 1024
  '   reset the stream's position in order to read it
  strm.Position = 0
  '   set the displayed results to the command's output
  txtResults = strm.ReadText
  '   clean up the output to make easier to read
  txtResults =  Replace(txtResults,">", ">")
  txtResults =  Replace(txtResults,"<", "<")
  txtResults = Replace(txtResults, "><", "><br><")
  Set strm = Nothing
  Set cmd = Nothing
  Set conn = Nothing
  Response.Write "<br>" & txtResults

Reverse DNS Lookups with DOS and ASP

Created 15 November 2002 00:00


nslookup <ip_address>


You need Windows Script Host 2.0 or higher installed in order to do this, but on a Windows NT or Windows 2000 machine you can perform reverse DNS lookups using the following code:
  Dim oFs
  Dim oShell 
  Dim oStream
  Dim temp 
  Dim sHost
  sHost = Request.Form("sHost")
  ' create shell object
  Set oShell = Server.CreateObject("Wscript.Shell")
  ' run NSLookup via command prompt
  oShell.Run "%ComSpec% /c nslookup " & sHost & "> C:" & sHost & ".txt", 0, True

  ' open the temp text file and write the contents to your page
  Set oFS = Server.CreateObject("Scripting.FileSystemObject")
  Set oStream = oFS.OpenTextFile("C:" & sHost & ".txt")

  Do While Not oStream.AtEndOfStream
    Response.Write "<br>" & Trim(oStream.Readline)
  ' clean up    
  oFS.DeleteFile "C:" & sHost & ".txt"
  Set oFS = Nothing

Retrieving data as a stream using ADO

Created 05 November 2002 00:00
Use ADO to return data as a stream. This is extremely useful for XML queries, allowing you retrieve a large XML dataset into a stream in one go.
  Dim m_oConn			' connection object
  Dim m_oCmd			' command object
  Dim m_strQuery		' query string
  Dim m_oStreamQuery	' query stream to retrieve info
  Dim m_oStreamOut	' output stream
  Dim m_oDict			' dictionary object
  ' open connection to the database
  Set m_oConn = Server.CreateObject("ADODB.Connection")
  m_oConn.Open strConn
  ' set up XPath query
  m_strQuery = "" _ 
    & "exec sp_GetDropDownBoxInfoAsXML"
  ' open up stream objects
  Set m_oStreamQuery = Server.CreateObject("ADODB.Stream")
  Set m_oStreamOut = Server.CreateObject("ADODB.Stream")
  ' initialize the query streams
  With m_oStreamQuery
    .WriteText m_strQuery, 0
    .Position = 0
  End With
  ' create and set up command object
  Set m_oCmd = Server.CreateObject("ADODB.Command")
  With m_oCmd
    Set .ActiveConnection = m_oConn
    .CommandStream = m_oStreamQuery
    .Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
    .Properties("Output Stream") = m_oStreamOut
    .Execute , , 1024
  End With
  ' create dictionary object
  Set m_oDict = Server.CreateObject("Commerce.Dictionary")
  m_oDict.strXML = m_oStreamOut.ReadText
  Set Global_Load_DDB_Info = m_oDict
  ' clean up
  Set m_oDict = Nothing
  Set m_oStreamQuery = Nothing
  Set m_oStreamOut = Nothing
  Set m_oCmd = Nothing
  Set m_oConn = Nothing

Submitting a message to MSMQ using VB

Created 05 November 2002 00:00
For this code to run you need to add a reference to the Microsoft Message Queue Object Library - this will only be available on your machine if you add the Message Queue Windows component (can be added via Start/Remove Programs).
    ' create message queue objects
    Dim objQueueInfo As New MSMQQueueInfo
    Dim objQueue As MSMQQueue
    Dim objMsg As New MSMQMessage
    Dim strQName As String
    ' create queue name
    strQName = "DIRECT=OS:MACHINEPrivate$queuename"
    ' set queue information settings
    objQueueInfo.FormatName = strQName
    objQueueInfo.Label = "Test Queue"
    ' create queue object from queue info
    Set objQueue = objQueueInfo.Open(MQ_SEND_ACCESS, MQ_DENY_NONE)
    ' create message with name and body text
    objMsg.Label = "Test Message"
    objMsg.Body = "message body here"
    ' send the message to the queue and clean up
    objMsg.send objQueue, MQ_SINGLE_MESSAGE
    Set objQueueInfo = Nothing
    Set objQueue = Nothing
    Set objMsg = Nothing