Error during DataAdapter update: "The text, ntext, and image data types cannot be compared or sorted

  • Thread starter Thread starter Michael Lang
  • Start date Start date
M

Michael Lang

I am using the standard "pubs" database.

Errror.ToString() --

"System.Data.SqlClient.SqlException: The text, ntext, and image data types
cannot be compared or sorted, except when using IS NULL or LIKE
operator.\r\n at System.Data.Common.DbDataAdapter.Update(DataRow[]
dataRows, DataTableMapping tableMapping)\r\n at
System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)\r\n at System.Data.Common.DbDataAdapter.Update(DataSet
dataSet)\r\n at Pubs.Data.titlesDB.Update() in
e:\\projects\\vs7\\pubsexample\\data\\titlesdb.cs:line 194"

This Occurs when I update the database...
DataAdapter _da; DataSet _ds;
....
_da.Update(_ds);

When I check the database, nothing has been updated. So the "compare or
sort" operation is happening before the update.

The DataSet has 1 "Modified" record. I changed a single field value on
field "6" below. These are the field values from the locals window for the
"Modified" DataRow.

[0] "BU1032" string
[1] "The Busy Executive's Database Guide" string
[2] "business " string
[3] "1389" string
+ [4] 19.99 System.Decimal
+ [5] 5000 System.Decimal
+ [6] {11} System.Int32
+ [7] {4095} System.Int32
[8] "An overview of available database systems with emphasis on common
business applications. Illustrated." string
+ [9] {6/12/1991} System.DateTime

Some other records have null field values, but they were null before the
update, and they are not modified records. here is the locals window values
for one of those DataRows

[0] "PC9999" string
[1] "Net Etiquette" string
[2] "popular_comp" string
[3] "1389" string
+ [4] {System.DBNull} System.DBNull
+ [5] {System.DBNull} System.DBNull
+ [6] {System.DBNull} System.DBNull
+ [7] {System.DBNull} System.DBNull
[8] "A must-read for computer conferencing." string
+ [9] {7/25/2003} System.DateTime

What is trying to "compare or sort" the records as the error states? The
DataAdapter?

Michael Lang, MCSD
 
If an SqlException is generated, I'm assuming that means that the error
occured in the SQL Server engine. This probably means that your stored
procedure code or SQL update statement is incorrect. I wouldn't know unless
I saw the SQL that should be executed through the call to Update on the
DataAdapter.

Hammad
 
As requested here is my code to create the DataAdapter and UpdateCommand.
===========================================================
//Code to create DataAdapter and Select Command...
{
...
IDbCommand cmd = _context.DbContext.DbTemplate.Command(
"SELECT title_id, title, type, pub_id, price, advance, royalty, ytd_sales,
notes, pubdate FROM titles",
_context.DbContext.Connection);
_da = _context.DbContext.DbTemplate.DataAdapter(cmd);
_da.InsertCommand = GetInsertCommand();
_da.UpdateCommand = GetUpdateCommand();
_da.DeleteCommand = GetDeleteCommand();
...
}

private IDbCommand GetUpdateCommand()
{
//Define the SqlCommand
IDbCommand cmd = _context.DbContext.DbTemplate.Command();
cmd.Connection = _context.DbContext.Connection;
cmd.CommandText = _context.DbContext.DbTemplate.FormatCommand("UPDATE
titles SET "
+ "title = @title"+ ", "
+ "type = @type"+ ", "
+ "pub_id = @pub_id"+ ", "
+ "price = @price"+ ", "
+ "advance = @advance"+ ", "
+ "royalty = @royalty"+ ", "
+ "ytd_sales = @ytd_sales"+ ", "
+ "notes = @notes"+ ", "
+ "pubdate = @pubdate"+ " WHERE title_id = @title_id");

IDbDataParameter prm0 = _context.DbContext.DbTemplate.DataParameter(
"@title_id" , DbType.String);
prm0.SourceColumn = "title_id";
cmd.Parameters.Add(prm0);
IDbDataParameter prm1 = _context.DbContext.DbTemplate.DataParameter(
"@title" , DbType.String);
prm1.SourceColumn = "title";
cmd.Parameters.Add(prm1);
IDbDataParameter prm2 = _context.DbContext.DbTemplate.DataParameter(
"@type" , DbType.StringFixedLength);
prm2.SourceColumn = "type";
cmd.Parameters.Add(prm2);
IDbDataParameter prm3 = _context.DbContext.DbTemplate.DataParameter(
"@pub_id" , DbType.StringFixedLength);
prm3.SourceColumn = "pub_id";
cmd.Parameters.Add(prm3);
IDbDataParameter prm4 = _context.DbContext.DbTemplate.DataParameter(
"@price" , DbType.Currency);
prm4.SourceColumn = "price";
cmd.Parameters.Add(prm4);
IDbDataParameter prm5 = _context.DbContext.DbTemplate.DataParameter(
"@advance" , DbType.Currency);
prm5.SourceColumn = "advance";
cmd.Parameters.Add(prm5);
IDbDataParameter prm6 = _context.DbContext.DbTemplate.DataParameter(
"@royalty" , DbType.Int32);
prm6.SourceColumn = "royalty";
cmd.Parameters.Add(prm6);
IDbDataParameter prm7 = _context.DbContext.DbTemplate.DataParameter(
"@ytd_sales" , DbType.Int32);
prm7.SourceColumn = "ytd_sales";
cmd.Parameters.Add(prm7);
IDbDataParameter prm8 = _context.DbContext.DbTemplate.DataParameter(
"@notes" , DbType.String);
prm8.SourceColumn = "notes";
cmd.Parameters.Add(prm8);
IDbDataParameter prm9 = _context.DbContext.DbTemplate.DataParameter(
"@pubdate" , DbType.Date);
prm9.SourceColumn = "pubdate";
cmd.Parameters.Add(prm9);
return cmd;
}
===========================================================

