ASP.net, MSSQL XML QUERY, 2048 char problem.

[beachnerd]

Hey,

I am having some trouble with my current project. i am querying data from mssql 2005 express with an xml output, but it cutting the query off after char 2048.

My SQL Query Is...

SELECT tDataType.tDataType_ID, tDataType.tDataType_Name, tDataType.tDataType_Type, DVALUES.tDataTypeValue_ID, DVALUES.VALUE, DVALUES.DataValue, tDataType.tDataType_Required AS Required, (SELECT TOP (1) tDataTypeValue AS textvalue FROM tMemberData WHERE (tDataType.tDataType_ID = tDataType_ID) AND (tMember_ID = @memberid)) AS TextValue FROM tDataType LEFT OUTER JOIN (SELECT tDataTypeValue_ID, tDataTypeValue_Value AS VALUE, tDataType_ID AS DataID, (SELECT TOP (1) 'Selected' AS selected FROM tMemberData WHERE (tDataTypeValue.tDataType_ID = tDataType_ID) AND (tMember_ID = @memberid) AND (tDataTypeValue.tDataTypeValue_ID = tDataTypeValue_ID)) AS DataValue FROM tDataTypeValue) AS DVALUES ON DVALUES.DataID = tDataType.tDataType_ID WHERE tDataType.tAccountGroup_ID = @accountgroup ORDER BY tDataType.tDataType_Order FOR XML AUTO, ROOT('controls')

the asp.net code is

Dim sql_cd as sqlcommand(Query, Connection)
Dim ret As XmlReader
 
        With sql_cd
 
            ret = .ExecuteXmlReader
            .Connection.Close()
            .Connection.Dispose()
            .Dispose()
 
        End With
 
        Dim xmldoc As New XmlDocument
        xmldoc.Load(ret)
        ret.Close()
 
        'xmldoc is the output value

Could Someone please tell me why asp.net is cutting the xml query off after char 2048

Kind Regards
Beachnerd

[pufa]

I suspect that is because you are closing the reader and the connection before loading XmlReader into the xml document.

cheers,
Rui

[beachnerd]

Has Been Fixed Awesome!

Shared Function ExecuteXML(ByVal sql As String, ByVal hsh As Hashtable, Optional ByVal datatype As Data.CommandType = CommandType.StoredProcedure, Optional ByVal which As String = "main") As XmlDocument
 
        Dim sql_cd As SqlCommand = GetDataCommand(sql, hsh, datatype, which)
        Dim ret As XmlReader
 
        ret = sql_cd.ExecuteXmlReader
 
        Dim xmldoc As New XmlDocument
        xmldoc.Load(ret)
 
        'Close Everything
        With sql_cd
            ret.Close()
            .Connection.Close()
            .Connection.Dispose()
            .Dispose()
        End With
 
        Return xmldoc
 
    End Function

Kind Regards
Beachnerd