Text Datatype in MS SQL Server 7 not updating??

[kaul125]

I've defined a field in my MS Sql Server 7 database as a 'text' datatype. However, when I issue a PB 6.5 update() function in my code I get the following error:

The text, ntext, and image data types cannot be used in the WHERE, HAVING, or ON clause,
except with the LIKE or IS NULL predicates.

No changes made to database.

Why is this error occuring and how can I correct this problem?

Sincerely,

Keith

 

[mbalent]

In the update properties of the datawindow make sure the 'where clause for update/delete' is set to the 'key columns' option.

 

[BetterLateThanNever]

Try setting your update properties for the datawindow to key columns only.

Cheers.

 

[rt63]

Hi Keith,

As the error message says, MS SQL Server 7.0 does not allow to use text,ntext and image datatypes to be used in a defining filter conditions for a query.

The problem is coming in PB because, this column in your datawindow is defined as an updateable column and most likely, your datawindow update properties are defined as key and updateable/modifed columns. So by default, when PB generates the update statements, it includes all the key and updateable/modified columns in the where clause of the statement.

As mbalent and betterlatethannever have suggested, you can change this to key columns only. However, if you are worried about real multiuser issues, you can turn off only the text field to not figure in the where clause of the update statement. Though there is not option available on the datawindow designer, you can do it as follows

1. Export the datawindow as an .srd file.
2. If you open the srd in a text editor, you will see entries which define the source columns something like this

detail(height=1236 color="536870912" )
table(column=(type=char(1) updatewhereclause=no name=dmy_indctr dbname="dmy_indctr" values=" Y/ N" )
column=(type=char(5) update=yes updatewhereclause=yes key=yes name=src_icd dbname="trf_trnsfr_rqst.src_icd" )

3. In this block, just change the updatewhereclause=yes to updatewhereclause=no for your text field.
4. Save the srd file and import it back in your pbl.

Hope it helps

RT