This is using the example pubs database. Here is the definition of the
'titles' table...
Name DataType Size Nulls
title_id tid 6 No
title nvarchar 80 No
type nchar 12 No
pub_id nchar 4 Yes
price money 8 Yes
advance money 8 Yes
royalty int 4 Yes
ytd_sales int 4 Yes
notes nvarchar 200 Yes
pubdate datetime 8 No

Michael Lang said:
I am using the standard "pubs" database.

Errror.ToString() --

"System.Data.SqlClient.SqlException: The text, ntext, and image data types
cannot be compared or sorted, except when using IS NULL or LIKE
operator.\r\n at System.Data.Common.DbDataAdapter.Update(DataRow[]
dataRows, DataTableMapping tableMapping)\r\n at
System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)\r\n at System.Data.Common.DbDataAdapter.Update(DataSet
dataSet)\r\n at Pubs.Data.titlesDB.Update() in
e:\\projects\\vs7\\pubsexample\\data\\titlesdb.cs:line 194"

This Occurs when I update the database...
DataAdapter _da; DataSet _ds;
...
_da.Update(_ds);

When I check the database, nothing has been updated. So the "compare or
sort" operation is happening before the update.

The DataSet has 1 "Modified" record. I changed a single field value on
field "6" below. These are the field values from the locals window for the
"Modified" DataRow.

[0] "BU1032" string
[1] "The Busy Executive's Database Guide" string
[2] "business " string
[3] "1389" string
+ [4] 19.99 System.Decimal
+ [5] 5000 System.Decimal
+ [6] {11} System.Int32
+ [7] {4095} System.Int32
[8] "An overview of available database systems with emphasis on common
business applications. Illustrated." string
+ [9] {6/12/1991} System.DateTime

Some other records have null field values, but they were null before the
update, and they are not modified records. here is the locals window values
for one of those DataRows

[0] "PC9999" string
[1] "Net Etiquette" string
[2] "popular_comp" string
[3] "1389" string
+ [4] {System.DBNull} System.DBNull
+ [5] {System.DBNull} System.DBNull
+ [6] {System.DBNull} System.DBNull
+ [7] {System.DBNull} System.DBNull
[8] "A must-read for computer conferencing." string
+ [9] {7/25/2003} System.DateTime

What is trying to "compare or sort" the records as the error states? The
DataAdapter?

Michael Lang, MCSD
Click to expand...
 
I get the same exact error message when deleting a record also. However the
Insert does work!

private IDbCommand GetDeleteCommand()
{
IDbCommand cmd = _context.DbContext.DbTemplate.Command();
cmd.Connection = _context.DbContext.Connection;
cmd.CommandText = _context.DbContext.DbTemplate.FormatCommand(
"DELETE FROM titles WHERE title_id = @title_id");
IDbDataParameter prm1 =
_context.DbContext.DbTemplate.DataParameter("@title_id",
DbType.String);
prm1.SourceColumn = "title_id";
cmd.Parameters.Add(prm1);
return cmd;
}

