Есть вот такая хранимая процедура:
CREATE PROCEDURE [dbo].[spSaveMyProfile]
(
@data ntext = null
)
AS
SET NOCOUNT ON
DECLARE @hDoc int
if (@data is not null)
BEGIN
EXEC sp_xml_preparedocument @hDoc OUTPUT, @data
--UPDATE
UPDATE Users
SET
Name = XmlUsers.Name,
[Family name] = XmlUsers.[Family name],
[Logo] = XmlUsers.Logo
FROM
OPENXML(@hDoc, '/NewDataSet/My_x0020_profile')
WITH (
ID int,
Name nvarchar(50),
[Family name] nvarchar(50),
[Logo] image
) XmlUsers
WHERE
Users.ID = XmlUsers.ID
--INSERT
INSERT INTO Users
(ID,
Name,
[Family name],
Logo)
SELECT
XmlUsers.ID,
XmlUsers.Name,
XmlUsers.[Family name],
XmlUsers.[Logo]
FROM
OPENXML(@hDoc, 'NewDataSet/My_x0020_profile')
WITH (
ID int,
Name nvarchar(50),
[Family name] nvarchar(50),
[Logo] image
) XmlUsers
WHERE
XmlUsers.ID NOT IN (SELECT ID FROM Users)
EXEC sp_xml_removedocument @hDoc
END --if (@data is not null)
GO
Вышеуказанная хранимая процедура вызывается следующим образом:
Public Sub XmlSaveData(table as DataTable)
Dim sb As System.Text.StringBuilder
Dim sw As IO.StringWriter
Try
If (table.GetChanges() IsNot Nothing) Then
For Each col As DataColumn In table.Columns
col.ColumnMapping = MappingType.Attribute
Next 'col As DataColumn In table.Columns
'NEW AND MODIFIED VALUES
If (table.GetChanges(DataRowState.Added Or DataRowState.Modified) IsNot Nothing) Then
sb = New System.Text.StringBuilder(1000)
sw = New IO.StringWriter(sb)
table.GetChanges(DataRowState.Added Or _
DataRowState.Modified).WriteXml(sw, XmlWriteMode.WriteSchema)
Debug.WriteLine(sb.ToString())
_cmdXmlSave.Parameters(0).Value = sb.ToString()
sw.Close()
End If '(table.GetChanges(DataRowState.Added Or DataRowState.Modified) IsNot Nothing)
OpenDBConnection(True)
_cmdXmlSave.ExecuteNonQuery()
End If '(table.GetChanges() IsNot Nothing)
Catch ex As Exception
...
Finally
CloseDBConnection(True)
End Try
End Sub
Поле Logo (тип image) не сохраняется.
ПОЧЕМУ? И как сделать так чтобы сохранялось?