XML based update gram for SQL server doesn't work

[Painkiller]

Hi all,

I'm trying to add new records to a SQL server 2000 DB using XML based updategrams in a VB app. However, it seems that the updategram isn't executed or interpreted. I just get the updategram back in the resulting stream.

The table Locatietypen consists of 2 fields:

id (integer with autonumber on) and Naam (text)

I use the following updategram:

strSQL = &quot;<root xmlns:sql=&quot;&quot;urn:schemas-microsoft-com:xml-sql&quot;&quot;>&quot; & _
&quot;<sql:sync> <sql:after> <LocatieType naam=&quot;&quot;Tester&quot;&quot; /> &quot; & _
&quot;</sql:after> </sql:sync> &quot; & _
&quot;<sql:query>SELECT naam FROM LocatieTypen AS LocatieType FOR XML AUTO &quot; & _
&quot;</sql:query> &quot; & _
&quot;</root> &quot;

I use the following code to add a new record:

Public Sub Add(ByVal strSQL As String)

' ADO Database connection
Dim cnnDBConn As New ADODB.Connection
' ADO Command to execute Updategram
Dim cmdUpdategram As New ADODB.Command
' ADO Stream object to store input (i.e. Updategram)
Dim strmUpdategram As New ADODB.Stream
' ADO Stream object to store output (i.e. Results)
Dim strmResults As New ADODB.Stream

cnnDBConn.ConnectionString = GetConnectionString
cnnDBConn.CursorLocation = adUseClient
cnnDBConn.Open

Set cmdUpdategram.ActiveConnection = cnnDBConn

' Use the SQL Server 2000 XML Dialect
' cmdUpdategram.Dialect = &quot;{5d531cb2-e6ed-11d2-b252-00c04f681b71}&quot;

Set strmUpdategram = New ADODB.Stream
strmUpdategram.Open
strmUpdategram.WriteText strSQL
strmUpdategram.Position = 0
Set cmdUpdategram.CommandStream = strmUpdategram

' Open the Results (Output) Stream ready to catch the output
Set strmResults = New ADODB.Stream
strmResults.Open
cmdUpdategram.Properties(&quot;Output Stream&quot.Value = strmResults
cmdUpdategram.Properties(&quot;Output Encoding&quot = &quot;UTF-8&quot;

' Execute the Updategram
cmdUpdategram.Execute , , adExecuteStream


' show contents of the Results stream
MsgBox strmResults.ReadText


End Sub

However, in the resulting stream, I just get the updategram back, followed by an XML listing of the table. Apparently the updategram isn't being executed. Anybody know how to solve this one?

Much appreciated.