Michael Lang said:
As requested here is my code to create the DataAdapter and UpdateCommand.
===========================================================
//Code to create DataAdapter and Select Command...
{
...
IDbCommand cmd = _context.DbContext.DbTemplate.Command(
"SELECT title_id, title, type, pub_id, price, advance, royalty, ytd_sales,
notes, pubdate FROM titles",
_context.DbContext.Connection);
_da = _context.DbContext.DbTemplate.DataAdapter(cmd);
_da.InsertCommand = GetInsertCommand();
_da.UpdateCommand = GetUpdateCommand();
_da.DeleteCommand = GetDeleteCommand();
...
}

private IDbCommand GetUpdateCommand()
{
//Define the SqlCommand
IDbCommand cmd = _context.DbContext.DbTemplate.Command();
cmd.Connection = _context.DbContext.Connection;
cmd.CommandText = _context.DbContext.DbTemplate.FormatCommand("UPDATE
titles SET "
+ "title = @title"+ ", "
+ "type = @type"+ ", "
+ "pub_id = @pub_id"+ ", "
+ "price = @price"+ ", "
+ "advance = @advance"+ ", "
+ "royalty = @royalty"+ ", "
+ "ytd_sales = @ytd_sales"+ ", "
+ "notes = @notes"+ ", "
+ "pubdate = @pubdate"+ " WHERE title_id = @title_id");

IDbDataParameter prm0 = _context.DbContext.DbTemplate.DataParameter(
"@title_id" , DbType.String);
prm0.SourceColumn = "title_id";
cmd.Parameters.Add(prm0);
IDbDataParameter prm1 = _context.DbContext.DbTemplate.DataParameter(
"@title" , DbType.String);
prm1.SourceColumn = "title";
cmd.Parameters.Add(prm1);
IDbDataParameter prm2 = _context.DbContext.DbTemplate.DataParameter(
"@type" , DbType.StringFixedLength);
prm2.SourceColumn = "type";
cmd.Parameters.Add(prm2);
IDbDataParameter prm3 = _context.DbContext.DbTemplate.DataParameter(
"@pub_id" , DbType.StringFixedLength);
prm3.SourceColumn = "pub_id";
cmd.Parameters.Add(prm3);
IDbDataParameter prm4 = _context.DbContext.DbTemplate.DataParameter(
"@price" , DbType.Currency);
prm4.SourceColumn = "price";
cmd.Parameters.Add(prm4);
IDbDataParameter prm5 = _context.DbContext.DbTemplate.DataParameter(
"@advance" , DbType.Currency);
prm5.SourceColumn = "advance";
cmd.Parameters.Add(prm5);
IDbDataParameter prm6 = _context.DbContext.DbTemplate.DataParameter(
"@royalty" , DbType.Int32);
prm6.SourceColumn = "royalty";
cmd.Parameters.Add(prm6);
IDbDataParameter prm7 = _context.DbContext.DbTemplate.DataParameter(
"@ytd_sales" , DbType.Int32);
prm7.SourceColumn = "ytd_sales";
cmd.Parameters.Add(prm7);
IDbDataParameter prm8 = _context.DbContext.DbTemplate.DataParameter(
"@notes" , DbType.String);
prm8.SourceColumn = "notes";
cmd.Parameters.Add(prm8);
IDbDataParameter prm9 = _context.DbContext.DbTemplate.DataParameter(
"@pubdate" , DbType.Date);
prm9.SourceColumn = "pubdate";
cmd.Parameters.Add(prm9);
return cmd;
}
===========================================================

This is using the example pubs database. Here is the definition of the
'titles' table...
Name DataType Size Nulls
title_id tid 6 No
title nvarchar 80 No
type nchar 12 No
pub_id nchar 4 Yes
price money 8 Yes
advance money 8 Yes
royalty int 4 Yes
ytd_sales int 4 Yes
notes nvarchar 200 Yes
pubdate datetime 8 No

Michael Lang said:
I am using the standard "pubs" database.

Errror.ToString() --

"System.Data.SqlClient.SqlException: The text, ntext, and image data types
cannot be compared or sorted, except when using IS NULL or LIKE
operator.\r\n at System.Data.Common.DbDataAdapter.Update(DataRow[]
dataRows, DataTableMapping tableMapping)\r\n at
System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)\r\n at System.Data.Common.DbDataAdapter.Update(DataSet
dataSet)\r\n at Pubs.Data.titlesDB.Update() in
e:\\projects\\vs7\\pubsexample\\data\\titlesdb.cs:line 194"

