adding new record using XML based-SQL updategrams doesn't work

[Painkiller]

Hi all,

I'm trying to add a new line to a table on a SQL server 2000 database by using SQL updategrams in a vb app. The program executes all the lines and I get no errors. However, the line isn't added in the table.

The table ("Locatietypen&quot consist 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:


ublic 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

Now, in the messagebox I get, the item &quot;Tester&quot; has been added to the table. However, when I check the DB using SLQ enterprise manager, the new record hasn't been added! Anybody know how to solve this one?

Much appreciated.

 

[Painkiller]

Please discard the last few lines. In the msgbox, the new record hasn't been added. I just get a stream of the updategram and then a stewam of the table.