AJAX In Action [164]
statement
" WHERE regionid = " & _
strQuery & " ORDER BY " & _
"TerritoryDescription"
Licensed to jonathan zheng Implementing the server: VB .NET 337 Dim dtOptions As DataTable dtOptions = FillDataTable(strSql) e Execute the SQL statement Dim strXML As StringBuilder strXML = New StringBuilder(""version=""1.0"" ?>") strXML.Append(" strXML.Append(" f Begin XML strXML.Append(" document strForm & _ " strXML.Append(" strElem & _ " strXML.Append(" If dtOptions.Rows.Count > 0 Then g Verify there are results strXML.Append(" strXML.Append(" "Select A Territory" & _ h Add first selection " element strXML.Append(" " strXML.Append(" Dim row As DataRow For Each row In dtOptions.Rows strXML.Append(" strXML.Append(" row("TerritoryDescription") & _ i Loop through result set and " add XML strXML.Append(" elements row("TerritoryID") & _ " strXML.Append(" Next End If strXML.Append(" j Return the XML document Response.Write(strXML.ToString) End Sub Public Function FillDataTable( _ ByVal sqlQuery As String) _ As DataTable Licensed to jonathan zheng 338 CHAPTER 9 Dynamic double combo Dim strConn As String = _ "Initial Catalog = Northwind; " & _ "Data Source=127.0.0.1; " & _ "Integrated Security=true;" Dim cmd1 As _ New SqlClient.SqlDataAdapter(sqlQuery, _ strConn) Dim dataSet1 As New DataSet cmd1.Fill(dataSet1) cmd1.Dispose() Return dataSet1.Tables(0) End Function Setting the page’s content type b to text/xml ensures that the XMLHttpRequest will parse the server response correctly on the client. We obtain the value of the selected region, the HTML form name, and the element name from the request parameters c received from the client. For added safety, we could add a check here to make sure that these values are not null. If the check does not find a value for each, the script could return an error response. We should also add checks for SQL injection before the application enters a production environment. This would ensure that the database is protected from malicious requests sent by attackers. Having obtained the selected region’s value, the next step is to generate a SQL string so we can retrieve the corresponding territories from the database d. The two columns we are interested in are TerritoryDescription and TerritoryID, from the database table Territories. We insert the region value into the SQL statement’s WHERE clause. To ensure that the results appear in alphabetical order in our selection list, we also set the SQL ORDER BY clause to TerritoryDescription. Next, we must execute the SQL statement e. In this case, we call the function FillDataTable() to create a connection to the database server, perform the query, and return the results in a data table. Now that we have obtained the result of the SQL query, we need to create the first part of the XML document f, which was discussed in listing 9.2. We begin the document and add the selectElement, containing the values of formName and formElem obtained from the request parameters. A check is needed to verify if any results were returned by the SQL query g. If there are results, we add the preliminary “Select A Territory” option h to the XML. Licensed to jonathan zheng Presenting the results 339 Next we loop through the results represented in the DataTable i, populating the value of the TerritoryDescription column into the optionText tag and the value of the TerritoryID column into the optionValue tag. By nesting each description/ID pair inside an entry tag, we provide an easier means to loop through the values on the client, with JavaScript’s XML DOM methods. After we finish populating our results into the XML document, we need to close