Adam Fasoldt

Hello.

I am creating a little app that takes information from an XML database and prints it to a table. In addition, there are buttons in the header of the table that call for the refresh of the table with the column clicked as the sort for the table.

At any rate, it's the datatype that's giving me a problem. When the datatype is not used on the numeric column, and it's just processed as a string, it doesn't throw an error at all, but it sorts as a string...

1
12
123
2
234
244
256
3
33

And that won't do, of course. I need it to sort numerically. I had anticipated this and used "tostring" on all my row/column references. When I went and changed the field to datatype="numeric" in the XML file, my project exploded.

Here's the codeBehind:

Code Snippet

Public Function PrintValues(ByVal dataSet As DataSet, ByVal label As String, ByVal sortVar As String) As String
Dim thisReturnString As String = ""

Dim table As DataTable
Dim row As DataRow
Dim column As DataColumn
Dim rowstring As String = ""

'row array
Dim foundRows() As DataRow

Dim looper As Integer = 0
For Each table In dataSet.Tables
If sortVar = "" Then
sortVar = "word"
End If
' Use the Select method to find all rows matching the filter.
foundRows = table.Select("", sortVar)
'For Each row In table.Rows
For Each row In foundRows
thisReturnString += "<tr>"
For Each column In table.Columns
If row(column).ToString() = "" Then
rowstring = "&nbsp"
Else
rowstring = row(column).ToString()
End If
thisReturnString += ("<td class=""dBaseTable"">" + rowstring + "</td>")
Next column
thisReturnString += "</tr>"
Next row
Next table
dataSet.Clear()
Return thisReturnString
End Function


(I know I need to add some tabs and vbCrlFs and stuff in there to make the HTML look pretty, but this is just a preliminary thing anyways)

And here is the database that works:

Code Snippet

< xml version="1.0" encoding="utf-8" >
<searchWords>
<aWord>
<word>hello</word>
<modifier>and</modifier>
<timesSearched>5</timesSearched>
<result>foo</result>
</aWord>
<aWord>
<word>hello</word>
<modifier>not</modifier>
<timesSearched>5</timesSearched>
<result>
</result>
</aWord>
<aWord>
<word>spam</word>
<modifier>and</modifier>
<timesSearched>8</timesSearched>
<result>foo</result>
</aWord>
<aWord>
<word>spam</word>
<modifier>not</modifier>
<timesSearched>3</timesSearched>
<result>
</result>
</aWord>

<aWord>
<word>world</word>
<modifier>not</modifier>
<timesSearched>231</timesSearched>
<result>
</result>
</aWord>
</searchWords>




And here's the one that throws the errors:

Code Snippet

< xml version="1.0" encoding="utf-8" >
<searchWords>
<aWord>
<word>hello</word>
<modifier>and</modifier>
<timesSearched datatype="integer">5</timesSearched>
<result>foo</result>
</aWord>
<aWord>
<word>hello</word>
<modifier>not</modifier>
<timesSearched datatype="integer">5</timesSearched>
<result>
</result>
</aWord>
<aWord>
<word>spam</word>
<modifier>and</modifier>
<timesSearched datatype="integer">8</timesSearched>
<result>foo</result>
</aWord>
<aWord>
<word>spam</word>
<modifier>not</modifier>
<timesSearched datatype="integer">3</timesSearched>
<result>
</result>
</aWord>
<aWord>
<word>world</word>
<modifier>and</modifier>
<timesSearched datatype="integer">231</timesSearched>
<result>foo</result>
</aWord>
</searchWords>




First, it says it can't find the column:

System.IndexOutOfRangeException: Cannot find column word.

When I refresh, it throws this:

System.IO.IOException: The process cannot access the file 'PATH\App_Data\formSearchStrings.xml' because it is being used by another process.

I find this very odd.

Thanks, in advance, for your consideration of my problem.



Re: .NET Framework Data Access and Storage XML Datatype Throwing an Error

Derek Smyth

Hi mate,

Your loading your xml into a generic dataset which requires that the xml is in a specific format. The original xml is in that format but once you added the datatype attribute the xml no longer matched the required format and it blew up. DataSet acts like a database table and it's default implementation requires that all columns are mapped to elements in the xml. In a strongly typed dataset then you can specify which columns map to attributes but the way your doing it the data tables columns are automatically generated and that means it's expecting elements.

If you did this... then it would be read it.... but it doesn't do what you want.

Code Snippet

<aWord>
<word>hello</word>
<modifier>and</modifier>
<timesSearched>5</timesSearched>

<timesSearchedDatatype>Integer</timesSearchedDatatype>
<result>foo</result>
</aWord>

I understand what your looking to do, your trying to read the timesSearched element into your data set as a column with an integer data type. To do that you need to update the datatable within the dataset inside your code and not the xml file.

You could try this after reading the file might not work though....

DataSet.DataTables("aWord").Columns("timesSearched").DataType = System.Type.GetType("System.Int32")

It might not work if you have data already in the data table, not sure, but it's worth a try. The other option is to build the aWord table through code and add it to the dataset (See the DataColumn.DataType help for an example of this). There are a couple of other options available but see if the above works first.