Executing an Updategram by Using ADO (SQLXML 4.0)
Applies to: 
SQL Server
Azure SQL Database
This Microsoft Visual Basic application uses ADO to establish a connection to an instance of Microsoft SQL Server and execute an updategram. The updategram updates the last name of a specific employee. This example uses the AdventureWorks sample database.
In this sample application:
The conn object (ADODB.Connection) establishes a connection to a running instance of SQL Server on a specific server computer.
The cmd object (ADODB.Command) executes on the established connection.
The command dialect is set to DBGUID_MSSQLXML.
The updategram is copied to the command stream (strmIn).
The command's output stream is set to the StrmOut object (ADODB.Stream) to receive any returned data.
Finally the command (updategram) is executed.
Here is the sample code:
Private Sub Form_Load()
Dim cmd As New ADODB.Command
Dim conn As New ADODB.Connection
Dim strmIn As New ADODB.Stream
Dim strmOut As New ADODB.Stream
Dim SQLxml As String
' Open a connection to the instance of SQL Server.
conn.Provider = "SQLOLEDB"
conn.Open "server=(local); database=AdventureWorks; Integrated Security=SSPI; "
conn.Properties("SQLXML Version") = "SQLXML.4.0"
Set cmd.ActiveConnection = conn
' Build the command string in the form of an XML template.
SQLxml = "<ROOT xmlns:updg='urn:schemas-microsoft-com:xml-updategram' >"
SQLxml = SQLxml & " <updg:sync updg:nullvalue='IsNULL'>"
SQLxml = SQLxml & " <updg:before>"
SQLxml = SQLxml & " <Person.Contact ContactID='64' Title='IsNULL'/>"
SQLxml = SQLxml & " </updg:before>"
SQLxml = SQLxml & " <updg:after>"
SQLxml = SQLxml & " <Person.Contact ContactID='64' Title='Mr.'/>"
SQLxml = SQLxml & " </updg:after>"
SQLxml = SQLxml & " </updg:sync>"
SQLxml = SQLxml & "</ROOT>"
' Set the command dialect to DBGUID_MSSQLXML.
cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
' Open the command stream and write our template to it.
strmIn.Open
strmIn.WriteText SQLxml
strmIn.Position = 0
Set cmd.CommandStream = strmIn
' Execute the command, open the return stream, and read the result.
strmOut.Open
strmOut.LineSeparator = adCRLF
cmd.Properties("Output Stream").Value = strmOut
cmd.Properties("Output Encoding").Value = "UTF-8"
cmd.Execute , , adExecuteStream
strmOut.Position = 0
Debug.Print strmOut.ReadText
strmOut.Close
strmIn.Close
End Sub
Note
If you're using SQLXML from ADO to execute updategrams that specify an XSD schema, you must set the "SQLXML Version" property to "SQLXML.4.0" on the connection object, as shown in the following example line of code:
conn.Properties("SQLXML Version") = "SQLXML.4.0"
Specifying a Mapping Schema for the Updategram
This example illustrates how to specify and use a mapping schema in an updategram.
Save the following XSD schema (EmpSchema.xml) to your disk, and be sure to update the path that is specified in the code to the location of the mapping schema on your disk. The code assumes that the schema is saved on the C: drive in the Schemas folder.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Contact" sql:relation="Person.Contact" >
<xsd:complexType>
<xsd:attribute name="CID"
sql:field="ContactID"
type="xsd:string" />
<xsd:attribute name="MName"
sql:field="MiddleName"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:schema>
Because both XSD and XDR schemas can be specified, this is the equivalent XDR schema:
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<ElementType name="Contact" sql:relation="Person.Contact" >
<AttributeType name="CID" />
<AttributeType name="MName" />
<attribute type="CID" sql:field="ContactID" />
<attribute type="MName" sql:field="MiddleName" />
</ElementType>
</Schema>
This is the Visual Basic code to execute an updategram that has an associated mapping schema. The updategram updates the middle name for contact 1 in the Person.Contact table.
Private Sub Form_Load()
Dim cmd As New ADODB.Command
Dim conn As New ADODB.Connection
Dim strmIn As New ADODB.Stream
Dim strmOut As New ADODB.Stream
' Open a connection to the SQL Server.
conn.Provider = "SQLOLEDB"
conn.Open "server=(local); database=AdventureWorks; Integrated Security='SSPI' ;"
conn.Properties("SQLXML Version") = "SQLXML.4.0"
Set cmd.ActiveConnection = conn
' Open the command stream and write the template to it.
strmIn.Open
strmIn.WriteText "<ROOT xmlns:updg='urn:schemas-microsoft-com:xml-updategram' >"
strmIn.WriteText " <updg:sync mapping-schema='C:\Schemas\EmpSchema.xml' >"
strmIn.WriteText " <updg:before>"
strmIn.WriteText " <Contact CID='1' />"
strmIn.WriteText " </updg:before>"
strmIn.WriteText " <updg:after>"
strmIn.WriteText " <Contact MName='M.'/>"
strmIn.WriteText " </updg:after>"
strmIn.WriteText " </updg:sync>"
strmIn.WriteText "</ROOT>"
' Set the command dialect to XML.
cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
strmIn.Position = 0
Set cmd.CommandStream = strmIn
' Execute the command, open the return stream, and read the result.
strmOut.Open
strmOut.LineSeparator = adCRLF
cmd.Properties("Output Stream").Value = strmOut
cmd.Execute , , adExecuteStream
strmOut.Position = 0
Debug.Print strmOut.ReadText
strmOut.Close
strmIn.Close
conn.Close
End Sub
Passing Parameters
In the Visual Basic applications provided earlier, parameters are not passed. In this application, the ContactID and MiddleName values are passed as parameterized input to the updategram.
Private Sub Form_Load()
Dim cmd As New ADODB.Command
Dim conn As New ADODB.Connection
Dim strmIn As New ADODB.Stream
Dim strmOut As New ADODB.Stream
Dim InputContactID As String
Dim InputMiddleName As String
InputContactID = "1"
InputMiddleName = "Q."
' Open a connection to the instance of SQL Server.
conn.Provider = "SQLOLEDB"
conn.Open "server=(local); database=AdventureWorks; Integrated Security=SSPI; "
conn.Properties("SQLXML Version") = "SQLXML.4.0"
Set cmd.ActiveConnection = conn
' Build the command string in the form of an XML template.
SQLxml = "<ROOT xmlns:updg='urn:schemas-microsoft-com:xml-updategram' >"
SQLxml = SQLxml & "<updg:header>"
SQLxml = SQLxml & "<updg:param name='ContactID'/>"
SQLxml = SQLxml & "<updg:param name='MiddleName' />"
SQLxml = SQLxml & "</updg:header>"
SQLxml = SQLxml & "<updg:sync >"
SQLxml = SQLxml & " <updg:before>"
SQLxml = SQLxml & " <Person.Contact ContactID='$ContactID' />"
SQLxml = SQLxml & "</updg:before>"
SQLxml = SQLxml & "<updg:after>"
SQLxml = SQLxml & "<Person.Contact MiddleName='$MiddleName' />"
SQLxml = SQLxml & "</updg:after>"
SQLxml = SQLxml & "</updg:sync>"
SQLxml = SQLxml & "</ROOT>"
' Set the command dialect to XML.
cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
' Open the command stream and write the template to it.
strmIn.Open
strmIn.WriteText SQLxml
strmIn.Position = 0
Set cmd.CommandStream = strmIn
' Execute the command, open the return stream, and read the result.
strmOut.Open
strmOut.LineSeparator = adCRLF
cmd.NamedParameters = True
cmd.Parameters.Append cmd.CreateParameter("@ContactID", adBSTR, adParamInput, 1, InputContactID)
cmd.Parameters.Append cmd.CreateParameter("@MiddleName", adBSTR, adParamInput, 7, InputMiddleName)
cmd.Properties("Output Stream").Value = strmOut
cmd.Execute , , adExecuteStream
strmOut.Position = 0
Debug.Print strmOut.ReadText
End Sub
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for