Inserting with UpdateGrams

Hello.

I'm triing to use UpdateGrams to save data:

XML:

<updg:sync>
<updg:before>
</updg:before>
<updg:after>
<Image ProductList="11" ObjectType="3" MimeType="image/png" Content="AAAAAAAAAAQAC" EditDate="08/31/2006 14:27:16" AuthorID="5"></Image>
</updg:after>
</updg:sync>

XSD:

<?xml version="1.0" encoding="utf-8" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="ImageProduct"
parent="Images"
parent-key="ID"
child="ProductImage"
child-key="ImageID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="Image" sql:relation="Images" sql:key-fields="ID">
<xsd:complexType>
<xsd:attribute name="ID" sql:field="ID" type="xsd:integer" />
<xsd:attribute name="Content" sql:field="ImgData" type="xsd:base64Binary" />
<xsd:attribute name="MimeType" sql:field="ImgType" type="xsd:string" />
<xsd:attribute name="Name" sql:field="Name" type="xsd:string" />
<xsd:attribute name="AuthorID" sql:field="AuthorID" type="xsd:integer" />
<xsd:attribute name="StateID" sql:field="StateID" type="xsd:integer" />
<xsd:attribute name="EditDate" sql:field="EditDate" type="xsd:dateTime" />
<xsd:attribute name="ObjectType" sql:field="TypeID" type="xsd:integer" />
<xsd:attribute name="ProductList" sql:relation="ProductImage"
sql:field="ProductID"
sql:relationship="ImageProduct"
type="xsd:IDREFS" />
</xsd:complexType>
</xsd:element>
</xsd:schema>

Error:

HRESULT="0x80040e14" Description="Cannot insert explicit value for identity column in table 'Images' when IDENTITY_INSERT is set to OFF.

What is the matter? Why does SQL Server try insert ID into Images table?

It tries to insert it because you map it in your annotated schema and provide a value for it. Either avoid mapping it or set IDENTITY_INSERT to ON before you run the updategram.

Best regards

Michael

|||

Hello, Michael.

Sorry, but I don't provide value for ID column. There is no ID attribute in the XML I posted above. Actually ID is autoincrementable column.

|||

Your mapping schema includes a mapping of the ID field...

<xsd:attribute name="ID" sql:field="ID" type="xsd:integer" />

Thus if you have no value it will try to explictly insert a NULL value and it will fail. Try it without mapping the attribute.

Best regards

Michael

Inserting with UpdateGrams

Hello.

I'm triing to use UpdateGrams to save data:

XML:

<updg:sync>
<updg:before>
</updg:before>
<updg:after>
<Image ProductList="11" ObjectType="3" MimeType="image/png" Content="AAAAAAAAAAQAC" EditDate="08/31/2006 14:27:16" AuthorID="5"></Image>
</updg:after>
</updg:sync>

XSD:

<?xml version="1.0" encoding="utf-8" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="ImageProduct"
parent="Images"
parent-key="ID"
child="ProductImage"
child-key="ImageID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="Image" sql:relation="Images" sql:key-fields="ID">
<xsd:complexType>
<xsd:attribute name="ID" sql:field="ID" type="xsd:integer" />
<xsd:attribute name="Content" sql:field="ImgData" type="xsd:base64Binary" />
<xsd:attribute name="MimeType" sql:field="ImgType" type="xsd:string" />
<xsd:attribute name="Name" sql:field="Name" type="xsd:string" />
<xsd:attribute name="AuthorID" sql:field="AuthorID" type="xsd:integer" />
<xsd:attribute name="StateID" sql:field="StateID" type="xsd:integer" />
<xsd:attribute name="EditDate" sql:field="EditDate" type="xsd:dateTime" />
<xsd:attribute name="ObjectType" sql:field="TypeID" type="xsd:integer" />
<xsd:attribute name="ProductList" sql:relation="ProductImage"
sql:field="ProductID"
sql:relationship="ImageProduct"
type="xsd:IDREFS" />
</xsd:complexType>
</xsd:element>
</xsd:schema>

Error:

HRESULT="0x80040e14" Description="Cannot insert explicit value for identity column in table 'Images' when IDENTITY_INSERT is set to OFF.

What is the matter? Why does SQL Server try insert ID into Images table?

It tries to insert it because you map it in your annotated schema and provide a value for it. Either avoid mapping it or set IDENTITY_INSERT to ON before you run the updategram.

