Database Connection

Hi,

I am using DataAssist 2.0.7 and have connected to a SQL database.

When I search, this works correctly and brings back information.

When I click View I get the following:
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

The same for Update.

If I click Insert and type something in like WDDDD I get:
permission denied on object 'HICOMClinicsAndDoctors', database 'PASExtract', owner 'dbo'.

If I changed the database permissions to write then the error changes to:
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Thanks

Wayne

your database has a column that is using one of these data types:
text, ntext, or image


the recordset created by dataassist is using the equals operator to filter that column.


this issue is being caused by the way your database is constructed.

Can I change the recordset rather than the database structure.

sure you would need to change any reference in the where clause that refers to a column you have defined as text, ntext, or image data type to use includes instead of equals for the comparison.

Hi,

The database only has 2 tables and they are both set to varchar.

Thanks

Wayne

If the tables are set as varchar why am I still getting the errors.

the varchar data type has the same limitation, you cannot perform an equals comparison in SQL on the varchar data type in a MS SQL database.

Hi,

I can only find 3 where commands I'm assuming its the first one where I need to change the = to includes. Is this correct because I still can't get it to work.

If you could include the changes showing which I need to change.

ADAdboHICOMClinicsAndDoctors_cmd.CommandText = "SELECT ClinicCode, DrCode FROM dbo.HICOMClinicsAndDoctors WHERE ClinicCode = ? OR ( -1=? AND ClinicCode=?)"

deleteParamsObj = WA_AB_generateWhereClause(WA_fieldNames, WA_columns, WA_fieldValues, WA_comparisons)

WA_Sql = "DELETE FROM " & WA_table & " WHERE " & deleteParamsObj

in reality, you should change the data type used in the database rather than the SQL.

Especialy when it comes to the delete statement, you would not want to use an sql state like this:
DELETE FROM table WHERE column LIKE value


this could potentially delete an unintended record.

Our issue is the database has been live for about a year. Its only added to occasionally, it only has 2 tables, one called ClinicCode and the other DrCode. I wanted to set this up to pass to the system administrator to add the codes rather than her contacting me.

The codes added are like CS CS4RH

Using a copy of the database we have changed the data types and each time we get an error.

The only time I can seem to get it to work is changing = to like. Includes doesn't work.

I thought using DataAssist would be easy but I'm tearing my hair out with this.