ASP.NET

ASP.NET RSS

Error for UpdateQuery from Visual Studio 2008

Please support our ASP.NET advertiser: Intel Parallel Studio Home

•

Join Date: Apr 2007

Posts: 9

Reputation: doel.jangkrik is an unknown quantity at this point

Solved Threads: 0

doel.jangkrik doel.jangkrik is offline

Offline

Newbie Poster

Error for UpdateQuery from Visual Studio 2008

Nov 5th, 2008

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:

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
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
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:

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
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
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.

•

Join Date: Aug 2008

Posts: 1,160

Reputation: dickersonka will become famous soon enough

Solved Threads: 137

dickersonka dickersonka is offline

Offline

Veteran Poster

Re: Error for UpdateQuery from Visual Studio 2008

Nov 6th, 2008

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

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
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
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