Use OPENXML to Minimize SQL Server Database Calls

article about the use of parenthesis to force ByVal that explains this further. It makes sense to pass the array ByVal in this case because you are passing to a middle tier component across the network. Here is a code snippet from the ASP page showing how to pass the array ByVal:

Listing 1 – ASP Calling COM Function Dim objBusEval, arrSurvey
set objBusEval = server.CreateObject(“SurveyXML.savesurvey”)
if objBusEval.SurveyToXML((arrsurvey)) then 

end if The following function is called by the ASP page (see Listing 1):

Listing 2 – VB COM Function Public Function SurveyToXML( ByVal arrSurvey As Variant) As Boolean
Dim objXMLdoc As MSXML.DOMDocument
Dim objXMLRootElem As MSXML.IXMLDOMElement
Dim objXMLAnswers As MSXML.IXMLDOMElement
Dim objXMLAttribEvalID As IXMLDOMAttribute
Dim objXMLAttribQnum As IXMLDOMAttribute
Dim objXMLAttribAnswer As IXMLDOMAttribute
Dim x As Integer
Dim y As Integer
Dim EvalID As Integer
Dim objDBSurvey As dbSurvey.dbSaveSurvey On Error GoTo ErrHandler Set objDBSurvey = New dbSurvey.dbSaveSurvey EvalID = objDBSurvey.GetEvalID(“Alex”, “Rodriguez”, “”, 1, vbNullString) Set objXMLdoc = New MSXML.DOMDocument With objXMLdoc Set objXMLRootElem = .createElement(“insert”)
.appendChild objXMLRootElem For y = LBound(arrSurvey, 2) To UBound(arrSurvey, 2) Set objXMLAnswers = .createElement(“Answers”)
.documentElement.appendChild objXMLAnswers Set objXMLAttribEvalID = .createAttribute(“EvalID”)
objXMLAttribEvalID.nodeValue = EvalID
objXMLAnswers.setAttributeNode objXMLAttribEvalID Set objXMLAttribQnum = .createAttribute(“QNum”)
objXMLAttribQnum.nodeValue = arrSurvey(0, y)
objXMLAnswers.setAttributeNode objXMLAttribQnum Set objXMLAttribAnswer = .createAttribute(“Answer”)
objXMLAttribAnswer.nodeValue = arrSurvey(1, y)
objXMLAnswers.setAttributeNode objXMLAttribAnswer Next y If objDBSurvey.SaveXMLResponse(.xml) Then
SurveyToXML = True
SurveyToXML = False
End If
End With SurveyToXML = True Cleanup:
Set objDBSurvey = Nothing
Set objXMLdoc = Nothing
Exit Function ErrHandler:
SurveyToXML = False
GoTo Cleanup
End Function The client passes in a two-dimensional variant array with the first dimension index 0 as the Question Number (Qnum) and first dimension index 1 as the Answer. The second dimension of the array represents the ordinal number of questions answered. The EvalID, which connects the answers to a specific evaluation or survey, is created and returned from the database. The three values required to insert an answer to a specific survey question are the EvalID, the Qnum, and the Answer.


Leave a comment

Your email address will not be published.