Problems with Data Sets in SQL Server 2005

Hello,

I am using existing code, which I am trying to convert from using MS Access to SQL Server 2005...

The data set works fine with MS Access database, however when executing with SQL Server 2005 as data source, it generates the following error:

"..The data types ntext and nvarchar are incompatible in the equal to operator..."

in this line:

count = adapter.Update(dataset);

Not sure what should I look for since data sets are new to me.. Where should I check to fix this problem? I have noticed that the table has two columns with nvarchar...

Please show the SQL string associated with the UpdateCommand in the data adapter.

In general, this means there is Text column you are comparing to an nvarchar column, and you cannot do that. Look at all tables, see what column is of type text, and see where that is used in the UpdateCommand. Perhaps the column is of type Text and it does not need to be a Text column.

|||

Hi,

Those data sets were generated by the designer in VS 2003. I do not even see any option to modify it. There are the following files:

DataSeteBayUser.xsd

DataSeteBayUser.Designer.cs

DataSeteBayUser.xsc

DataSeteBayUser.xss

I am not sure where to look for the SQL... I have opened DataSeteBayUser.xsd and it shows a diagram with all fields. But I do not even see option to configure it... I have noticed that all text fields are defined as System.Text. I have VS 2005 Professional Version...

I wonder if this dataset needs to be recreated in the designer, because it was converted from VS version 2003...

|||It shows as generated by MSDataSetGenerator... No idea what thsi tool is and where to download it...|||

rfurdzik:

It shows as generated by MSDataSetGenerator... No idea what thsi tool is and where to download it...

can you open the database look at the table. there must be at least one column 's type is ntext, and in the query's "WHERE" clause(either a stored procedure or dynamic sql), this columns must be used.

The solution is change the column type to nvarchar(n), make sure n is large enough.

Hope this help

|||

The problem is there is no query... The dataset was generated as a file using Visal Studio Table adapter wizard. It shows as data source in Visual Studio.

Also there is no field of type ntext in the database..

|||

This is the relevant code, which access the .xsd data source:

...

OleDbDataAdapter adapterTemplate = m_DataStore.DataAccessKits.DataAdapterUserAccountByID;OleDbConnection connection = m_DataStore.GetDbConnection();OleDbDataAdapter adapter =DBUtils.CloneOleDbAdapter(adapterTemplate, connection);

...

count = adapter.Update(dataset); --> THIS GENRATES ERROR

All other .xsd files (data source) were generated by wizard...

|||

OK, I have opened the xsd file in notepad and it is xml!

<?

xmlversion="1.0"standalone="yes"?>

<

xs:schemaid="DataSetUserAccount"targetNamespace="http://www.tempuri.org/DataSetUserAccount.xsd"xmlns:mstns="http://www.tempuri.org/DataSetUserAccount.xsd"xmlns="http://www.tempuri.org/DataSetUserAccount.xsd"xmlns:xs="http://www.w3.org/2001/XMLSchema"xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"attributeFormDefault="qualified"elementFormDefault="qualified">

<

xs:elementname="DataSetUserAccount"msdata:IsDataSet="true">

<

xs:complexType>

<

xs:choicemaxOccurs="unbounded">

<

xs:elementname="sdk_dbuser">

<

xs:complexType>

<

xs:sequence>

<

xs:elementname="ID"msdata:AutoIncrement="true"type="xs:int" />

<

xs:elementname="eBayUser"type="xs:string"minOccurs="0" />

<

xs:elementname="EnvironmentID"type="xs:int"minOccurs="0" />

<

xs:elementname="ApiToken"type="xs:string"minOccurs="0" />

<

xs:elementname="AuthType"type="xs:int"minOccurs="0" />

<

xs:elementname="Description"type="xs:string"minOccurs="0" />

<

xs:elementname="eBayPassword"type="xs:string"minOccurs="0" />

<

xs:elementname="eBayUserDBID"type="xs:int"minOccurs="0" />

<

xs:elementname="TokenCreateDate"type="xs:dateTime"minOccurs="0" />

<

xs:elementname="TokenExpirationDate"type="xs:dateTime"minOccurs="0" />

<

xs:elementname="TokenSecret"type="xs:string"minOccurs="0" />

<

xs:elementname="TokenSecretCreateDate"type="xs:dateTime"minOccurs="0" />

</

xs:sequence>

</

xs:complexType>

</

xs:element>

</

xs:choice>

</

xs:complexType>

<

xs:uniquename="Constraint1"msdata:PrimaryKey="true">

<

xs:selectorxpath=".//mstns:sdk_dbuser" />

<

xs:fieldxpath="mstns:ID" />

</

xs:unique>

</

xs:element>

</

xs:schema>|||It seems to me like the "xs:string" is translated by wizard generated code to ntext instead of nvarchar. In the table there is nvarchar, so maybe taht is why it does not work...|||

I have found there si a property called Adapter.UpdateCommand.Text:

"UPDATE sdk_dbuser SET EnvironmentID = ?, Description = ?, eBayUser = ?, eBayPassword = ?, eBayUserDBID = ?, ApiToken = ?, TokenCreateDate = ?, TokenExpirationDate = ?, TokenSecret = ?, TokenSecretCreateDate = ?, AuthType = ? WHERE (ID = ?) AND (ApiToken = ? OR ? IS NULL AND ApiToken IS NULL) AND (Description = ? OR ? IS NULL AND Description IS NULL) AND (TokenCreateDate = ? OR ? IS NULL AND TokenCreateDate IS NULL) AND (TokenExpirationDate = ? OR ? IS NULL AND TokenExpirationDate IS NULL) AND (TokenSecret = ? OR ? IS NULL AND TokenSecret IS NULL) AND (TokenSecretCreateDate = ? OR ? IS NULL AND TokenSecretCreateDate IS NULL) AND (eBayPassword = ? OR ? IS NULL AND eBayPassword IS NULL) AND (eBayUser = ?)"

The actual update is done here:

count = adapter.Update(dataset);

|||Hi, I changed all ntext to nvarchar(MAX), not sure what MAX is, hopefully it is large enough..|||Seems to be working, but got other problems now..