Error for UpdateQuery from Visual Studio 2008

[doel.jangkrik]

I have problem with datatypes. When I use the text datatype on SQL Express, I get "The data types text and nvarchar are incompatible in the equal to operator." error.

Wwhen I set my variables to "varchar" or "nvarchar", I don't get an error, but my update command does not update any rows.

Someone suggested this solution:
---------------------------------------------------------------
This problem is likely caused by the automatically generated update query in datasource for the control.

Look in the UpdateQuery property in the datasource for the control

The error is generated by the query string comparison of the [source] field of type text or nvarchar

The update query can be modified like this:

UPDATE [TheTable] SET [source] = @source ......... WHERE [source] = @original_source ......
This should be changed to something like:

UPDATE [TheTable] SET [source] = @source ......... WHERE substring([source],1, 4096) = @original_source ......

----------------------------------------------------------------------

I did that, but I got a new error:

argument data type int is invalid for argument 1 of substring function

here is the original UpdateQuery:

UPDATE [JobVacancy] SET [JobName] = @JobName, [BriefDesc] = @BriefDesc, [FullDesc] = @FullDesc, [ClosingDate] = @ClosingDate, [JobSpec] = @JobSpec WHERE [JobID] = @original_JobID AND [JobName] = @original_JobName AND [BriefDesc] = @original_BriefDesc AND [FullDesc] = @original_FullDesc AND [ClosingDate] = @original_ClosingDate AND [JobSpec] = @original_JobSpec

I changed it to:

UPDATE [JobVacancy] SET [JobName] = @JobName, [BriefDesc] = @BriefDesc, [FullDesc] = @FullDesc, [ClosingDate] = @ClosingDate, [JobSpec] = @JobSpec WHERE substring([JobID],1,4096) = @original_JobID AND substring([JobName],1,4096) = @original_JobName AND substring([BriefDesc],1,4096) = @original_BriefDesc AND substring([FullDesc],1,4096) = @original_FullDesc AND substring([ClosingDate],1,4096) = @original_ClosingDate AND substring([JobSpec],1,4096) = @original_JobSpec

Any help is appreciated.

[dickersonka]

it is because although text and varchar may look the same, they are not, the actual data is a different type and can't be compared without converting

recommendation
the only part that should be in the where clause is the jobid and it should be an int or bigint value most likely, you don't want to have to keys as strings

change your text's to (n)varchar's, set dates to datetime, and all id's to ints or bigints

then your new sql statement will be

UPDATE [JobVacancy] SET [JobName] = @JobName, [BriefDesc] = @BriefDesc, [FullDesc] = @FullDesc, [ClosingDate] = @ClosingDate, [JobSpec] = @JobSpec WHERE [JobID] = @original_JobID AND [JobName] = @original_JobName AND [BriefDesc] = @original_BriefDesc AND [FullDesc] = @original_FullDesc AND [ClosingDate] = @original_ClosingDate AND [JobSpec] = @original_JobSpecUPDATE [JobVacancy] SET [JobName] = @JobName, [BriefDesc] = @BriefDesc, [FullDesc] = @FullDesc, [ClosingDate] = @ClosingDate, [JobSpec] = @JobSpec, [JobName] = @original_JobName, [BriefDesc] = @original_BriefDesc, [FullDesc] = @original_FullDesc, [ClosingDate] = @original_ClosingDate, [JobSpec] = @original_JobSpec
WHERE [JobID] = @original_JobID