|
Using SQLXML, Bulk Insert XML with IDENTITY Column ...
I have a program that will insert xml data into a table using the SQLXML Bulk Load COM Object. The bulk load is successful when I supply the RecordId in the xml. When I add an IDENTITY column to the table then the bulk load fails with the following error:
[Cannot insert the value NULL into column 'RecordId', table 'Alphanumericdata.dbo.MacgowanTestCust'; column does not allow nulls. INSERT fails.]
From the following article, XML Bulk Load ignores elements and attributes that are not mapped (either because they are not described in the schema, or because they are annotated in the XSD schema with sql:mapped="false"). All unmapped data goes into the overflow column, if such a column is specified by using sql:overflow-field.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/bulkload_9w9w.asp
I have attempted using sql:mapped="false" in the schema file and not using it .. both fail with the “nulls not allowed” error.
I have also set the KeepIdentity(true) to my pISQLXMLBulkLoad object.
Below is the table, xml, xsd and code ...
Any comments or answers are appreciated.
Thanks,
Chris
<br />
char progID[] = "SQLXMLBulkLoad.SQLXMLBulkload.3.0";<br />
CLSID clsid;<br />
wchar_t wide[80];<br />
mbstowcs(wide, progID, 80);<br />
CLSIDFromProgID(wide, &clsid);<br />
ISQLXMLBulkLoad* pISQLXMLBulkLoad = NULL;<br />
if(SUCCEEDED(CoCreateInstance(clsid, NULL, CLSCTX_ALL, IID_ISQLXMLBulkLoad, (void**)&pISQLXMLBulkLoad)))<br />
{<br />
hResult = pISQLXMLBulkLoad->put_ConnectionString(bstrConnect);<br />
hResult = pISQLXMLBulkLoad->put_ErrorLogFile(bstrXmlErrorLogFile);<br />
hResult = pISQLXMLBulkLoad->put_KeepIdentity((bool)TRUE);<br />
hResult = pISQLXMLBulkLoad->Execute(bstrXmlSchemaFile, vXmlDataFile);<br />
}<br />
<br />
<br />
<ROOT><br />
<Customers><br />
<RecordId>1</RecordId><br />
<CustomerID>1111</CustomerID><br />
<CompanyName>Sean Chai</CompanyName><br />
<City>NY</City><br />
</Customers><br />
<Customers><br />
<RecordId>2</RecordId><br />
<CustomerID>1112</CustomerID><br />
<CompanyName>Tom Johnston</CompanyName><br />
<City>LA</City><br />
</Customers><br />
<Customers><br />
<RecordId>3</RecordId><br />
<CustomerID>1113</CustomerID><br />
<CompanyName>Institute of Art</CompanyName><br />
</Customers><br />
</ROOT><br />
<br />
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"<br />
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"><br />
<br />
<xsd:element name="Customers" sql:relation="MacgowanTestCust" ><br />
<xsd:complexType><br />
<xsd:sequence><br />
<xsd:element name="RecordId" type="xsd:integer" sql:field="RecordId" /><br />
<xsd:element name="CustomerID" type="xsd:integer" sql:field="CustomerID" /><br />
<xsd:element name="CompanyName" type="xsd:string" sql:field="CompanyName" /><br />
<xsd:element name="City" type="xsd:string" sql:field="City" /><br />
</xsd:sequence><br />
</xsd:complexType><br />
</xsd:element><br />
</xsd:schema><br />
<br />
<ROOT><br />
<Customers><br />
<CustomerID>1111</CustomerID><br />
<CompanyName>Sean Chai</CompanyName><br />
<City>NY</City><br />
</Customers><br />
<Customers><br />
<CustomerID>1112</CustomerID><br />
<CompanyName>Tom Johnston</CompanyName><br />
<City>LA</City><br />
</Customers><br />
<Customers><br />
<CustomerID>1113</CustomerID><br />
<CompanyName>Institute of Art</CompanyName><br />
</Customers><br />
</ROOT><br />
<br />
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"<br />
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"><br />
<br />
<xsd:element name="Customers" sql:relation="MacgowanTestCust" ><br />
<xsd:complexType><br />
<xsd:sequence><br />
<xsd:element name="CustomerID" type="xsd:integer" sql:field="CustomerID" /><br />
<xsd:element name="CompanyName" type="xsd:string" sql:field="CompanyName" /><br />
<xsd:element name="City" type="xsd:string" sql:field="City" /><br />
</xsd:sequence><br />
</xsd:complexType><br />
</xsd:element><br />
</xsd:schema><br />
<br />
<br />
CREATE TABLE [MacgowanTestCust] (<br />
[RecordId] [int] IDENTITY (1, 1) NOT NULL ,<br />
[CustomerID] [int] NOT NULL ,<br />
[DataSourceId] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MacgowanTestCust_DataSourceId] DEFAULT ('OH'),<br />
[CompanyName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />
[City] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />
PRIMARY KEY CLUSTERED<br />
(<br />
[RecordId]<br />
) ON [PRIMARY]<br />
) ON [PRIMARY]<br />
GO<br />
<br />
<br />
<?xml version="1.0"?><Error><Record><HResult>0x80004005</HResult><SQLState>01000</SQLState><NativeError></NativeError><ErrorState>1</ErrorState><Severity>0</Severity><Source>Microsoft OLE DB Provider for SQL Server</Source><Description><![CDATA[The statement has been terminated.]]></Description></Record><Record><HResult>0x80004005</HResult><SQLState>23000</SQLState><NativeError></NativeError><ErrorState>2</ErrorState><Severity>16</Severity><Source>Microsoft OLE DB Provider for SQL Server</Source><Description><![CDATA[Cannot insert the value NULL into column 'RecordId', table 'Alphanumericdata.dbo.MacgowanTestCust'; column does not allow nulls. INSERT fails.]]></Description></Record></Error><br />
<br />
<br />
<br />
<br />
|
|
|
|
|
The xml again ...
///////////////////////////////////////////////////
// The code
char progID[] = "SQLXMLBulkLoad.SQLXMLBulkload.3.0";
CLSID clsid;
wchar_t wide[80];
mbstowcs(wide, progID, 80);
CLSIDFromProgID(wide, &clsid);
ISQLXMLBulkLoad* pISQLXMLBulkLoad = NULL;
if(SUCCEEDED(CoCreateInstance(clsid, NULL, CLSCTX_ALL, IID_ISQLXMLBulkLoad, (void**)&pISQLXMLBulkLoad)))
{
hResult = pISQLXMLBulkLoad->put_ConnectionString(bstrConnect);
hResult = pISQLXMLBulkLoad->put_ErrorLogFile(bstrXmlErrorLogFile);
hResult = pISQLXMLBulkLoad->put_KeepIdentity((bool)TRUE);
hResult = pISQLXMLBulkLoad->Execute(bstrXmlSchemaFile, vXmlDataFile);
}
///////////////////////////////////////////////////
// xml data (successful) RecordId is included
<ROOT>
<Customers>
<RecordId>1</RecordId>
<CustomerID>1111</CustomerID>
<CompanyName>Sean Chai</CompanyName>
<City>NY</City>
</Customers>
<Customers>
<RecordId>2</RecordId>
<CustomerID>1112</CustomerID>
<CompanyName>Tom Johnston</CompanyName>
<City>LA</City>
</Customers>
<Customers>
<RecordId>3</RecordId>
<CustomerID>1113</CustomerID>
<CompanyName>Institute of Art</CompanyName>
</Customers>
</ROOT>
///////////////////////////////////////////////////
// xsd schema file (successful) RecordId is included
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Customers" sql:relation="MacgowanTestCust" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="RecordId" type="xsd:integer" sql:field="RecordId" />
<xsd:element name="CustomerID" type="xsd:integer" sql:field="CustomerID" />
<xsd:element name="CompanyName" type="xsd:string" sql:field="CompanyName" />
<xsd:element name="City" type="xsd:string" sql:field="City" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
///////////////////////////////////////////////////
// xml data (fails) attempting to use identity column
<ROOT>
<Customers>
<CustomerID>1111</CustomerID>
<CompanyName>Sean Chai</CompanyName>
<City>NY</City>
</Customers>
<Customers>
<CustomerID>1112</CustomerID>
<CompanyName>Tom Johnston</CompanyName>
<City>LA</City>
</Customers>
<Customers>
<CustomerID>1113</CustomerID>
<CompanyName>Institute of Art</CompanyName>
</Customers>
</ROOT>
///////////////////////////////////////////////////
// xsd schema file (fails) attempting to use identity column
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Customers" sql:relation="MacgowanTestCust" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CustomerID" type="xsd:integer" sql:field="CustomerID" />
<xsd:element name="CompanyName" type="xsd:string" sql:field="CompanyName" />
<xsd:element name="City" type="xsd:string" sql:field="City" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
///////////////////////////////////////////////////
// table
CREATE TABLE [MacgowanTestCust] (
[RecordId] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [int] NOT NULL ,
[DataSourceId] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MacgowanTestCust_DataSourceId] DEFAULT ('OH'),
[CompanyName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
PRIMARY KEY CLUSTERED
(
[RecordId]
) ON [PRIMARY]
) ON [PRIMARY]
GO
///////////////////////////////////////////////////
// error message
<?xml version="1.0"?><Error><Record><HResult>0x80004005</HResult><SQLState>01000</SQLState><NativeError></NativeError><ErrorState>1</ErrorState><Severity>0</Severity><Source>Microsoft OLE DB Provider for SQL Server</Source><Description><![CDATA[The statement has been terminated.]]></Description></Record><Record><HResult>0x80004005</HResult><SQLState>23000</SQLState><NativeError></NativeError><ErrorState>2</ErrorState><Severity>16</Severity><Source>Microsoft OLE DB Provider for SQL Server</Source><Description><![CDATA[Cannot insert the value NULL into column 'RecordId', table 'Alphanumericdata.dbo.MacgowanTestCust'; column does not allow nulls. INSERT fails.]]></Description></Record></Error>
|
|
|
|
|
|
|
My application is simple dialog based application. I derived the OnOk(), OnCancel() functions in CMyDialog class. I wrote nothing in those functions. But after run this application I could not able to close the dialog. Why it is happeneing so. How can I solve this problem? Here my view is not to close the dialog when I pressed the enter button or escape button. How it is possiable in different ways. Please help me in this.
Nice talking to you. 
-- modified at 14:00 Wednesday 7th December, 2005
|
|
|
|
|
on your OnClose and on OnOK functions, make sure you add
CDialog::OnClose() or CDialog::OnOK() after anything you do.
If you override them and leave them empty of course they will do nothing 
|
|
|
|
|
Yes. Just I removed the OnCancel derivation. I feel my program as I expected. May be some other solutions too to handle the OnOk and OnCancel also. I think some PreTranslateMessage function is useful. I dont know exactly about this. If any let me know than I am very thankful to him.
Nice talking to you.
|
|
|
|
|
G Haranadh wrote: May be some other solutions too to handle the OnOk and OnCancel also.
What is it that you are wanting to handle?
"Take only what you need and leave the land as you found it." - Native American Proverb
|
|
|
|
|
My intension is I would like to handle Escape button and Enter key in different ways.
Thanks for giving reply
Nice talking to you.
|
|
|
|
|
G Haranadh wrote: My application is simple dialog based application. I derived the OnOk(), OnCancel() functions in CMyDialog class. I wrote nothing in those functions. But after run this application I could not able to close the dialog. Why it is happeneing so. How can I solve this problem? Here my view is not to close the dialog when I pressed the enter button or escape button. How it is possiable in different ways. Please help me in this.
See :
Dialog windows and WM_CLOSE[^]
|
|
|
|
|
![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif) Thank you for helping us through codeproject. Thanks alot. Have a nice day I am very glad to talk to you
Nice talking to you.
|
|
|
|
|
Hi,
When I used Visual Studio 6, life was easy, I would edit the main application icon and when I created a desktop shortcut for it, there is was. The same for the 16X16 icon, when I created it, you could see it in the windows explorer.
However, when working with Visual studio .NET 2003, my Application's Icon will not change and stays the default MFC icon.
Any help will be great.
shay
|
|
|
|
|
in fact, a .ico file contains several icons, varying from the size and/or the number of colors.
when you edit an icon with the icon editor within Visual Studio, you should verify that all the devices (that's how they're called) are like you want...
(search the menus for that ; i don't remember exactly where).
TOXCCT >>> GEII power
[toxcct][VisualCalc 2.20] | soon : [VisualCalc 3.0]
|
|
|
|
|
Well, you set it from the Image menu, I just found out.
Thanks.
|
|
|
|
|
Hello,
I don't know why this is proving difficult. It is obviously a very common task. I have an SDI application that is hosting an ActiveX control. The ActiveX control has some events that it fires. I want to handle these events in the host application. I cannot figure out how to hook it up.
The ActiveX control is not added at design time. It is dynamically created.
Thoughts?
Thanks!
-Ian
|
|
|
|
|
Ian Bowler wrote: The ActiveX control is not added at design time. It is dynamically created.
You still have to implement the event sink which means that you need to know how the sink interface is defined.
The easiest way is to add the control to the project at design time and use ClassWizard to generate the event sink.
When you 'hook it up' you have to use IConnectionPointContainer and IConnectionPoint interfaces and in the end call IConnectionPoint::Advise and provide an interface pointer to the sink.
--
Roger
It's supposed to be hard, otherwise anybody could do it!
|
|
|
|
|
Thanks. I'll look into this method.
still... this is extremely common right? It seems like there should be an easier way. Something through class wizard..?
|
|
|
|
|
I want to create a Window witch is always on bottom z order, in fact this window is my "desktop" with some "buttons" , and these buttons launch some other applications. But the "desktop window" can hide the launched applications
Some body could help me
Thanks a lot in advance
Christian
|
|
|
|
|
Look at the Windows MFC site for the description of the SetWindowPos() function, you can understand it from there, I am sure.
|
|
|
|
|
How do you change the little icon picture in the title bar of a CDialog window?
Heather
|
|
|
|
|
you have to change the IDR_MAINFRAME resource with the new icon (still in OnInitDialog())...
TOXCCT >>> GEII power
[toxcct][VisualCalc 2.20] | soon : [VisualCalc 3.0]
|
|
|
|
|
SetIcon()
"You're obviously a superstar." - Christian Graus about me - 12 Feb '03
"Obviously ??? You're definitely a superstar!!!" - mYkel - 21 Jun '04
"There's not enough blatant self-congratulatory backslapping in the world today..." - HumblePie - 21 Jun '05
Within you lies the power for good - Use it!
|
|
|
|
|
True, a secondary thread might not even have a message pump.
Regards
Senthil
_____________________________
My Blog | My Articles | WinMacro
|
|
|
|
|
How do you dynamically change the caption title in .NET for a CDialog window?
Heather Heiman
|
|
|
|
|
CDialog is not a .NET class, but a MFC one.
i so consider you're using MFC.
you simply have to do this :
this->SetWindowText("new caption");
in a function of your dialog class...
TOXCCT >>> GEII power
[toxcct][VisualCalc 2.20] | soon : [VisualCalc 3.0]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