Best regards

Michael

|||

Hello, Michael.

Sorry, but I don't provide value for ID column. There is no ID attribute in the XML I posted above. Actually ID is autoincrementable column.

|||

Your mapping schema includes a mapping of the ID field...

<xsd:attribute name="ID" sql:field="ID" type="xsd:integer" />

Thus if you have no value it will try to explictly insert a NULL value and it will fail. Try it without mapping the attribute.

Best regards

Michael

Inserting with UpdateGrams

Hello.

I'm triing to use UpdateGrams to save data:

XML:

<updg:sync>
<updg:before>
</updg:before>
<updg:after>
<Image ProductList="11" ObjectType="3" MimeType="image/png" Content="AAAAAAAAAAQAC" EditDate="08/31/2006 14:27:16" AuthorID="5"></Image>
</updg:after>
</updg:sync>

XSD:

<?xml version="1.0" encoding="utf-8" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="ImageProduct"
parent="Images"
parent-key="ID"
child="ProductImage"
child-key="ImageID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="Image" sql:relation="Images" sql:key-fields="ID">
<xsd:complexType>
<xsd:attribute name="ID" sql:field="ID" type="xsd:integer" />
<xsd:attribute name="Content" sql:field="ImgData" type="xsd:base64Binary" />
<xsd:attribute name="MimeType" sql:field="ImgType" type="xsd:string" />
<xsd:attribute name="Name" sql:field="Name" type="xsd:string" />
<xsd:attribute name="AuthorID" sql:field="AuthorID" type="xsd:integer" />
<xsd:attribute name="StateID" sql:field="StateID" type="xsd:integer" />
<xsd:attribute name="EditDate" sql:field="EditDate" type="xsd:dateTime" />
<xsd:attribute name="ObjectType" sql:field="TypeID" type="xsd:integer" />
<xsd:attribute name="ProductList" sql:relation="ProductImage"
sql:field="ProductID"
sql:relationship="ImageProduct"
type="xsd:IDREFS" />
</xsd:complexType>
</xsd:element>
</xsd:schema>

Error:

HRESULT="0x80040e14" Description="Cannot insert explicit value for identity column in table 'Images' when IDENTITY_INSERT is set to OFF.

What is the matter? Why does SQL Server try insert ID into Images table?

It tries to insert it because you map it in your annotated schema and provide a value for it. Either avoid mapping it or set IDENTITY_INSERT to ON before you run the updategram.

Best regards

Michael

|||

Hello, Michael.

Sorry, but I don't provide value for ID column. There is no ID attribute in the XML I posted above. Actually ID is autoincrementable column.

|||

Your mapping schema includes a mapping of the ID field...

<xsd:attribute name="ID" sql:field="ID" type="xsd:integer" />

Thus if you have no value it will try to explictly insert a NULL value and it will fail. Try it without mapping the attribute.

Best regards

Michael

Inserting to SQL DB from a form using parameters

I have a web form for new members to fill out and save their info in a SQL database to be retrieved later. There will be like 20 elements in the form mostly textboxes. Is there a good example of how to read all the elements and have them INSERTed in a SQL Database? I imagine parameters and a SP would be the way to go. So if someone could provide me with some sample code or a good link.

Thanks in advance,Here's a sample calling a stored procedure with parameters and with a variety of data types.

SqlCommand cm= new SqlCommand();
cm.Connection= Connection;
cm.CommandType= CommandType.StoredProcedure;
cm.CommandText= "StoredProcedureName";
SqlParameter sp1 = cm.Parameters.Add( new SqlParameter("@.Field1",SqlDbType.Int,4)); sp1.Value = salesID;
SqlParameter sp2 = cm.Parameters.Add( new SqlParameter("@.Field2",SqlDbType.Int, 4)); sp2.Value = someIntValue;
SqlParameter sp3 = cm.Parameters.Add( new SqlParameter("@.Field3",SqlDbType.Money, 8));sp3.Value = someDoubleValue;
SqlParameter sp4 = cm.Parameters.Add( new SqlParameter("@.Field4",SqlDbType.Float, 8 )); sp4.Value = anotherDoubleValue;
SqlParameter sp5 = cm.Parameters.Add( new SqlParameter("@.Field5",SqlDbType.DateTime, 8)); sp5.Value = someDateTimeValue
Connection.Open();
cm.ExecuteNonQuery();
Connection.Close();