(c) 2002 Visual Studio Magazine
Fawcette Technical Publications

Issue: April 2002
Section: Power Up Your Database Apps With XML
Author: Daniel Anderson and Mary V. Hooke


Load the Grid Control With Category Data
Listing 1 This code illustrates how to work with the results of an XML query.  This example takes the results of an XML template and populates it into a grid.

Private Sub LoadCategoryGrid()
	Dim strQuery As String
	Dim XmlDoc As MSXML2.DOMDocument
	Dim XmlRoot As IXMLDOMNode
	Dim XmlNode As IXMLDOMElement
	Dim XmlChildNode As IXMLDOMElement
	Dim XmlHttp As MSXML2.XmlHttp
	Dim lngRowCount, lngCurRow , t As Long

	' Clear and prepare the FlexGrid.  Column 0 is 
	' hidden, so its length is set to zero
	With gridCats
		.TextMatrix(0, 0) = "ID"
		.TextMatrix(0, 1) = "Category"
		.ColWidth(0) = 750
		.ColWidth(1) = 3650
	End With

	' Prepare the query to be run.  strQuery 
	' contains the path to the template 
	' sub-directory of the virtual directory
	strQuery = strTemplateDir & _
		"getcategories.xml"

	Set XmlDoc = CreateObject _
		("MSXML2.DOMDocument")
	Set XmlHttp = CreateObject("MSXML2.XMLHTTP")

	' We are going to use a POST request to call 
	' the template
	XmlHttp.open "POST", strQuery, False
	XmlHttp.setRequestHeader "Content-type", _
		"text/xml"
	XmlHttp.send

	' We set the async property of the XML 
	' document to false to ensure it
	' is completely loaded before continuing
	XmlDoc.async = False

	' We load the XML document returned from the 
	' template query into the DOM object and set 
	' the root of the document
	XmlDoc.Load XmlHttp.responseXML
	Set XmlRoot = XmlDoc.documentElement
	Set XmlNode = XmlRoot.selectSingleNode( _
		"Categories")

	' We want the number of nodes returned in the 
	' document so we can ensure the grid has the 
	' correct number of rows, which is equal to 
	' the node count plus 1 for the row header of 
	' the grid
	lngRowCount = XmlNode.childNodes.length

	gridCats.Rows = lngRowCount + 1
	lngCurRow = 1

	' Finally, we iterate through the child nodes 
	' and populate the grid with the data
	For Each XmlChildNode In XmlNode.childNodes
		gridCats.TextMatrix(lngCurRow, 0) = _
			XmlChildNode.getAttribute _
			("Category_ID")
		gridCats.TextMatrix(lngCurRow, 1) = _
			XmlChildNode.getAttribute("Category")
		lngCurRow = lngCurRow + 1
	Next
End Sub

Add or Update Data
Listing 2	This code illustrates how to use XML updategrams with VB.  Although VB isn't case sensitive, it's important to notice that your XML code, including the parameter names, is case sensitive.

Private Sub cmdSave_Click()

	Dim strQuery As String
	Dim strRowData As String
	Dim XmlDoc As MSXML2.DOMDocument
	Dim XmlRoot As IXMLDOMNode
	Dim XmlNode As IXMLDOMElement
	Dim XmlHttp As MSXML2.XmlHttp

	Screen.MousePointer = vbHourglass: DoEvents

	Set XmlDoc = CreateObject _
		("MSXML2.DOMDocument")
	Set XmlHttp = CreateObject("MSXML2.XMLHTTP")

	mCategory.Category = FormatString _
		(txtCategory.Text)

	If mCategory.Category_ID = 0 Then
		strQuery = strTemplateDir & _
			"savenewcategory.xml"
		XmlHttp.open "POST", strQuery, False
		XmlHttp.send "Category=" & _
			mCategory.Category
	Else
		strQuery = strTemplateDir & _
			"saveexistingcategory.xml"
		XmlHttp.open "POST", strQuery, False
		XmlHttp.send "OldCategory_ID=" & _
			mCategory.Category_ID & "&Category=" _
			& mCategory.Category
	End If

	XmlDoc.async = False
	XmlDoc.Load XmlHttp.responseXML

	MsgBox XmlDoc.xml

	Call LoadCategoryGrid
	Call ResetForm

	Screen.MousePointer = vbNormal: DoEvents
End Sub