This Occurs when I update the database...
DataAdapter _da; DataSet _ds;
...
_da.Update(_ds);

When I check the database, nothing has been updated. So the "compare or
sort" operation is happening before the update.

The DataSet has 1 "Modified" record. I changed a single field value on
field "6" below. These are the field values from the locals window for the
"Modified" DataRow.

[0] "BU1032" string
[1] "The Busy Executive's Database Guide" string
[2] "business " string
[3] "1389" string
+ [4] 19.99 System.Decimal
+ [5] 5000 System.Decimal
+ [6] {11} System.Int32
+ [7] {4095} System.Int32
[8] "An overview of available database systems with emphasis on common
business applications. Illustrated." string
+ [9] {6/12/1991} System.DateTime

Some other records have null field values, but they were null before the
update, and they are not modified records. here is the locals window values
for one of those DataRows

[0] "PC9999" string
[1] "Net Etiquette" string
[2] "popular_comp" string
[3] "1389" string
+ [4] {System.DBNull} System.DBNull
+ [5] {System.DBNull} System.DBNull
+ [6] {System.DBNull} System.DBNull
+ [7] {System.DBNull} System.DBNull
[8] "A must-read for computer conferencing." string
+ [9] {7/25/2003} System.DateTime

What is trying to "compare or sort" the records as the error states? The
DataAdapter?

Michael Lang, MCSD
Click to expand...
Click to expand...
 
AH!

I'm using the wrong DbType for the title_id parameter. I tried
DbType.AnsiString instead of DbType.String and it worked (both commands)

so sql "nvarchar" maps into DbType.AnsiString?

DbType.AnsiString = A variable-length stream of non-Unicode characters
ranging between 1 and 8,000 characters.
DbType.String = A type representing Unicode character strings.
DbType.StringFixedLength = A fixed-length stream of Unicode characters.

SqlDbType.NVarChar = A variable-length stream of Unicode characters ranging
between 1 and 4,000 characters. Note Implicit conversion fails if the
string is greater than 4,000 characters. Explicitly set the object when
working with strings longer than 4,000 characters.

How does this work? AnsiString is non-Unicode, and NVarChar is Unicode.

Also DbType.StringFixedLenth works. It doesn't make sense to me either.
How des fixed length map to variable length? However, I think I'll use that
instead to allow unicode characters.

Any other suggestions?

Michael Lang, MCSD

Michael Lang said:
I get the same exact error message when deleting a record also. However the
Insert does work!

private IDbCommand GetDeleteCommand()
{
IDbCommand cmd = _context.DbContext.DbTemplate.Command();
cmd.Connection = _context.DbContext.Connection;
cmd.CommandText = _context.DbContext.DbTemplate.FormatCommand(
"DELETE FROM titles WHERE title_id = @title_id");
IDbDataParameter prm1 =
_context.DbContext.DbTemplate.DataParameter("@title_id",
DbType.String);
prm1.SourceColumn = "title_id";
cmd.Parameters.Add(prm1);
return cmd;
}

Michael Lang said:
As requested here is my code to create the DataAdapter and UpdateCommand.
===========================================================
//Code to create DataAdapter and Select Command...
{
...
IDbCommand cmd = _context.DbContext.DbTemplate.Command(
"SELECT title_id, title, type, pub_id, price, advance, royalty, ytd_sales,
notes, pubdate FROM titles",
_context.DbContext.Connection);
_da = _context.DbContext.DbTemplate.DataAdapter(cmd);
_da.InsertCommand = GetInsertCommand();
_da.UpdateCommand = GetUpdateCommand();
_da.DeleteCommand = GetDeleteCommand();
...
}

private IDbCommand GetUpdateCommand()
{
//Define the SqlCommand
IDbCommand cmd = _context.DbContext.DbTemplate.Command();
cmd.Connection = _context.DbContext.Connection;
cmd.CommandText = _context.DbContext.DbTemplate.FormatCommand("UPDATE
titles SET "
+ "title = @title"+ ", "
+ "type = @type"+ ", "
+ "pub_id = @pub_id"+ ", "
+ "price = @price"+ ", "
+ "advance = @advance"+ ", "
+ "royalty = @royalty"+ ", "
+ "ytd_sales = @ytd_sales"+ ", "
+ "notes = @notes"+ ", "
+ "pubdate = @pubdate"+ " WHERE title_id = @title_id");

IDbDataParameter prm0 = _context.DbContext.DbTemplate.DataParameter(
"@title_id" , DbType.String);
prm0.SourceColumn = "title_id";
cmd.Parameters.Add(prm0);
IDbDataParameter prm1 = _context.DbContext.DbTemplate.DataParameter(
"@title" , DbType.String);
prm1.SourceColumn = "title";
cmd.Parameters.Add(prm1);
IDbDataParameter prm2 = _context.DbContext.DbTemplate.DataParameter(
"@type" , DbType.StringFixedLength);
prm2.SourceColumn = "type";
cmd.Parameters.Add(prm2);
IDbDataParameter prm3 = _context.DbContext.DbTemplate.DataParameter(
"@pub_id" , DbType.StringFixedLength);
prm3.SourceColumn = "pub_id";
cmd.Parameters.Add(prm3);
IDbDataParameter prm4 = _context.DbContext.DbTemplate.DataParameter(
"@price" , DbType.Currency);
prm4.SourceColumn = "price";
cmd.Parameters.Add(prm4);
IDbDataParameter prm5 = _context.DbContext.DbTemplate.DataParameter(
"@advance" , DbType.Currency);
prm5.SourceColumn = "advance";
cmd.Parameters.Add(prm5);
IDbDataParameter prm6 = _context.DbContext.DbTemplate.DataParameter(
"@royalty" , DbType.Int32);
prm6.SourceColumn = "royalty";
cmd.Parameters.Add(prm6);
IDbDataParameter prm7 = _context.DbContext.DbTemplate.DataParameter(
"@ytd_sales" , DbType.Int32);
prm7.SourceColumn = "ytd_sales";
cmd.Parameters.Add(prm7);
IDbDataParameter prm8 = _context.DbContext.DbTemplate.DataParameter(
"@notes" , DbType.String);
prm8.SourceColumn = "notes";
cmd.Parameters.Add(prm8);
IDbDataParameter prm9 = _context.DbContext.DbTemplate.DataParameter(
"@pubdate" , DbType.Date);
prm9.SourceColumn = "pubdate";
cmd.Parameters.Add(prm9);
return cmd;
}
===========================================================

This is using the example pubs database. Here is the definition of the
'titles' table...
Name DataType Size Nulls
title_id tid 6 No
title nvarchar 80 No
type nchar 12 No
pub_id nchar 4 Yes
price money 8 Yes
advance money 8 Yes
royalty int 4 Yes
ytd_sales int 4 Yes
notes nvarchar 200 Yes
pubdate datetime 8 No

Michael Lang said:
I am using the standard "pubs" database.

Errror.ToString() --

"System.Data.SqlClient.SqlException: The text, ntext, and image data types
cannot be compared or sorted, except when using IS NULL or LIKE
operator.\r\n at System.Data.Common.DbDataAdapter.Update(DataRow[]
dataRows, DataTableMapping tableMapping)\r\n at
System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)\r\n at System.Data.Common.DbDataAdapter.Update(DataSet
dataSet)\r\n at Pubs.Data.titlesDB.Update() in
e:\\projects\\vs7\\pubsexample\\data\\titlesdb.cs:line 194"

This Occurs when I update the database...
DataAdapter _da; DataSet _ds;
...
_da.Update(_ds);

When I check the database, nothing has been updated. So the "compare or
sort" operation is happening before the update.

The DataSet has 1 "Modified" record. I changed a single field value on
field "6" below. These are the field values from the locals window for the
"Modified" DataRow.

[0] "BU1032" string
[1] "The Busy Executive's Database Guide" string
[2] "business " string
[3] "1389" string
+ [4] 19.99 System.Decimal
+ [5] 5000 System.Decimal
+ [6] {11} System.Int32
+ [7] {4095} System.Int32
[8] "An overview of available database systems with emphasis on common
business applications. Illustrated." string
+ [9] {6/12/1991} System.DateTime

Some other records have null field values, but they were null before the
update, and they are not modified records. here is the locals window values
for one of those DataRows

[0] "PC9999" string
[1] "Net Etiquette" string
[2] "popular_comp" string
[3] "1389" string
+ [4] {System.DBNull} System.DBNull
+ [5] {System.DBNull} System.DBNull
+ [6] {System.DBNull} System.DBNull
+ [7] {System.DBNull} System.DBNull
[8] "A must-read for computer conferencing." string
+ [9] {7/25/2003} System.DateTime

What is trying to "compare or sort" the records as the error states? The
DataAdapter?

Michael Lang, MCSD
Click to expand...
Click to expand...
Click to expand...
 
Back
Top